代理键

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 关系型数据库入门