Contents

Create a primary key using SQL

A primary key field uniquely identifies a record in a table. In a properly designed database, each table has a primary key field.

A primary key field can be created at the moment the table itself is created, or it can be added later. It is usually created along with the table, because it is good practice to have a primary key for each table and in most situations it is needed right from the beginning to create relationships with other tables.

Here I will show you how to create a table with a primary key using SQL for various databases.

If you want to know more about what primary keys are and why they are important read tables and the primary key. For now, remember the following

  • Primary keys are used to uniquely identify a record in a table
  • Primary keys can not be null
  • Primary keys must be unique
  • Primary keys can be managed by the database or by the application or person that uses the databases. I will get to "autonumbering" fields that are managed by the database on the next page.

Create a table with primary key in Mysql, Postgressql, SQL Server and Oracle

The SQL CREATE statement below creates a customer table with a primary key called customer_id in Mysql or Postgresql.

CREATE TABLE Customer
(
customer_id INTEGER NOT NULL,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50),
birthdate DATE,
average_monthly_visits INTEGER,
PRIMARY KEY(customer_id)
)

It may not be required to add the NOT NULL constraint to the customer_id field, because most databases will automatically add a NOT NULL constraint when  they see a primary key field. But it's good to be explicit in the code you write, so that other people will understand what it does. That is why I included the NOT NULL constraint anyway.

Alternative way of writing

You can also include the PRIMARY KEY statement directly in the customer_id field declaration. This will work on Mysql, Postgressql, Sql Server and Oracle.

CREATE TABLE Customer
(
customer_id integer PRIMARY KEY NOT NULL,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50),
birthdate DATE,
average_monthly_visits INTEGER
)

Naming the primary key constraint

A primary key is a type of constraint. In database speak a constraint is a certain rule that is applied to a field. You will see this term often when you are worki with databases. In SQL, the word constraint is used to name constraints. If you would want to name the primary key of the Customer table "pk_customer" you would use the following SQL.

CREATE TABLE Customer
(
customer_id INTEGER NOT NULL,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50),
birthdate DATE,
average_monthly_visits INTEGER,
CONSTRAINT pk_customer PRIMARY KEY(customer_id)
)

This SQL code will work on all four databases mentioned earlier on this page. Naming your constraints explicitly is not a requirement for most projects, but in larger tables with multiple different constraints it can become hard to make sense of the constraint names databases generate themselves if you don't supply one.

If you use the SQL CREATE statement above to create your table in SQL server it will show you the constraint name under "indexes".

Create a named primary key constraint

Autonumbering primary key fields

The primary key examples you saw on this page all create a primary key field that is not managed by the database. For each new Customer record the user of the database will have to come up with a new unique number, or the database will complain and not insert the record.

On the next page I will show you how you put the database in charge of key generation so that you (or your application) don't have to worry about it.