SQL SERVER – Primary Key Constraints and Unique Key Constraints

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

, , , , , , , , ,
Previous Post
SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case
Next Post
SQL SERVER – Function to Convert List to Table

Related Posts

97 Comments. Leave new

  • Hi

    I have a two tables A1 & A2 with columns

    ADD1, city, state, postal_code & country

    & B_ADD1,B_state,B_postal_code & B_country.

    The second table has the correct combination of city,state,postal_code and Country &

    the first table can have errors.

    I need to write a query or create a view which will fetch me all the combinations of A which does not have a perfect match in B

    for example

    A has
    hyd,ap,500056,india
    bombay,ap,500056,india

    B has
    hyd,ap,500056,india
    bombay,mah,2993849,india

    my query must return the error record from A

    bombay,ap,500056,india

    after checking all combinations in B

    Thanks in Advance
    Prashanth

    Reply
  • SELECT A.* FROM A, B WHERE A.CITY = B.CITY AND (A.STATE B.STATE OR A.PINCODE B.PINCODE OR A.COUNTRY B.COUNTRY)

    Reply
  • I am making project (desktop application) using C#.NET and SQL server 2008.I have used p.k and f.k in three tables….and when i update or delete or insert i get violation error and its natural but is there any solution that neither constraints get affected nor my prject.

    Reply
  • Rajendra Sorkhe
    April 28, 2011 8:50 pm

    I want to update a table in which almost 20 records for a single name.
    I want to update dates in a table which is assoicated with a single name for 20 records. I want to keep the name unchanged n want to update the dates as 3,4,5, and so on.

    Reply
  • hello friends,help me on this

    Create table person(p_id varchar(10) unique,
    name_s varchar(20))

    I have created a table person and set p_id as unique.
    Now i want to delete the Unique for the P_id..
    how can i do that in sql server… reply me asap

    Reply
  • Really nice site…

    Reply
  • Hi,
    I have one question: I have one table Logs table lots of thread make entry in this table. The Primary key of this table in not identity column. I am getting primary key conflict error..what should i do now?

    Reply
  • Hello Mr. Dave,
    I read your articles so many time which are very fruitful for me,
    From their I will get all most all the solutions of my query very easily , But I have some ambiguities
    In my table. Lets give me solution for that .
    My query is , may table is tab1(colname, tabname) here both are column name and multivalued
    My table data is given as follows :
    Opdate compupdtdtl
    Cldate compupdtdtl
    Totalamt loanstatus
    [long list truncated]

    My expected output is :
    colname tab1 tab3 tab4
    cldate cr_cp compupdtdlt rpl_emp_leave
    loanid loantrans compupdtdtl loanstatus

    Could you gime me solution of this problem ,
    Anyone ?

    Reply
  • i have two tables unit and item primary key(uid) of unit table is foreign key of item table
    now i want to add the data in item table but due to foreign key problem my data is not inserted into item table in SQL server 2005

    what is an alternate way to make insert query on database

    Reply
  • can you pls give real time examples of a just pk and a unique key which is not PK

    Reply
  • hai i need the command of without using constraint to drop the primary key

    Reply
  • Sourish Biswas
    February 9, 2012 7:25 pm

    I have checked
    Primay Key does not allow NULL value

    Reply
  • how i update primary key value which have already foreign key value and i and i want to also update the record of foreign tables. tell me i am using ms sql 2005 server

    Reply
  • Hi Pinal,
    I tried creating table with unique key as below and getting error when trying to insert one record with NULL value.
    create table TEST (
    id int not null,
    name varchar(100)
    )

    ALTER TABLE TEST
    ADD CONSTRAINT UNQ UNIQUE(ID)

    INSERT INTO TEST VALUES(1,’A’)
    INSERT INTO TEST VALUES(NULL,’B’)

    Error Msg:
    Msg 515, Level 16, State 2, Line 1
    Cannot insert the value NULL into column ‘id’, table ‘CR6_DW.dbo.TEST’; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    But unique key should allow one null value right?
    Version used:
    Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (VM)

    Reply
  • Hi This is Srikanth,

    Is Possible to give Primary Key and Unique Key in same table .

    Reply
  • Hi Pinal,
    I want to learn about SQL Transactions.Please Help me.
    Thanks
    With Regards
    Amit Kumar

    Reply
  • I want to throw exception while entering the values in registration form n ve to store it in database… wen i am giving email id n second time also i am using same id means the error should come as mail id is already exists in Database…i need code in asp.net(c#).

    Reply
  • I want to throw exception while entering the values in registration form n ve to store it in database… wen i am giving email id n second time also i am using same id means the error should come as mail id is already exists in Database…i need code in asp.net(c#).

    Reply
  • I learnt lot from this conversation…………

    Reply
  • Dear Pinal Sir,

    Please explain me how to disable Primary key constraint?

    Reply

Leave a Reply