How to Create a Primary Key in SQL

Problem

You want to create a primary key for a table in a database.

Example

We want a primary key for a table called product.

Solution 1: Creating new table with a single-column primary key

CREATE TABLE product (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  producer VARCHAR(100) NOT NULL,
  price DECIMAL(7,2)
);

Discussion

To create a new table with a column defined as the primary key, you can use the keyword PRIMARY KEY at the end of the definition of that column. In our example, we create the table product using a CREATE TABLE clause, with the names of the columns and their respective data types in parentheses. We choose the column id as the primary key of this table, so we designate PRIMARY KEY at the end of the definition of this column.

Keep in mind that you can create only one primary key for a given table.

Solution 2: Creating new table with a two-column primary key

CREATE TABLE product (
  name VARCHAR(100) NOT NULL,
  producer VARCHAR(100) NOT NULL),
  price DECIMAL(7,2),
  PRIMARY KEY(name, producer)
);

Discussion

Another way to define a primary key during the creation of a new table is with a PRIMARY KEY clause at the end of the column definitions. In this case, we designate one or more columns as an argument to the PRIMARY KEY clause; these columns become the primary key for the table.

Note that this approach allows primary keys built with multiple columns. In this example, the primary key for the table product is formed by two columns: name and producer.

Solution 3: Adding primary key to an existing table

ALTER TABLE product
ADD PRIMARY KEY (id);

Discussion

It is also possible to add a primary key for an existing table which does not yet have a primary key.

Here, the table is altered using an ALTER TABLE clause. The table (in our example, product) is named after the keyword ALTER TABLE, then the ADD PRIMARY KEY clause follows with the name of the column to be used as the primary key. Note that the table must already exist before this command is executed and the table must not already have a primary key.

Solution 4: Addind a named primary key to an existing table

ALTER TABLE product
ADD CONSTRAINT PK_product_id PRIMARY KEY (id);

Discussion

Use a query like this if you want to name a primary key constraint for an existing table. Here, the primary key constraint, PK_product_id, is generated from the column id. If you do not specify the constraint name, the database generates a default constraint name (the name will vary by database).