Tuesday, June 15, 2010

How to CREATE Primary Key on existing table?

Only single PRIMARY KEY constraint can be added to a table.
  • Primary Key can be added when the table is created, as part of the table definition.
  • Primary Key can be added to an existing table, provided that no other PRIMARY KEY constraint already exists.
How to CREATE Primary Key on existing table?
To explain this, I'll create a table without Primary Key constraint. Below is T-SQL to create a TestPK table:

IF OBJECT_ID('TestPK','U') IS NOT NULL
DROP TABLE TestPK
GO
CREATE TABLE TestPK (
    [ID] [int] NOT NULL,
    [Name] [varchar](50),
    [DoB] [date],
    [DeptNo] [tinyint]
)
GO
INSERT INTO TestPK (ID,Name,DoB,DeptNo) VALUES
(1,'Name1','1983-04-07',1),
(2,'Name2','1992-10-21',3),
(3,'Name3','1981-02-17',1),
(4,'Name4','1975-11-23',2)
GO
SELECT ID,Name,DoB,DeptNo FROM TestPK (NOLOCK)


Now ALTER the table to ADD Primary Key
Execute below T-SQL code to add primary key:

ALTER TABLE TestPK
ADD CONSTRAINT TestPK_PrimaryKey_ID PRIMARY KEY (ID)

Note: Before adding a PRIMARY KEY constraint to an existing column(s) in the table, ensure that the existing data follows the rules for primary keys:
1. No NULL values  2. No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.

2 comments:

  1. Hi, just passing by your site to see something that will interest me and luckily you impressed me with your great article and I have a great time reading everything that is written. I'm looking forward to see more of your write-ups. If you have time you can also visit my site which indicated below.

    n8fan.net

    www.n8fan.net

    ReplyDelete
  2. I am very happy to locate your website. I just wanted to thank you for the time you spent on this great article. I definitely enjoyed reading it and I have you bookmarked to check out new stuff you post.


    Yong
    www.gofastek.com

    ReplyDelete