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: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID); [/cc] To create a non clustered primary key on an existing table: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonID); [/cc] To create a composite primary key on an existing table: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID, DOB); [/cc] 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: [cc lang=”sql”] CREATE TABLE [dbo].[Person]( PersonID [int] IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED NOT NULL, FirstName VARCHAR(50) NULL ) [/cc] 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. [cc lang=”sql”] CREATE TABLE [dbo].[Person]( PersonID […]