5.6. 主鍵/外鍵

5.6. 主鍵/外鍵
上一頁	第 5 章數據定義（DDL）	下一頁

5.6.1. 主鍵

			
下面書寫方式，推薦第二種，比較清晰。
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

5.6.2. 外鍵約束

			
下面第前兩種寫法不推薦。第三、四種寫法較清晰。
1.      第一種書寫方式
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);
2.      第二種書寫方式
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);
3.      第三種書寫方式
CREATE TABLE table1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
4.      第四種書寫方式，在SQL腳本最後面添加外鍵約束
Alter table "groupmember" add  foreign key ("uid") references "user" ("id") on update restrict on delete restrict;
Alter table "groupmember" add  foreign key ("gid") references "group" ("id") on update restrict on delete restrict;
Alter table "rolemember" add  foreign key ("uid") references "user" ("id") on update restrict on delete restrict;
Alter table "rolemember" add  foreign key ("rid") references "role" ("id") on update restrict on delete restrict;
3.9.3   PostgreSQL 7.3.x 新增功能

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
類似 ON DELETE，還有 ON UPDATE 選項，它是在主鍵被修改（更新）的時候調用的。
以前我們刪除其它表中受外鍵約束的記錄，使用規則或觸發器來完成。現可以用CASCADE

5.6.3. 案例

層次遞歸-分類目錄

實現一個無限向下分類的目錄，例如：
計算機與互聯網
       免費資源
              軟件下載(3431)
壁紙/屏保/桌面(109)
免費電子賀卡(197)
代理伺服器(33)
免費電子郵箱(73)
免費主頁空間(75)
免費聊天室(11)
免費論壇(36)
軟件
       XXXXXXXX
       XXXXXXXX
       XXXXXXXX
       XXXXXXXX
硬件
互聯網
編程

資料結構定義

			
Drop table "directory" CASCADE;

Create table "directory"
(
    "id" Serial NOT NULL,
    "root_id" Integer NOT NULL Default 0,
    "name"    Varchar(20)NOT NULL ,
    "status"   boolean Default 'true',
    "created" Timestamp Default current_timestamp,
    "modified" Timestamp Default current_timestamp,
    UNIQUE (id,root_id),
    PRIMARY KEY ("id")
--    FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE
);
INSERT INTO directory (id,root_id,name) VALUES (0,0,'/');
Alter table "directory" add  FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE;
Create index "directory_index" on "directory" using btree ("id","root_id","name");

演示

			
數據存儲狀態：
Id
Root_id
Name
0
0
/
1
0
計算機
2
1
顯示器
3
1
滑鼠
4
1
主板
5
2
Samsung 顯示器
6
2
LG顯示器
7
2
SONY顯示器


上圖是一個分類目錄，當刪除子目錄時如果子目錄中有目錄或數據，將刪除這些數據和目錄

說明：
       id            目錄根
       root_id    REFERENCES id ON DELETE CASCADE當pk刪除時關聯的fk自動刪除
       name       目錄名
       status      狀態true可用，false不可用
       created    創建時間
       modified  修改時間

注意：
       因為使用了關聯欄位，所以不能在create table 中使用
       FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE
       因為插入記錄做參考表中的“id”欄位，創建表的中沒有數據，所以無法插入數據。
       先創建表，不定義FOREIGN KEY，然後初始化插入第一條數據：
INSERT INTO directory (id,root_id,name) VALUES (0,0,'/');
       再定義外建：
       Alter table "directory" add  FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE;

postgres=# Create table "directory"
postgres-# (
postgres(#     "id" Serial NOT NULL,
postgres(#     "root_id" Integer NOT NULL Default 0,
postgres(#     "name" Varchar(20)NOT NULL ,
postgres(#     "status"boolean Default 'true',
postgres(#     "created" Timestamp Default current_timestamp,
postgres(#     "modified" Timestamp Default current_timestamp,
postgres(#     UNIQUE (id,root_id),
postgres(#     PRIMARY KEY ("id")
postgres(# --    FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE
postgres(# );
NOTICE:  CREATE TABLE will create implicit sequence 'directory_id_seq' for SERIAL column 'directory.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'directory_pkey' for table 'directory'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'directory_id_key' for table 'directory'
CREATE TABLE
postgres=# INSERT INTO directory (id,root_id,name) VALUES (0,0,'/');
INSERT 17110 1
postgres=# Alter table "directory" add  FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE;
NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ALTER TABLE
postgres=# Create index "directory_index" on "directory" using btree ("id","root_id","name");
CREATE INDEX
postgres=# INSERT INTO directory (root_id,name) VALUES (0,'計算機');
INSERT 17116 1
postgres=# SELECT * from directory ;
 id | root_id |   name    | status |          created           |          modified
----+---------+-----------+--------+----------------------------+----------------------------
  0 |       0 | /         | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機 | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
(2 rows)

postgres=# INSERT INTO directory (root_id,name) VALUES (0,'金融');
INSERT 17117 1
postgres=# SELECT * from directory ;
 id | root_id |   name    | status |          created           |          modified
----+---------+-----------+--------+----------------------------+----------------------------
  0 |       0 | /         | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機 | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融    | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
(3 rows)

postgres=# INSERT INTO directory (root_id,name) VALUES (1,'顯示器');
INSERT 17118 1
postgres=# INSERT INTO directory (root_id,name) VALUES (1,'滑鼠');
INSERT 17119 1
postgres=# INSERT INTO directory (root_id,name) VALUES (1,'主板');
INSERT 17120 1
postgres=# SELECT * from directory ;
 id | root_id |   name    | status |          created           |          modified
----+---------+-----------+--------+----------------------------+----------------------------
  0 |       0 | /         | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機 | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融    | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
  3 |       1 | 顯示器 | t      | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
  4 |       1 | 滑鼠    | t      | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
  5 |       1 | 主板    | t      | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
(6 rows)

postgres=# INSERT INTO directory (root_id,name) VALUES (3,'Samsung 顯示器');
INSERT 17121 1
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'LG顯示器');
INSERT 17122 1
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'SONY顯示器');
INSERT 17123 1
postgres=# SELECT * from directory ;
 id | root_id |       name        | status |          created           |          modified
----+---------+-------------------+--------+----------------------------+----------------------------
  0 |       0 | /                 | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機         | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融            | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
  3 |       1 | 顯示器         | t      | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
  4 |       1 | 滑鼠            | t      | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
  5 |       1 | 主板            | t      | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
  6 |       3 | Samsung 顯示器 | t      | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
  7 |       3 | LG顯示器       | t      | 2003-11-12 17:01:03.736121 | 2003-11-12 17:01:03.736121
  8|       3 | SONY顯示器     | t      | 2003-11-12 17:01:18.257337 | 2003-11-12 17:01:18.257337
(9 rows)

postgres=# INSERT INTO directory (root_id,name) VALUES (7,'CRT顯示器');
INSERT 17124 1
postgres=# INSERT INTO directory (root_id,name) VALUES (7,'液晶顯示器');
INSERT 17125 1
postgres=# INSERT INTO directory (root_id,name) VALUES (8,'液晶顯示器');
INSERT 17126 1
postgres=# INSERT INTO directory (root_id,name) VALUES (8,'特利隆顯示器');
INSERT 17127 1
postgres=# INSERT INTO directory (root_id,name) VALUES (7,'鑽石隆顯示器');
INSERT 17128 1
postgres=# SELECT * from directory ;
 id | root_id |        name        | status |          created           |          modified
----+---------+--------------------+--------+----------------------------+----------------------------
  0 |       0 | /                  | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機          | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融             | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
  3 |       1 | 顯示器          | t      | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
  4 |       1 | 滑鼠             | t      | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
  5 |       1 | 主板             | t      | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
  6 |       3 | Samsung 顯示器  | t      | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
  7 |       3 | LG顯示器        | t      | 2003-11-12 17:01:03.736121 | 2003-11-12 17:01:03.736121
  8 |       3 | SONY顯示器      | t      | 2003-11-12 17:01:18.257337 | 2003-11-12 17:01:18.257337
  9 |       7 | CRT顯示器       | t      | 2003-11-12 17:03:05.594891 | 2003-11-12 17:03:05.594891
 10 |       7 | 液晶顯示器    | t      | 2003-11-12 17:03:21.793674 | 2003-11-12 17:03:21.793674
 11 |       8 | 液晶顯示器    | t      | 2003-11-12 17:03:30.688531 | 2003-11-12 17:03:30.688531
 12 |       8 | 特利隆顯示器 | t      | 2003-11-12 17:03:57.697321 | 2003-11-12 17:03:57.697321
 13 |       7 | 鑽石隆顯示器 | t      | 2003-11-12 17:04:28.61153  | 2003-11-12 17:04:28.61153
(14 rows)

測試：
1.         刪除子目錄：計算機/顯示器/ LG顯示器/ CRT顯示器
CRT顯示器的id是9
SQL：DELETE FROM directory WHERE id=9;
postgres=# DELETE FROM directory WHERE id=9;
DELETE 1
postgres=# SELECT * from directory ;
 id | root_id |        name        | status |          created           |          modified
----+---------+--------------------+--------+----------------------------+----------------------------
  0 |       0 | /                  | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機          | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融             | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
  3 |       1 | 顯示器          | t      | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
  4 |       1 | 滑鼠             | t      | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
  5 |       1 | 主板             | t      | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
  6 |       3 | Samsung 顯示器  | t      | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
  7 |       3 | LG顯示器        | t      | 2003-11-12 17:01:03.736121 | 2003-11-12 17:01:03.736121
  8 |       3 | SONY顯示器      | t      | 2003-11-12 17:01:18.257337 | 2003-11-12 17:01:18.257337
 10 |       7 | 液晶顯示器    | t      | 2003-11-12 17:03:21.793674 | 2003-11-12 17:03:21.793674
 11 |       8 | 液晶顯示器    | t      | 2003-11-12 17:03:30.688531 | 2003-11-12 17:03:30.688531
 12 |       8 | 特利隆顯示器 | t      | 2003-11-12 17:03:57.697321 | 2003-11-12 17:03:57.697321
 13 |       7 | 鑽石隆顯示器 | t      | 2003-11-12 17:04:28.61153  | 2003-11-12 17:04:28.61153
(13 rows)

postgres=#
2.         刪除子目錄：計算機/顯示器/ LG顯示器
LG顯示器目錄下的子目錄：液晶顯示器、鑽石隆顯示器也將被刪除
postgres=# DELETE FROM directory WHERE id=7;
DELETE 1
postgres=# SELECT * from directory ;
 id | root_id |        name        | status |          created           |          modified
----+---------+--------------------+--------+----------------------------+----------------------------
  0 |       0 | /                  | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機          | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融             | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
  3 |       1 | 顯示器          | t      | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
  4 |       1 | 滑鼠             | t      | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
  5 |       1 | 主板             | t      | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
  6 |       3 | Samsung 顯示器  | t      | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
  8 |       3 | SONY顯示器      | t      | 2003-11-12 17:01:18.257337 | 2003-11-12 17:01:18.257337
 11 |       8 | 液晶顯示器    | t      | 2003-11-12 17:03:30.688531 | 2003-11-12 17:03:30.688531
 12 |       8 | 特利隆顯示器 | t      | 2003-11-12 17:03:57.697321 | 2003-11-12 17:03:57.697321
(10 rows)

3.         再刪除：計算機/顯示器/ SONY顯示器
postgres=# DELETE FROM directory WHERE id=8;
DELETE 1
postgres=# SELECT * from directory ;
 id | root_id |       name        | status |          created           |          modified
----+---------+-------------------+--------+----------------------------+----------------------------
  0 |       0 | /                 | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機         | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融            | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
  3 |       1 | 顯示器         | t      | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
  4 |       1 | 滑鼠            | t      | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
  5 |       1 | 主板            | t      | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
  6 |       3 | Samsung 顯示器 | t      | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
(7 rows)


4.         刪除子目錄：計算機/顯示器
顯示器目錄下的子目錄：
下有目錄LG顯示器/ CRT顯示器、SONY顯示器/……、LG顯示器/……
刪除顯示器目錄後，下的所有子目錄將被刪除。
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'LG顯示器');
INSERT 17129 1
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'SONY顯示器');
INSERT 17130 1
postgres=# INSERT INTO directory (root_id,name) VALUES (6,'CRT顯示器');
INSERT 17131 1
postgres=# INSERT INTO directory (root_id,name) VALUES (14,'CRT顯示器');
INSERT 17132 1
postgres=# INSERT INTO directory (root_id,name) VALUES (15,'CRT顯示器');
INSERT 17133 1
postgres=# INSERT INTO directory (root_id,name) VALUES (15,'特利隆顯示器');
INSERT 17134 1
postgres=# INSERT INTO directory (root_id,name) VALUES (15,'鑽石隆顯示器');
INSERT 17135 1
postgres=# INSERT INTO directory (root_id,name) VALUES (6,'液晶顯示器');
INSERT 17136 1
postgres=# INSERT INTO directory (root_id,name) VALUES (14,'液晶顯示器');
INSERT 17137 1
postgres=# INSERT INTO directory (root_id,name) VALUES (15,'液晶顯示器');
INSERT 17138 1
postgres=# SELECT * from directory ;
 id | root_id |        name        | status |          created           |          modified
----+---------+--------------------+--------+----------------------------+----------------------------
  0 |       0 | /                  | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機          | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融             | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
  3 |       1 | 顯示器          | t      | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
  4 |       1 | 滑鼠             | t      | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
  5 |       1 | 主板             | t      | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
  6 |       3 | Samsung 顯示器  | t      | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
 14 |       3 | LG顯示器        | t      | 2003-11-12 17:28:03.927651 | 2003-11-12 17:28:03.927651
 15 |       3 | SONY顯示器      | t      | 2003-11-12 17:28:15.235316 | 2003-11-12 17:28:15.235316
 16 |       6 | CRT顯示器       | t      | 2003-11-12 17:28:49.586084 | 2003-11-12 17:28:49.586084
 17 |      14 | CRT顯示器       | t      | 2003-11-12 17:28:55.290861 | 2003-11-12 17:28:55.290861
 18 |      15 | CRT顯示器       | t      | 2003-11-12 17:28:59.731191 | 2003-11-12 17:28:59.731191
 19 |      15 | 特利隆顯示器 | t      | 2003-11-12 17:29:10.747115 | 2003-11-12 17:29:10.747115
 20 |      15 | 鑽石隆顯示器 | t      | 2003-11-12 17:29:30.770079 | 2003-11-12 17:29:30.770079
 21 |       6 | 液晶顯示器    | t      | 2003-11-12 17:29:47.006177 | 2003-11-12 17:29:47.006177
 22 |      14 | 液晶顯示器    | t      | 2003-11-12 17:29:51.904914 | 2003-11-12 17:29:51.904914
 23 |      15 | 液晶顯示器    | t      | 2003-11-12 17:29:57.355213 | 2003-11-12 17:29:57.355213
(17 rows)

postgres=# DELETE FROM directory WHERE id=3;
DELETE 1
postgres=# SELECT * from directory ;
 id | root_id |   name    | status |          created           |          modified
----+---------+-----------+--------+----------------------------+----------------------------
  0 |       0 | /         | t      | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
  1 |       0 | 計算機 | t      | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
  2 |       0 | 金融    | t      | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
  4 |       1 | 滑鼠    | t      | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
  5 |       1 | 主板    | t      | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
(5 rows)
不再舉例了，刪除id=0將刪除計算機包括下面的所有目錄被刪除。
注意，千萬不要刪除id=0。

總結: 分類目錄的例子中使用了ON DELETE CASCADE，方便了操作，但也有危險。如果不用ON DELETE CASCADE而用程序來實現，需要使用遞歸算法，非常麻煩。

上一頁	上一級	下一頁
5.5. 約束	起始頁	第 6 章 DML