PostgreSQL 中的外键语法

Bilal Shahid 2023年1月30日
  1. 在 PostgreSQL 中使用 References 施加外键关系
  2. PostgreSQL 中 Reference 方法的可能增强
  3. PostgreSQL 中多对多关系的外键约束
PostgreSQL 中的外键语法

在我们的教程中,我们主要在名为 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_numberidentity。因此,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');

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

因此,当我们将数据集 (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 属于 Mack12 属于 John13 属于 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".

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

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,这将引发违规异常。

作者: Bilal Shahid
Bilal Shahid avatar Bilal Shahid avatar

Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!

GitHub