Difference Between Primary Key and Unique Key In Sql Server

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
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id,FirstName)
)

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
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT UK_CUSTOMER UNIQUE (Id,FirstName)
)

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

57 thoughts on “Difference Between Primary Key and Unique Key In Sql Server

  1. Hello Admin,

    one more major difference is primary key can be referred as foreign key and unique key can not refer as foreign key.

    1. 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??????????????????

      1. 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

      2. 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.

      3. 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?

      4. 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 .

      5. 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..

      6. 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

      7. 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.

      1. I think he got confused if not wrong
        A PRIMARY KEY CAN HAVE MULTIPLE COLUMNS not MULTIPLE PRIMARY ZKEYS in a Table

  2. 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

    1. 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.

      1. 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.

    2. 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.

  3. 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…

  4. 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)
    )

  5. 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.

  6. 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?

  7. 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));

  8. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *