Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We can declare an Identity like id_num so that id_num will have an increment of unique numbers.

CREATE TABLE new_employees
(
  id_num int IDENTITY(1,1),
  fname varchar (20),
  minit char(1),
  lname varchar(30)
)

Is it recommended to use Identity as a alternative to Primary key since Identity provided a unique number for each row?

share|improve this question
    
Why you need to do this ? because it's good to be one primary key in table it's automatically create cluster index and good for optimization. –  KuldipMCA Jul 15 '13 at 10:12
3  
No, it is recommended to use IDENTITY as a primary key (not as an alternative to a PK). It makes for a very good, very efficient primary key (which by default also is the clustering key in SQL Server) - it's narrow, it's usually static, it's typically unique (unless you fiddle around with the identity spec), and it's ever-increasing –  marc_s Jul 15 '13 at 11:41
add comment

2 Answers

up vote 8 down vote accepted

Identity columns and Primary Keys are two very distinct things. An Identity column provides an auto-incrementing number. That's all it does. The Primary Key (at least in SQL Server) is a unique constraint that guarantees uniqueness and is usually (but not always) the clustered key. Again in MS SQL Server it is also an index (in some RDBMS they are not as closely tied). As an index it provides faster lookups etc. Frequently Identity columns are used as the Primary Key if no good natural key exists, but are not a substitute.

share|improve this answer
    
In SQL Server, a non-clustered index contains the primary key at the leaf level. Designating a natural key as a primary key will make your indexes very large. –  Andomar Jul 15 '13 at 15:16
2  
@Andomar a PK is a logical entity. The supporting index for the PK is physical. And a nonclustered index can support a PK, doesn't necessarily have to be a clustered index. Therefore, your statement is incorrect if the PK's index is a NCI. It's the clustered index key at the leaf level of NCIs. –  Thomas Stringer Jul 15 '13 at 15:24
    
@ThomasStringer: You're right, my comment should read clustered index key where it says primary key. –  Andomar Jul 15 '13 at 15:30
    
Possibly. Your natural key could be an integer in which case there is no difference. The number of arguments you will see on Artificial vs Natural keys is staggering. Try a web search on it sometime :) That just happens to be one of the arguments for artificial keys. –  Kenneth Fisher Jul 15 '13 at 15:38
add comment

No it is not, because identity does not guarantee a unique value. The identity property can be bypassed with SET IDENTITY_INSERT <schema>.<table> ON (in SQL Server - you didn't specify what RDBMS you are using).

A primary key constraint (and a unique constraint) uses a unique index to enforce uniqueness.

share|improve this answer
1  
it does not guarantee a unique value. But neither does anything else. Anyone who has access to the database can attempt to INSERT duplicate values to a primary key. What an identity does is make it easier to generate unique values (especially if you do not have a good natural key as Kenneth Fisher says). You could declare an integer field with primary key and it would be exactly the same thing but without auto generation. So the IDENTITY is for generation and the key is for enforcing uniqueness. –  Chris Aldrich Jul 15 '13 at 13:01
    
+1 For pointing out the basic flaw in the premise of the question - Identity and Primary Key are not equivalent and serve two distinct purposes. –  JNK Jul 15 '13 at 18:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.