Primary Key Constraints

Primary key constraint enables us to identify each row in a given table. A primary key column could not be NULL, it should have a unique value in a table and there could only be one primary key constraint in a table. The above statement doesn’t mean there should only be one column with primary key constraint but we could have a combination of columns as our primary key.
It is possible to select or more of our candidate keys to serve as a primary key for our table or we could generate a unique set of numbers to represent each row or of our table. These kinds of columns are also known as surrogate keys.
To create a primary key for new table we could do that as

CREATE TABLE table1
(
col1 INT NOT NULL IDENTITY,
col2 VARCHAR(20),
CONSTRAINT pk_table1_col1 PRIMARY KEY (col1)
);

To create a primary key constraint for an existing table

ALTER TABLE table1
ADD CONSTRAINT pk_table1_col1 PRIMARY KEY (col1);

PK_Table1_col1 is a constraint name for our table and we could use any descriptive name instead of that.
We could check our primary key constraint is created by using

SELECT *
FROM sys.key_constraints
WHERE type = ‘PK’
AND name = ‘pk_table1_col1’;

To drop primary key constraints make sure there are no related tables to our table then

ALTER TABLE table1
DROP CONSTRAINT pk_table1_col1;

You can create a Primary Key constraint for temporary tables. But unlike other regular tables it is better not to give name for the primary key constraint or drop the constraint before you drop the temp table.
Let us see an example so that it will be so clear. Let us say you are working one some ETL project and you are using a temp table frequently creating it then dropping after you are done with it.

–first time
CREATE TABLE #table1
(
col1 INT,
col2 VARCHAR(20),
CONSTRAINT pk_#table1 PRIMARY KEY (col1)
);
go

DROP TABLE #table1;
go

so far no problem but let us try to recreate the table again.

–second time
CREATE TABLE #table1
(
col1 INT,
col2 VARCHAR(20),
CONSTRAINT pk_#table1 PRIMARY KEY (col1)
);
go

Then you will get an error similar to the one above (There is already an object named ‘’ in the database). This means dropping the temp table doesn’t automatically drop the primary key constraint on it. We could check that by using either of the following methods

SELECT *
FROM   sys.key_constraints
WHERE  type = ‘PK’
AND name = ‘pk_#table1’;

SELECT *
FROM   sys.objects
WHERE  name = ‘pk_#table1’;

To avoid this problem we could either create a primary key constraint without name and let sql server create a unique name for us

CREATE TABLE #table1
(
col1 INT PRIMARY KEY,
col2 VARCHAR(20)
);
go

Or if we create the constraint with the name drop the constraint first before we drop the temp table as

ALTER TABLE #table1
DROP CONSTRAINT pk_#table1;
DROP TABLE #table1;
go