Primary Key and Unique Key
DanYeung
[85 byte] By [Acceris] at [2007-11-11 7:51:47] 
What is the difference between primary key and unique key? Thanks.
DanYeung
# 1 Re: Primary Key and Unique Key
None, really. Why do you ask?Phil Weber at 2007-11-11 23:47:33 >
# 2 Re: Primary Key and Unique Key
I was asked this question in an interview. I said there were no difference. The interviewer said one allows NULL and not the other one. I forgot which one. But it didn't make sense to me. If NULL is allowed, that means they are not unique. That is why I asked.Acceris at 2007-11-11 23:48:39 >
# 3 Re: Primary Key and Unique Key
The interviewer didn't know what he was talking about. Perhaps he was thinking about a Primary Key vs. an non-unique Index.pclement at 2007-11-11 23:49:37 >
# 4 Re: Primary Key and Unique Key
Quoting from SQL Server Books Online:"UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values."
This will be of little significance in practice, as only one null value will be allowed. One place where it could be used would be for a lookup table that had a value for a null reference.
So yes, your interviewer really knew what he was talking about.
Rune
Rune Bivrin at 2007-11-11 23:50:43 >
# 5 Re: Primary Key and Unique Key
Actually I realized this after I posted but I think it's even worse than described. I will have to check this behavior in SQL Server but I believe null values can be ignored completely, which means that you're not limited to one row with a null value in the column that represents a unique index.I'm not really sure why the interviewer asked this question. That is unless you were interviewing for a DBA position on a specific database platform. I think it's fair to say that this concept is not universally and consistently applied across other database platforms.
pclement at 2007-11-11 23:51:47 >
# 6 Re: Primary Key and Unique Key
Probably not a universal truth, no. And definitely nothing you think about every day. The uniqueness holds, I should have written that I tested that, at least on SQL Server 2000 and 2005.
Rune
Rune Bivrin at 2007-11-11 23:52:43 >
# 7 Re: Primary Key and Unique Key
Can unique key has more than one columns? For example, column1, 2, and 3 combine makes one unique key. Thanks.DanYeung
Acceris at 2007-11-11 23:53:41 >
# 8 Re: Primary Key and Unique Key
Yes. It's referred to as a "composite" key and is fairly common.pclement at 2007-11-11 23:54:44 >
