PostgreSQL 中的外來鍵語法

Bilal Shahid 2023年1月30日 2022年5月14日
  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,這將引發違規異常。

Author: 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