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_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
,這將引發違規異常。
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