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
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL
97 Comments. Leave new
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.