Primary Key:
Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.
Create table with Primary Key:
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO
Alter table with Primary Key:
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO
Unique Key:
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.
Alter table to add unique constraint to column:
ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)
GO
Primary Key or Unique Key: What to Check Before You Choose
The two constraints look alike, so here is how I decide. A table can have only one primary key, and it should be the column or columns that identify each row. You can add as many unique constraints as you need for other columns that must not repeat, like an email address or a product code.
The index part has a small catch. A PK creates a clustered index only when the table does not have one yet and you do not ask for NONCLUSTERED. If a clustered index already exists, the PK gets a nonclustered index. In the same way, you can make a unique constraint clustered if you ask for it. So the default is a starting point, not a rule.
NULL handling is the other big difference. A PK column can never be NULL. A unique constraint in SQL Server treats NULL as a value, so only one row can hold NULL in that column. If you need many NULLs but unique values for the rest, a filtered unique index with WHERE column IS NOT NULL does the job, but that needs SQL Server 2008 or later.
Both constraints can be the target of a foreign key, which surprises many people. Whenever you create either one, give it a clear name, like PK_Authors or UQ_Authors_Name. Names generated by the system are hard to read in error messages and scripts.
To see what a table already has, run sp_helpconstraint with the table name, or query sys.key_constraints. Both list each PK and unique constraint on the table.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
97 Comments. Leave new
Wonderful beat ! I would like to apprentice while you amend your website, how could i subscribe for a blog website?
The account aided me a acceptable deal. I had been tiny bit acquainted
of this your broadcast offered bright clear concept
Hi ,
Why unique key allows only NULL value when one NULL is different from other NULL.
Hi Pinal,
Happy Evening.
First of all, your blog has been of great help to me in my SQL Server queries and application development. Almost all my queries are answered and its really great work done by you.
I have got a query which i would like you to directly clarify.
These days, i am into an retail chain application development with a HO and multiple BO which has distributed database. Since i want the primary key to be unique on data merge with Head Office database, i have kept the primary key to be nvarchar(50) in all the tables so that the primary key data would be something as mentioned below
3 Characters of each : Company – Location – User – Division – Terminal – WorkStation – RowID(Identity Field of that table or a counter for generating unique number for each row)
CO1-HOF-US1-DV1-TE1-WS1-1001
CO2-BR1-US4-DV3-TE9-WS4-15201
CO1-BR2-XYZ-FFL-TE8-WS2-250001
This way when the data will merge, there wont be any clash in primary key index.
The database will have millions of records on a future date.
The complete database has been designed with very good normalization.
I am planning to create a desktop based application as well as a web based application with the same data structure hosted on cloud server. Whether can i go ahead reluctantly or do i need to take some measure right now with nvarchar primary keys.
Just want clarity, whether having an nvarchar(50) field as a primary key in all the tables which will be used for all multiple joins with every query will have a huge impact on Reports loading speed and on the database size?
Is it recommended as per my application scenario? and if not then how should i achieve unique primary key with distributed database with 5-6 different factors (Company, Branch, Division, User, Terminal, WorkStation)
Your quick response with clarity awaited.
Usefull..Thanks
Hi ,
Can Foreign key reference to UNIQUE KEY ?
Any one help me !
Hi,
As we know unique key only allow one null value.
If we try to insert another null value then unique key constraint violated.
But Null is unknown value, How can we compare one null value to another null value?
And saying first entered null value is unique?
Getting confused. Waiting for reply.
Thanks in Advance.
Remove the “not null” from the id column first.