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 (http://blog.SQLAuthority.com), BOL

About these ads

98 thoughts on “SQL SERVER – Primary Key Constraints and Unique Key Constraints

  1. Can u please tell me, what is the maximum number of unique key constraints that can be defined in a table.

    Also I have a query regarding joins in SQL-Server 2005.
    In a single select query, what is a number of max joins that we can have?

  2. Hi Pinal,
    In your Unique Key definition section you wrote, “Unique Key does not allow Nulls.” But i think Unique Keys allows one null value in the column on which they are defined.

  3. primary key is unique identifiying rows in s trable Ok…

    i agree with that but whats the problem with Primary Key means if you are not difine as a NOT NULL it will also taking null values as like unique key .

    So you should difine Not Null while difinig key as a Primary key.

    First check it out Reply me don’t forget oK……….

  4. to
    respected sir,

    i have created ta table and now i want to add a constraint
    primary key in SQL SERVER 2005

    but its generating error

    code:

    1) create table sample(sno varchar(20))
    2) ALTER TABLE sample
    ADD CONSTRAINT pk_sno PRIMARY KEY (sno)
    error details :
    Msg 8111, Level 16, State 1, Line 1
    Cannot define PRIMARY KEY constraint on nullable column in table ‘sample’.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    KINDLY SEND ME A SOLUTION

    • first u have to make the not null
      first u run the query
      Alter table sample modify sno datatype not null

      After this query u can define the primary key on the column.

  5. hi ankhenatraj

    Please refer the dataschema of your database and check if your column ‘sno’ of sample table has set to ‘NULL’. If so change that to ‘NOT NULL’ as primary key cannot be defined for a nullable column.

    Or else you can generate a unique constartint for that column ‘sno’ as unique constratints can be defined for the nullable columns.

    • Hi Deepika,

      I’m also facing the same problem when adding primary key to a column of an existing table.

      Error Msg:
      Msg 8111, Level 16, State 1, Line 1
      Cannot define PRIMARY KEY constraint on nullable column in table ‘sample’.
      Msg 1750, Level 16, State 0, Line 1
      Could not create constraint. See previous errors.

      You sugested “refer the dataschema of your database and check if your column ‘sno’ of sample table has set to ‘NULL’. If so change that to ‘NOT NULL’ as primary key cannot be defined for a nullable column. ”

      Please let me know how to refer to datascheme and change null to notnull.

      Thanks in Advance
      Swarup

      • Hi Swarup & ankhenatraj,

        when i tried to create primary key as you said, i have also got the same error as you guys mentioned.

        try the steps as deepika mentioned. here are the steps again for you.

        1) right click on the table name and select “Modify” in the Object Explorer(which is right side of the query window)

        2) remove the check mark for the column sno under Allow Nulls(which will then not allow the null values for that column and which is primary for creating the primary key)

        3) close that window and it will ask to save that table, then save it

        4) now again try to run the add constraint query, now it will execute successfully…

        –Narasimha

  6. hi

    Can u please tell me, I have two questions

    1.what is difference between Primary key & Unique Key Constraints?

    2.Which Constraints act as a primary key like not null and unique key Constraints?

  7. Hi,

    I want to drop the primary key on one table but i cannot know which constraint is there. Is there a way to drop the primary key without specifying constraint.

    The basic idea of doing this is :

    I have one table with 4 columns e.g.

    1. SrNo
    2. NodeID
    3. EnrollmentNo
    4. FingerNo

    Now the two columns already have Primary key NodeID and EnrollmentNo now i want to add another primary key FingerNo as well.

    Thanks,
    Prashant Hirapara

  8. @ Prashant

    I am not sure if you want to use GUI to do the same. Running queries is always good… but this can be done very easily using Enterprise Manager (SQL Server 2000 ) or SSMS ( SQL Server 2005)

    1. SQL Server 2000

    open Enterprise manager -> expand server -> expand database -> expand tables -> on the right side-> right click table name -> design table ->
    now you can see all the columns in that tables also you can see some primary key on one or more than one column, if you want to add one more column in the primary key, just do the following,

    1. First remove allow null check on that column.
    2. Press the control button and select all the column you want to be in your primary ( include previous primary key columns as well)
    3. Now right click the select columns, select Set Primary key.
    4. On the top, save the table.

    Just to check again, refresh database open table – design and see if you can see the primary key includes your new column.

    SQL Server 2005.

    Expand Server- Database – tables – keys – right click key name and click modify, do the same process you did in SQL Server 2000, first you will uncheck allow null on that column, and then you will press control button and you will select all the columns you want to have in primary keys and then right click on those selected column, select set primary key.

    at the top, click save and check once again if the changes has taken place.

    One more thing, if you are using Varchar(max), text, ntext,nvarchar(max), image datatypes, then I dont think you will be able to set primary key, if you have any varchar(max) columns then try to give a fix values to those columns, like varchar(100).

    Now your question remains the same, which one is primary key, what is the name of the primary key,

    IN SQL Server 2005, you can easily detect what is your primary key( under database – tables- keys) , because the prange color of primary key is different from foriegn keys which is grey color, and there can be only one primary key on one table.

    In SQL Server 2000 it is difficult to see as primary key and a foreign key.

    Try running these script, which will give you all primary key on all tables in the database including column information.

    SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
    WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    ORDER BY A.TABLE_NAME

    Sometimes you will see the primary constraint name repeats, meaning that primary key is a composite key.

    So now you have Primary Key constraint names and I told you procedure , you can do like that or what ever procedure you were trying to do earlier, I guess you were trying to drop primary key and then create a new primary key on the table. You can do this as well.

    Make sure no users are connected to that database, because when you create a primary key, by default a clustered index is created, clustered actually physically stores the data, meaning the tables on which you are trying to create primary key will be un available for users.

    Hope this helps.
    Imran.

  9. Hi Imran,

    Thanks for your prompt reply and i am sorry i have not mentioned that i am using SQL 2000 and i want to execute my things through query.

    Now your above query gives me the list of all the primary key constraint along with the column in which primary key is used. but it does not allow me to delete any of the key as it says that the information is view and cannot be update or delete.

    At last i have come to this query which is not working

    :-
    ———————————————————————–
    Alter Table tblFPTemplates Drop Constraint

    (

    Select Name

    From sysobjects

    Where xtype=’PK’ And parent_obj = (Select id From sysobjects Where xtype=’U’ And Name=’tblFPTemplates’))

    ———————————————————————–
    Can u please modify the above query to work it out for me. Again thanks a lot for your input.

    Thanks and Regards,
    Prashant Hirapara

  10. Pingback: SQL SERVER - Find Primary Key Using SQL Server Management Studio Journey to SQL Authority with Pinal Dave

  11. Can u please tell me how to update table with primary key on diffrent column and unique key on diffrent column? is that based on primary or unique key?

  12. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 3 Journey to SQL Authority with Pinal Dave

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

    Kallol

  14. Prashant – to drop a PK from a table, when You don’t know a PK’s name, You need to run something like:

    Declare @pkName nvarchar(500)
    Declare @sql nvarchar(500)

    select @pkName=name
    from sysobjects
    where xtype = ‘PK’ and parent_obj = object_id(‘YourTableName’)

    IF NOT @pkName IS NULL
    BEGIN
    select @sql = ‘ALTER TABLE YourTableName DROP CONSTRAINT ‘ + @pkName
    execute sp_executesql @sql
    END

  15. Dear Pinal,
    I like your site very much. Few concern about pk and unique key

    1) how many columns could be included in unique key(i mean whether composite unique key is possible or not)
    2) Could we edit values of the unique key/pk (keeping them unique)
    3) how to use composite pk or fk as foreign key
    4) which one is better choice composite pk or composite unique key
    5) What if wanna modify composite pk column value

    I could try in db to get my ans but wanna confirm frm you.

  16. @spider

    1) Yes, composite UNIQUE keys are allowed.
    2) Yes, because you don’t edit the key, you edit the values. Then the UNIQUE CONSTRAINT will make sure the data is still unique. If it is, it will allow the edit.
    3) Like any other FK. FOREIGN KEY(col1, col2) REFERENCES other_tab

    If the parent is a UNIQUE key instead of the PRIMARY KEY
    FOREIGN KEY(col1, col2) REFERENCES other_tab(col1, col2)

    4) That depends on the situation. Assuming i understand the question, PK would be better.
    5) See question 2.

  17. We can create composite like

    CREATE TABLE Authors (
    AuthorID INT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    PRIMARY KEY(AuthorID,Name)
    )
    GO

    am I right??

  18. Hi Pinal.
    I am looking for the sql statement, by which executing, i can come to know which column(s) is having unique constraint and which is/are not having.

  19. Hi Pinal,
    Thanx for the reply. It solved my problem…..
    It is really very useful me…
    Thanx again….

    Pinal, I am fond of learning SQL deeply. And I have heard lots of about your knowledger and you. So I would love to attend your seminars. So can you plz tell me how can I get the information about your upcoming seminars?

    Regards,
    Nilesh Soni

  20. Hellow Pinal,

    You solved my last problem. But I’ve come again with another problem.

    I need to change the data type of column ‘Science’ in table ‘tblMarks’.
    I want to change the data type from int to varchar(100) and want to set the default value ‘abc’.

    I’ve tried too many times with different sql statements and have checked lots of websites. But didn’t succeeded.

    Here I have written a sql statement which is producing error.

    ALTER TABLE [dbo].[tblMarks] Alter Column Science varchar(100) Not Null DEFAULT ‘abc’

    Before changing the data type the column ‘Science’ does not have any default constraint created on it.

    Plz help me.

    Regards,
    Nilesh Soni

    • @Nilesh

      1) ADD a new COLUMN of the correct type.
      ALTER TABLE [dbo].[tblMarks] ADD Science2 varchar(100) Not Null DEFAULT ‘abc’;

      2) UPDATE [dbo].[tblMarks] SET Science2 = Science;

      3) ALTER TABLE [dbo].[tblMarks] DROP Column Science;

      4) 3 options.

      4a) sp_rename
      4b) Open tblMarks in Design (right-click) and change the name.
      4c) ADD Science as the new COLUMN, UPDATE it to equal Science2, and DROP Science2.

  21. Hello Nilesh,

    What is the error produced by ALTER TABLE statement?
    Try to perform alter column length using SSMS interface when table is not being used.

    Regards,
    Pinal Dave

  22. Hi Pinal,
    I’m getting below error while executing the alter statement “ALTER TABLE [dbo].[tblMarks] Alter Column Science varchar(100) Not Null DEFAULT ‘abc’”.

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘DEFAULT’.

    and i’m unaware about SSMS interface.

    Regards,
    Nilesh Soni

    • Hello Nilesh,

      Break the statement in two as below:

      ALTER TABLE [dbo].[tblMarks] Alter Column Science varchar(100) Not Null

      ALTER TABLE [dbo].[tblMarks] ADD CONSTRAINT Sc_Default DEFAULT ‘abc’ FOR Science

      Regards,
      Pinal Dave

  23. Hi Brian Tkatch.
    Thanx for reply.
    But Brian my purpose is not for just a single time execution. I’m developing a tool which used to create the xml file at admin side or our side. the file will contains the information about changes in the database and the same tool will read the xml at the client side and will make the changes by creating and executing the sql statements. so the way you suggested is little bit long to altering the table. and i’m sure there is a way to alter the table with default value and not null attribute.

    Thanx once again Brian…

    Regards,
    Nilesh Soni

  24. hi

    i created a table with columns such as p_id(unique),name(unique),address,city.

    now i want to drop the constraint for name column.

    whats the query to done the same

  25. HAI….

    I HAVE ANOTHER PROBLEM….

    I CREATED A TABLE AS

    CREATE TABLE PERSONS(PID INT, LASTNAME VARCHAR(8),FIRSTNAME VARCHAR(8))

    NOW I WANT TO ADD PRIMARY KEY FOR PID COLUMN…

    FOR THIS I WROTE A QUERY AS

    ALTER TABLE PERSON
    ADD PRIMARY KEY (PID)

    I AM WAITING FOR UR ANSWER

    WHILE I EXECUTE THIS ,I FACE SOME ERROR. I MENTION IT BELOW

    Msg 8111, Level 16, State 1, Line 1
    Cannot define PRIMARY KEY constraint on nullable column in table ‘primaryalter’.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    PLS SOLVE MY PROBLEM..

    • Hi Ramya,

      it seems the column PID is accepting null values..that’s why the PK constraint is not defined. go to the table design and remove the Allow Nulls check mark for the PID column and then try again.

  26. hi,

    I want Add Unique key constraint on my existing table which already have data.

    I run this query..

    ALTER TABLE tbl_Standard ADD CONSTRAINT Unique_Standard_Name UNIQUE(Standard_Name)

    I think error is due to this names
    Ex

    in first row ‘ASTM C 295 – 98′

    in 2nd row ‘ASTM C 40 – 98′

    these both values are different but I am receiving error.. plz help me..

    Error
    “CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is ‘ASTM C’ “

  27. Isn’t the other *BIG* difference between PRIMARY KEY & UNIQUE INDEX the fact hat UNIQUE INDEX allows you to add columns using the INCLUDE keyword, and PRIMARY KEY will not.

    • Nope.

      Primary key is basically unique index. By default it is also clustered index, which defines how data is ordered by default, but it can be non-clustered also and then it behaves as normal unique index.

      Include can be used with non-clustered indexes and index doesn’t even have to be unique.

  28. hi

    iam using select query in my sp to reterieve the values from the temp table. In my sp iamgetting the values from the temp table by calling another sp by passing the parameter and then i am getting the values through select query using 2 right joins and 3 left outer join with where condition and with group by.

    but this select statement is taking more than 15 minutes to execute. i have already created index for the temp table. canu plz give me any solution

  29. Hello Aswathaman,

    This is not sufficient to identify the issue and recommond a solution. You can get the correct solution by checking the Execution Plan of query. In execution plan, Idetify the most resource consuming tasks and apply a fix.

    Regards,
    Pinal Dave

    • 1. Is it good to use foreign key reference to a unique key. Please advice.

      2. How to hide primary key from view of user in sql server.

  30. Hi,

    Can you please tell me why Unique Constraint can allow only one Null and no more?
    You know that Null is not a value so what is the reason of this?

    • @Majid

      SQL Server (and DB2, IIRC) store NULLs on the INDEX. Even NULL can only take up one space in a UNIQUE INDEX. Oracle does not put NULLs on the INDEX, so it can have as many as there are. Both have pros and cons.

      Regardless, SQL Server 2008 can support more than one NULL with a filtered INDEX, WHERE col IS NOT NULL;

    • @ahmed

      unique(a): COLUMN a must be UNIQUE by itself.
      unique(b): COLUMN b must be UNIQUE by itself.
      unique(a) unique(b): Both COLUMN a and b must be UNIQUE separately.
      unique(a,b): COLUMNs a and B must be UNIQUE in combination.

      a b
      - -
      1 2
      1 3

      That is unique(a,b), but is not unique(a) unique(b), because b is not UNIQUE on its own.

  31. Hi,
    I have to alter some column’s datatype in many tables. Problem is, the columns are composite keys or foreign keys. When I am altering the data type its giving error

    Server: Msg 5074, Level 16, State 8, Line 1
    Server: Msg 4922, Level 16, State 1, Line 1

    I do not want to drop the constraint as there are lots of data in the tables.
    Please help

    thanks
    Harsha

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

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

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

  35. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31 Journey to SQLAuthority

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

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

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

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

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

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

  42. 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#).

  43. 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#).

  44. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  45. Pingback: SQL SERVER – Primary Key and NonClustered Index in Simple Words « SQL Server Journey with SQL Authority

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s