totn SQL

SQL: Primary Keys

This SQL tutorial explains how to create and drop a primary key in SQL with syntax and examples.

What is a primary key in SQL?

In SQL, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a NULL value. A table can have only one primary key.

You use either the CREATE TABLE statement or the ALTER TABLE statement to create a primary key in SQL.

Create Primary Key (CREATE TABLE statement)

A primary key can be created when you execute a CREATE TABLE statement in SQL.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in SQL is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)
);

OR

CREATE TABLE table_name
(
  column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
  column2 datatype [ NULL | NOT NULL ],
  ...
);
table_name
The name of the table that you wish to create.
column1, column2
The columns that you wish to create in the table.
constraint_name
The name of the primary key.
pk_col1, pk_col2, ... pk_col_n
The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in SQL. We will start with a very simple one where our primary key consists of just one column.

For example:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the suppliers table called suppliers_pk. It consists of only one column - the supplier_id column.

We could have used the alternate syntax and created this same primary key as follows:

CREATE TABLE suppliers
( supplier_id int CONSTRAINT suppliers_pk PRIMARY KEY,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

Both of these syntaxes are valid when creating a primary key with only one field.

If you create a primary key that is made up of 2 or more columns, you are limited to using only the first syntax where the primary key is defined at the end of the CREATE TABLE statement.

For example:

CREATE TABLE contacts
( last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25) NOT NULL,
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (last_name, first_name)
);

This example creates a primary key called contacts_pk that is made up of a combination of the last_name and first_name columns. So each combination of last_name and first_name must be unique in the contacts table.

Create Primary Key (ALTER TABLE statement)

If your table already exists and you wish to add a primary key later, you can use the ALTER TABLE statement to create a primary key.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in SQL is:

ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
    PRIMARY KEY (column1, column2, ... column_n);
table_name
The name of the table to modify. This is the table that you wish to add a primary key to.
constraint_name
The name of the primary key.
column1, column2, ... column_n
The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in SQL. So say, we already have a suppliers table created in our database. We could add a primary to the suppliers table with the following ALTER TABLE statement:

ALTER TABLE suppliers
  ADD CONSTRAINT suppliers_pk 
    PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing suppliers table called suppliers_pk. It consists of the supplier_id column.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE suppliers
  ADD CONSTRAINT suppliers_pk
    PRIMARY KEY (supplier_id, supplier_name);

This example would created a primary key called suppliers_pk that is made up of a combination of the supplier_id and supplier_name columns.

Drop Primary Key

In SQL, you can drop a primary key using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key in SQL is:

ALTER TABLE table_name
  DROP PRIMARY KEY;
table_name
The name of the table to modify. This is the table whose primary key you wish to drop.

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in SQL.

ALTER TABLE suppliers
  DROP PRIMARY KEY;

In this example, we've dropped the primary key on the suppliers table. We do not need to specify the name of the primary key as there can only be one primary key on a table.

Share on: