JIYIK CN >

In our article, we mainly work on the PostgreSQL application called pgAdmin [app. number], which can be downloaded from the PostgreSQL website. Therefore, we hope that you already have the application installed or another alternative where you can run the queries we provide as a solution.

Today, we will learn how to use it in PostgreSQL FOREIGN KEYS.

ReferencesTo enforce a foreign key relationship in PostgreSQL, use

So let's start by creating our two tables. The first one is going to be a identity_numbertable with all the people in it id.

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

The second table is person_detailstable which stores the information of all persons along with their 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 will see in this query that the references the table identity_number. identityTherefore, the constraint person_detailsin identitynow uses identity_numberthe in table .idforeign_key

But how does our database know which key in the table it is referencing?

In the first table, we have iddefined as PRIMARY KEY. Therefore, when we reference identity_numberthe table, it automatically references the existing PRIMARY KEY.

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

We do hope you know why only the parent table's PRIMARYkey can be FOREIGNthe key, right? If it is not unique, 多对多the relationship will violate data integrity.

Therefore, we always choose PRIMARY KEYas FOREIGN KEY.

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

identity int not null references identity_number(id),

or

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

Now let's test this. So we will go ahead and insert some values ​​into 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 happens if you run this? Because there is no value for in the first 4table id.

So when we insert the dataset (4, 'Mack', '2001-05-05')into the child table:, person_detailsit throws 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".

ReferencePossible enhancements to methods in PostgreSQL

Even though the above method works fine in almost all cases, you may encounter errors due to app version issues or other situations.

In this case, you can 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 with FOREIGN KEY RELATIONSHIPthe name CONSTRAINTand establishes the connection.

If you have already created the tables you may be able ALTERto add relations later using the statement.

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

多对多Foreign key constraints on relations in PostgreSQL

Now let's make three different tables, one for catwhich friends are a specific one dogand another for which have them person.

people:

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 us 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; 11belongs Mack, 12belongs John, and 13belongs Anthony.

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

If we tend to remove the dog 11, we will get an error. Why? Because the cat also has 11as a friend, so removing the dog will make this row become 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 remove dependencies on this dog, catwe can add the following statement.

animal_friend_tag int references dog on delete cascade

ON DELETE CASCADEwill also delete cat. If you use:

animal_friend_tag int references dog on delete restrict

It will throw again EXCEPTION VIOLATION ERROR.

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 this will only work if the variable being declared is not set to NOT NULL. Since our variable is set to NOT NULL, this will throw a Violation Exception.