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:
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