PostgreSQL - Day 7 - Part 1

• Sun, Jun 12, 2022
• 2-minute read

Keep learning PostgreSQL by Learn PostgreSQL Tutorial - Full Course for Beginners.

In this part, we’ll learn Primary Key and Constraint.

36. Primary Keys

The primary key is a value in a column which uniquely identifies a record in any table.

Both people have the same name. To distinguish them, we have to use the primary key(id), unique to each record.

37. Understanding Primary Keys

This section will help you understand how to work with primary keys.

Let’s check the schema of the person table.

We set id as the primary key previously.

Thus, you can’t insert a record with an existing primary key.

Experiment: Remove the primary key

You can drop the primary key constraint by altering the table and dropping the actual constraint.

ALTER TABLE person DROP CONSTRAINT person_pkey;

Now the person table doesn’t have a primary_key

Without the primary key, we can insert the same record into the person table. However, we can’t distinguish these records.

38. Adding Primary Key

ALTER TABLE person ADD PRIMARY KEY (id);

The argument for adding a primary key is an array because you can compose a primary key based on multiple columns.

We can’t add a primary key when the rows are not unique.

Thus, we have to delete the duplicate record first.

Then add it back.

Now we can add the primary key back.

39. Unique Constraints

A Unique Constraint allows us to have unique values for a given column.

ALTER TABLE person ADD CONSTRAINT unique_email_address UNIQUE(email);

Now you can’t add a row with an existing email.

There is another way to create a unique constraint while Postgres determine the constraint name.

ALTER TABLE person ADD UNIQUE (email);

40. Check Constraints

allows us to add a constraint based on a Boolean condition.

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK <conditions>