Foreign Key Syntax in PostgreSQL

1. Use References to Impose a Foreign Key Relationship in PostgreSQL
2. Possible Enhancement to the Reference Method in PostgreSQL
3. a Foreign Key Constraint on MANY to MANY Relationships in PostgreSQL

In our tutorials, we mostly use PostgreSQL on a Windows application called pgAdmin [app. number], downloadable from the PostgreSQL website. Hence, we expect you to have the application already installed or some other alternative in which you can run the queries we provide as solutions.

Today, we’ll be learning how to use FOREIGN KEYS in PostgreSQL.

Use References to Impose a Foreign Key Relationship in PostgreSQL

So let’s begin creating our two tables. The first one will be an identity_number table with all the ids of the respective persons.

create table identity_number(
	id int not null,
	PRIMARY KEY (id)
);

The second table would be the person_details table that stores all the persons’ information with the ids referenced from the first table.

create table person_details(
	identity int not null references identity_number,
	name varchar(50) not null,
	dob date not null
);

You’ll see the identity referencing the table identity_number in this query. Thus, identity in the person_details now establishes a foreign_key constraint with the id from the identity_number table.

But how does our database know which key from the table it refers to?

In the first table, we already defined id as the PRIMARY KEY. Thus, when we refer to the identity_number table, it automatically references the PRIMARY KEY present.

And because a table cannot have more than one primary key, thus it makes perfect sense.

And we do hope you know why only PRIMARY keys of the parent table can be FOREIGN keys, right? If it is not unique, many to many relationships will violate data integrity.

Hence, we always choose a PRIMARY KEY as a FOREIGN KEY.

However, if you want to be more specific, you may use this.

identity int not null references identity_number(id),

or

FOREIGN KEY(identity) references identity_number(id)
);

Let us test it out now. So we’ll go ahead and insert a few values in both tables.

insert into identity_number values(1), (2), (3);

insert into person_details values(1, 'John', '2001-04-04'), (4, 'Mack', '2001-05-05');

What will happen if you run this? Because there is no id in the first table with the value 4.

Hence when we insert the data set (4, 'Mack', '2001-05-05') into the child table: person_details, it will throw up an error.

Output:

ERROR:  insert or update on table "person_details" violates foreign key constraint "person_details_identity_fkey"
DETAIL:  Key (identity)=(4) is not present in table "identity_number".

Possible Enhancement to the Reference Method in PostgreSQL

Even though the above method works fine in almost all cases, you might have an error due to application version issues or other scenarios.

In that case, you may try the following code.

create table person_details(
	identity int not null,
	constraint fk_identity foreign key (identity) references identity_number (id),
	name varchar(50) not null,
	dob date not null
);

This explicitly mentions the CONSTRAINT with the FOREIGN KEY RELATIONSHIP name and establishes the connection.

If you might have already created the table, you can use the ALTER statement to add the relationship later.

alter table person_details
add constraint fk_identity
foreign key (identity)
references identity_number (id);

a Foreign Key Constraint on MANY to MANY Relationships in PostgreSQL

Let’s make three different tables now, a cat with one specific dog as its friend and the person that owns them both.

Person:

create table person(
	id int not null PRIMARY KEY,
	name varchar(50) not null
);

Dog:

create table dog(
	tag int PRIMARY KEY,
	owner_id int references person(id)
);

Cat:

create table cat(
	animal_friend_tag int references dog on delete cascade,
	owner_id int references person,
	PRIMARY KEY(animal_friend_tag, owner_id)
);

Now let’s insert some values into these three tables.

insert into person values(1, 'Mack'), (2, 'John'), (3, 'Anthony');

insert into dog values(11, 1), (12, 2), (13, 3);

insert into cat values(11, 1), (12, 3);

So, there are three dogs; 11 belongs to Mack, 12 belongs to John, and 13 belongs to Anthony.

Also, there are two cats, the first cat has a friend 11 and owner Mack, and the second cat has a friend 12 and the owner Anthony.

If we tend to delete dog 11, we will run into an error. Why? Because a cat also has 11 as a friend, deleting the dog will render this row null.

Output:

ERROR:  update or delete on table "dog" violates foreign key constraint "cat_animal_friend_tag_fkey" on table "cat"
DETAIL:  Key (tag)=(11) is still referenced from table "cat".

So to avoid this error and delete the cat that depends on this dog, we can add the statement below.

animal_friend_tag int references dog on delete cascade

The ON DELETE CASCADE deletes the cat as well. If you use:

animal_friend_tag int references dog on delete restrict

It will throw the EXCEPTION VIOLATION ERROR again.

You can also use:

animal_friend_tag int references dog on delete set null;

or

animal_friend_tag int references dog on delete set default;

But do this only when the declared variable is not set to NOT NULL. Because our variables are set to NOT NULL, this will throw an exception of violation.