迹忆客 专注技术分享

在我们的文章中，我们主要在名为 pgAdmin [app. number]，可从 PostgreSQL 网站下载。因此，我们希望你已经安装了应用程序或其他替代方案，你可以在其中运行我们作为解决方案提供的查询。

今天，我们将学习如何在 PostgreSQL 中使用 FOREIGN KEYS。

在 PostgreSQL 中使用 References 施加外键关系

所以让我们开始创建我们的两个表。第一个将是一个 identity_number 表，里面有所有的人的 id。

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

第二个表是 person_details 表，它存储所有人员的信息以及从第一个表引用的 id。

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

你将在此查询中看到引用表 identity_number 的 identity。因此，person_details 中的 identity 现在使用 identity_number 表中的 id 建立 foreign_key 约束。

但是我们的数据库如何知道它所引用的表中的哪个键呢？

在第一个表中，我们已经将 id 定义为 PRIMARY KEY。因此，当我们引用 identity_number 表时，它会自动引用存在的 PRIMARY KEY。

而且因为一张表不能有多个主键，所以它非常有意义。

我们确实希望你知道为什么只有父表的 PRIMARY 键可以是 FOREIGN 键，对吧？如果它不是唯一的，多对多关系将违反数据完整性。

因此，我们总是选择 PRIMARY KEY 作为 FOREIGN KEY。

但是，如果你想更具体，可以使用它。

identity int not null references identity_number(id),

或者

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

现在让我们测试一下。所以我们将继续在两个表中插入一些值。

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

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

如果你运行这个会发生什么？因为第一个表中没有值为 4 的 id。

因此，当我们将数据集 (4, 'Mack', '2001-05-05') 插入子表：person_details 时，它会抛出错误。

输出：

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".

PostgreSQL 中 Reference 方法的可能增强

即使上述方法在几乎所有情况下都可以正常工作，但由于应用程序版本问题或其他情况，你可能会遇到错误。

在这种情况下，你可以尝试以下代码。

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
);

这明确提到了带有 FOREIGN KEY RELATIONSHIP 名称的 CONSTRAINT 并建立了连接。

如果你可能已经创建了表，你可以稍后使用 ALTER 语句添加关系。

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

PostgreSQL 中多对多关系的外键约束

现在让我们制作三张不同的表，一个是 cat，它的朋友是一只特定的 dog，另一张是拥有它们的 person。

人：

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

狗：

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

猫：

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

现在让我们在这三个表中插入一些值。

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);

所以，有三只狗； 11 属于 Mack，12 属于 John，13 属于 Anthony。

另外，有两只猫，第一只猫有一个朋友 11 和主人 Mack，第二只猫有一个朋友 12 和主人 Anthony。

如果我们倾向于删除狗 11，我们会遇到错误。为什么？因为猫也有 11 作为朋友，所以删除狗会使这一行变成 null。

输出：

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".

所以为了避免这个错误并删除依赖于这个 dog 的 cat，我们可以添加下面的语句。

animal_friend_tag int references dog on delete cascade

ON DELETE CASCADE 也会删除猫。如果你使用：

animal_friend_tag int references dog on delete restrict

它将再次抛出 EXCEPTION VIOLATION ERROR。

你还可以使用：

animal_friend_tag int references dog on delete set null;

或者

animal_friend_tag int references dog on delete set default;

但是只有在声明的变量没有设置为 NOT NULL 时才这样做。因为我们的变量设置为 NOT NULL，这将引发违规异常。