Both PRIMARY KEY and UNIQUE KEY enforces the Uniqueness of the values (i.e. avoids duplicate values) on the column[s] on which it is defined. Also these key’s can Uniquely identify each row in database table.
[ALSO READ] Difference Between Sql Server VARCHAR and NVARCHAR Data Type
Below table lists out the major difference between PRIMARY KEY and UNIQUE KEY:
| PRIMARY KEY | UNIQUE KEY | |
| NULL | It doesn’t allow Null values. Because of this we refer PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT |
Allows Null value. But only one Null value. |
| INDEX | By default it adds a clustered index | By default it adds a UNIQUE non-clustered index |
| LIMIT | A table can have only one PRIMARY KEY Column[s] | A table can have more than one UNIQUE Key Column[s] |
| CREATE SYNTAX | Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table:CREATE TABLE dbo.Customer ( Id INT NOT NULL PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY: CREATE TABLE dbo.Customer |
Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table:CREATE TABLE dbo.Customer ( Id INT NOT NULL UNIQUE, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY: CREATE TABLE dbo.Customer |
| ALTER SYNTAX | Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key:ALTER TABLE dbo.Customer ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id) |
Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created:ALTER TABLE dbo.Customer ADD CONSTRAINT UK_CUSTOMER UNIQUE (Id) |
| DROP SYNTAX | Below is the Syntax for dropping a PRIMARY KEY:ALTER TABLE dbo.Customer DROP CONSTRAINT PK_CUSTOMER |
Below is the Syntax for dropping a UNIQUE KEY:ALTER TABLE dbo.Customer DROP CONSTRAINT UK_CUSTOMER |
ALSO READ
- Varchar vs NVarchar
- Varchar vs Varchar(MAX)
- Char vs Varchar
- Text vs Varchar(Max)
- Union vs Union All
- DateTime vs DateTime2
- SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
- Stored Procedure vs User Defined Function
- Primary Key vs Unique Key
- RAISERROR vs THROW
- Temporary Table vs Table Variable
- Len() vs Datalength()
- Sequence vs Identity
- DATEDIFF vs DATEDIFF_BIG
- LEFT JOIN vs LEFT OUTER JOIN
- RIGHT JOIN vs RIGHT OUTER JOIN
- JOIN vs INNER JOIN
- LEFT OUTER JOIN vs RIGHT OUTER JOIN
- SMALLDATETIME vs DATETIME
Hello Admin,
one more major difference is primary key can be referred as foreign key and unique key can not refer as foreign key.
I believe it can: http://technet.microsoft.com/en-us/library/ms191166(v=sql.105).aspx.
It can’t, since a unique key could have a Null value and Primary Key can’t have a Null value
can anyone explain me that in the above examples two primary keys are defined in create syntax row,but it is stated in the same slide that one table can only have one primary key??????????????????
Hi
Not null is a constraint which will not allow null values but duplicate values for that column is allowed.
Where as , the CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id,FirstName)
is not a Primary key, but composite Primary key.
Composite primary key can be defined on more than one column.
So, according to the structure defined,
ID Firstname
1 Nitin
1 Vijay
2 Nitin
2 Vijay,
The main point here, though the duplicate are there in both the column, but the combination of both column is different.
Hope, this is useful.
Regards,
Nitin
Good one Nitin,very easy to understand the difference between Primary Key and Composite Primary Key.
If you are specifying primary on more then one column then it is known as the composite primary key.
good question.
A table can have only have one PK. All tables in your databse should have a PK (although this is not enforced by most dbms). PK can span multiple columns.
see try to read it carefully. as it mention one table has only one primary key it
does not mention that primary key can be applied to only ONE COLUMN.
Here is only single primary key that applied to multiple column.
and single primary key name is PK_CUSTOMER.
did you get it?
Yes utkarsh as said above there can only be one primary key column in a table but as show below we are not assigning primary key to two different columns actually we are concating or merging two columns into a single then assigning primary key
So after the mering cmd do desc table name
Then these two columns will not be shown seprately assigned pri key .
There are two types of primary key stated as primary key constraints which is one on one page…
And primary key unique can be multiple on one page..
A primary key can contain one or more columns but a table can have ONLY ONE Primary Key
Primary Keys data is in PHYSICAL ORDER IN A TABLE MUST follow NOT NULL RULE and NON DUPLICATE
The answer for your question is simple. A table can have one primary key constriant,but can hold multiple columns.
it would have been clear if the statement has mentioned as clearly as a table can have one primary key constraint and not many.
Thanks.
Those are not 2 primary keys. It is simply a set of 2 attributes forming a single primary key
hi eashwar,
foreign key can also refer unique key only in sql server.
A foreign key can be defined to reference a column of a unique constraint of another table @Eshwar V
Hi easwar,
Correct me if m wrong, as per i know Foreign keys can reference Unique Keys not the Primary Key.
No friend … FK can refer PK as well as UK
Unique key can also be referred as foreign key.
In Sql Server Unique key can be referred as foreign key.
Yup
A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.
thanks very useful
Why is the above table syntax example has two primary keys, shouldnt it only be one primary key for each table?
Where we have two primary key’s?
No two or more table records are not same
I think he got confused if not wrong
A PRIMARY KEY CAN HAVE MULTIPLE COLUMNS not MULTIPLE PRIMARY ZKEYS in a Table
We don’t have two primary keys, its a single primary key combining two attributes(Id, FirstnName)
can we create 2 unique keys on a table in sql server?
Yes ,You can do that .you can make any number of unique key in a table
how to use the indexes
HI,
what happens if we need to give more than 2 times null values for a column where it is being set as UNIQUE….it allows null , and second time when null comes and if it accpts !!?? then wot s the significance for the uniqueness??? pls somebdoy explain ??
expecting some wise answers
Unique column has unique values it cannot be same but it can be null value for once which makes different from primary key…
If you declare column as unique key and if dont enter any value then it takes null automatically but if you dont enter again any value then it gives you an error because this time it tries to store null values but already null value is present in your table…so i said it can have null values for once.
Hi
– As per database theory NULL is not a value, it is a state of absence of data, hence uniqueness of nulls cannot be identified by Unique constraint.
– when we declare unique constraint on a column multiple no of NULLS will be generated whenever input values are not given.
What’s the meaning of uniqueness ? It means, “the quality of being one of a kind”.
So once the ‘Null’ Value (a kind of quality) is existed in the column, the unique constraint won’t allow the another value of same kind i.e. Null.
In other words, no two identical values will exist in the column at a time where the column is defined as Unique.
Hope it’s clear.
Corrections are most welcomed.
Regards,
Nabeel.
What is the use of unique key? Where do we apply?
what is use of foreign key and wht is the difference between primary key and foreign key?????
What do you mean by NULL is it
“NULL” or “” .. in what “type” of primary key are you talking about…
i mean “int” or “varchar”…
This is so confusing…..
Because a Primary key can take null “” values….. go to myadmin for the same…
Zabardast example bro… loved the to-the-point explanation.
i want one more simple explaination of PK and UK difrnce
whre v hv two PK???
Primary Key is the one which does not allow NULL VALUES.then what is the use of NOT NULL declaration with addition to PRIMARY KEY declaration.Refer the lines:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)
Read Basic frist of any language.
Can a PK join a different column PK ? Let’s say
Inner Join …….
On BusinessEntityID = SalesPersonID
thank you
Nice article and examples also good
hi guys i need to drop my unique constraint on column in my table but i dont have constraint name for that so plz help me with this.
hi pals,i have a doubt about pk and fk
suppose we have 2 composite primary key tables,then how to do relation for that 2 tables using foreign key?
like in a table we have combination of two attributes as one primary key then can we declare one of that attribute alone as unique..
ex: primary key(pno,dno),
unique (pno));
If you can make a unique column not null, and you can make a clustered index on that column, doesn’t that mean you could get away with always using unique constraints instead of primary keys? It doesn’t seem like primary keys have any need to exist.