Primary Key and Unique Key difference in SQL Server
posted from http://www.sqlservergeeks.com/primary-key-and-unique-key-difference-in-sql-server/
Primary and Unique Key both enforce uniqueness of columns on which they are defined. Then where do they differ? They differ in following way
- A primary key doesn’t allow null value wherein a unique key allows one null value.
- A primary keys purpose is to uniquely define a row in a table wherein unique keys purpose is to enforce uniqueness on a column(s).
- A primary key is implemented by a default unique clustered index on column(s) wherein unique key is implemented by a default unique non clustered index on column(s).
- A primary key can’t be created on a null able column wherein a unique key can be created on a null able column.
- There can be only one primary key on a table wherein a table can have multiple unique key defined on it.
Let’s create a table with a primary key and a unique key constraint.
CREATE TABLE tblCns (sno INT NOT NULL, col1 VARCHAR(100),col2 VARCHAR(100)) GO -- Add unique key constraint ALTER TABLE tblCns ADD CONSTRAINT uq_Cns_col1 UNIQUE(col1) GO -- Add another unique constraint ALTER TABLE tblCns ADD CONSTRAINT uq_Cns_col2 UNIQUE(col2) GO -- Add a primary key constraint ALTER TABLE tblCns ADD CONSTRAINT pk_Cns_sno PRIMARY KEY(sno) GO
Let’s analyze the indexes using sp_helpindex stored procedure.
There are two unique key and a primary key. Attempt to create a second primary key will result in an error. The primary key is implement by a unique clustered index and unique key is implemented by a unique non clustered index.
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
