Articles Cookbook
Back to list
Standard SQL

How to Create a Primary Key in SQL

Database:

Operators:

CREATE TABLE, PRIMARY KEY, ALTER TABLE, ADD CONSTRAINT, ADD PRIMARY KEY

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 (new table):

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 (new table):

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 (existing table):

ALTER TABLE product
ADD PRIMARY KEY (id);

Discussion:

It is also possible to define 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 (existing table, primary key constraint):

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).

Recommended courses:

Recommended articles:

See also:

go to top