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

Question

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.

see this too [http://stackoverflow.com/questions/20404730/what-is-difference-between-unique-key-with-not-null-constraint-and-primary-key] — Iftikhar Ali Ansari, Mar 22 '15 at 19:10

score 1 · Answer 1 · answered Mar 22 '15 at 19:08

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.

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

1 Answers1