SQL Server Add Primary Key

Adding a primary key can be done either after a table is created, or at the same a table is created. It is important to note, that by default a primary key is clustered. This may or may not be the preferred method of creation. For more information on clustered vs non clustered indexes, please see this article. Either way, you can specify the clustered / non clustered option when creating a primary key.

To create a clustered primary key on an existing table:

ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person
PRIMARY KEY CLUSTERED (PersonID);

To create a non clustered primary key on an existing table:

ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person
PRIMARY KEY NONCLUSTERED (PersonID);

To create a composite primary key on an existing table:

ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person
PRIMARY KEY CLUSTERED (PersonID, DOB);

For an existing table, there are two places that can be used to specify the primary key. The first is inline to the column. Using this method, you cannot create a composite primary key:

CREATE TABLE [dbo].[Person](
    PersonID [INT] IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED NOT NULL,
    FirstName VARCHAR(50) NULL
)

The second way is to specify the primary key after the creation of the table, this will enable you to create a composite primary key. Keep in mind that in order to use a column in the primary key, the field must not be nullable.

CREATE TABLE [dbo].[Person](
    PersonID [INT] IDENTITY(1,1) NOT NULL,
    FirstName VARCHAR(50) NOT NULL
    CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonID, FirstName)
)

Note: The “CONSTRAINT PK_Name” portion of the code above is optional, however if not specified a constraint name will be auto generated.

Prerequisites
The primary key column(s) must be unique for each row. If you are altering an existing table and trying to add a primary key to a column that is not unique, you will receive the error:

“The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.Person’ and the index name ‘PK_Person’. The duplicate key value is (1).”

6 comments
Anonymous 03 Dec 2011 at 5:13 am

i have one table which contain duplicate values.but now i want to give primary key on one field.can i?

Dario 28 Jan 2013 at 9:13 am

Insert select Group by for the key!

ravi 11 Aug 2011 at 2:00 am

How can we insert more than one Row at a time ?…

Derek Dieter 12 Aug 2011 at 10:21 pm

Hi Ravi,

Inserting more that one row at a time is the reason for SQL. SQL is known as a “SET” based language. Which means “more than one row at a time”. Programming languages are by nature “iterative”. Meaning one record at a time. So ultimately, inserting one row at a time is easy, depending on the source of the data. For you, I’m going to assume your best input method would be BCP. BCP allows multple rows contained in a file to be inserted in a set based method. Look into that and let me know if you have any questions.

Thanks,
Derek

keval 03 Aug 2011 at 9:26 pm

thanx……

Featured Articles

 Site Author