代理鍵

SQL 關聯式資料庫入門

Timo Grossenbacher

Data Journalist

代理鍵

主鍵應由最少的欄位組成
主鍵不應隨時間改變

     license_no     | serial_no |    make    |  model  | color
 -------------------+-----------+------------+---------+------
 Texas ABC-739      | A69352    | Ford       | Mustang | blue
 Florida TVP-347    | B43696    | Oldsmobile | Cutlass | black
 New York MPO-22    | X83554    | Oldsmobile | Delta   | silver
 California 432-TFY | C43742    | Mercedes   | 190-D   | champagne
 California RSK-629 | Y82935    | Toyota     | Camry   | red
 Texas RSK-629      | U028365   | Jaguar     | XJS     | blue

    make    |  model  | color
 -----------+---------+------
 Ford       | Mustang | blue
 Oldsmobile | Cutlass | black
 Oldsmobile | Delta   | silver
 Mercedes   | 190-D   | champagne
 Toyota     | Camry   | red
 Jaguar     | XJS     | blue

以 serial 型別新增代理鍵

ALTER TABLE cars
ADD COLUMN id serial PRIMARY KEY;

INSERT INTO cars
VALUES ('Volkswagen', 'Blitz', 'black');

    make    |  model  | color       | id
  ----------+---------+-------------+-------------
 Ford       | Mustang | blue        | 1
 Oldsmobile | Cutlass | black       | 2
 Oldsmobile | Delta   | silver      | 3
 Mercedes   | 190-D   | champagne   | 4
 Toyota     | Camry   | red         | 5
 Jaguar     | XJS     | blue        | 6
 Volkswagen | Blitz   | black       | 7

以 serial 型別新增代理鍵（續）

INSERT INTO cars
VALUES ('Opel', 'Astra', 'green', 1);

duplicate key value violates unique constraint "id_pkey"
DETAIL:  Key (id)=(1) already exists.

「id」在表中可唯一識別每筆紀錄——方便做參照！

另一種代理鍵

ALTER TABLE table_name
ADD COLUMN column_c varchar(256);

UPDATE table_name
SET column_c = CONCAT(column_a, column_b);

ALTER TABLE table_name
ADD CONSTRAINT pk PRIMARY KEY (column_c);

一起來練習吧！

SQL 關聯式資料庫入門