CODEWITHSUNDEEP
mysqldatabaseprimary-keyunique-keyrdbms
Nargis
Nargis

Reputation: 769

Which column we should choose as primary key if we have 2 unique columns

I know what's the difference between unique and primary key as explained in following links

http://sqlhints.com/2013/06/02/difference-between-primary-key-and-unique-key-in-sql-server/ difference between primary key and unique key

but I was asked something different in an interview ,

If I have 2 columns in a table which can uniquely identify entries , say student's roll no and national id no. Then which one I'll select as primary key and what's the difference between primary and unique key if we add not null constraint on unique key.

Any help regarding this?

Thanks in advance.

Upvotes: 0

Views: 158

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I am a big advocate of the third way -- having an auto-incremented, integer primary key. You can then put unique indexes on the others.

Such a key offers several things. First, it allows you to determine the order of insertion of records -- at least to a close approximation. Second, it makes foreign key references easier. I typically name such columns <tablename>Id, which makes external references obvious.

Third, integers are a pretty efficient mechanism for indexes (fixed width values, typically four bytes). Admittedly, they do incur a bit of overhead in the original table, but that is usually minor.

Fourth, the columns may not be applicable. In the United States, the closest we have to a national id number is a social security number -- and it is not guaranteed to be unique. Further, there may be students who enroll in the school that do not have such numbers -- foreign students, for instance.

Upvotes: 1

Related Questions