Primary Key Constraints keep your rows unique, and therefore help maintain your datas integrity.
They can be applied to a single column, or across multiple columns (A maximum of 16). Any column or combination of columns referenced by the Primary Key will be unique, and because of this, they’re usually chosen as the Identity column as well.
A Primary Key column cannot be nullable, due to the fact that it forces uniqueness in the table. Computed columns are allowed to be used as a Primary Key.
When a Primary Key is created, the columns it’s created on also have a clustered index created on them. If you’d like to override this functionality, you can do so by specifying NONCLUSTERED during the creation of the key.
Adding a Primary Key Constraint to an existing table
ALTER TABLE Products ADD PRIMARY KEY (ProductId)
The above code will modify the Products table to add a Primary Key Constraint to the ProductId column.
ALTER TABLE Products ADD PRIMARY KEY NONCLUSTERED (ProductId, ProductName)
The above code will modify the Products table to add a Primary Key Constraint across two columns, but also overrides the default action of creating a clustered composite index, and creates a nonclustered composite index instead. What do I mean by composite? This means that the index references multiple columns.
Adding a Primary Key Constraint during table creation
CREATE TABLE Products ( ProductId INT PRIMARY KEY IDENTITY, ProductName VARCHAR(50))
In the above example, we’re adding the Primary Key to the integer column ProductId. The same column is also being specified as the identity column for the table, as I mentioned earlier, this is fairly common.
Removing a Primary Key Constraint
A table can only ever have one Primary Key, and you may reassess your needs and have to drop the existing Primary Key.
In order to do this, you’ll first need to find the name of the primary key that you’d like to drop. You can do this by executing the below code (replace “Schema.Table” with your details, but keep the single quotes):
SELECT I.name [PrimaryKeyName] FROM sys.tables T JOIN sys.indexes I ON I.OBJECT_ID = T.OBJECT_ID WHERE T.OBJECT_ID = OBJECT_ID('Schema.Table') AND I.is_primary_key = 1
Once you’ve run the above code, and the name has been returned, you can run the below code to drop the Primary Key:
ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint_name