Primary Keys

tags: #sql/constraints

What is the primary key?

The PRIMARY KEY constraint clause uniquely identifies each record in a table.

Primary keys must contain UNIQUE values (i.e., cannot be duplicated), and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

How to specify the primary key constraint

CREATE TABLE table_name(
	column1 datatype constraint, // e.g. NOT NULL
	column2 datatype constraint,
	column3 datatype constraint,
	...
	PRIMARY KEY (column1)
)

Alternatively, we can specify the PK by adding PRIMARY KEY as a constraint corresponding to the column:

CREATE TABLE table_name(
	pk_id INT NOT NULL PRIMARY KEY UNIQUE, 
	...
)

Dropping (& Changing) a Primary Key

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons  
DROP PRIMARY KEY;

After dropping the PK, we can specify a new PK as follows:

ALTER TABLE my_table
DROP PRIMARY KEY,
ADD PRIMARY KEY (new_PK);