SQL, Unique and Primary Keys: How to Create and Use Them in a Database

In a SQL database, it’s important to have a way to ensure uniqueness and identify individual rows in a table. This can be achieved by using unique and primary keys.

Let’s start with a table created using the following command:

CREATE TABLE people (
 age INT NOT NULL,
 name CHAR(20) NOT NULL
);

By default, this table allows duplicate rows and columns that repeat the same values. However, we can prevent this by adding the UNIQUE key constraint to a column. For example:

CREATE TABLE people (
 age INT NOT NULL,
 name CHAR(20) NOT NULL UNIQUE
);

Now, if we try to insert a duplicate entry for the ‘name’ column, such as ‘Flavio’ in this case, we will encounter an error like this:

1 2	ERROR: duplicate key value violates unique constraint "people_name_key" DETAIL: Key (name)=(Flavio) already exists.

A primary key is a special type of unique key that not only ensures uniqueness but also acts as the primary identifier for a row in the table. We can define a primary key using the PRIMARY KEY constraint. For instance:

CREATE TABLE people (
 age INT NOT NULL,
 name CHAR(20) NOT NULL PRIMARY KEY
);

The primary key can be a column like an email in a list of users or a unique id that is automatically assigned to each record. Regardless of the value, the primary key provides a reliable way to reference and identify a specific row in the table.

Tags: SQL, unique key, primary key, database design