Surrogate keys
Introduction to Relational Databases in SQL
Timo Grossenbacher
Data Journalist
Surrogate keys
- Primary keys should be built from as few columns as possible
- Primary keys should never change over time
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
Adding a surrogate key with serial data type
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
Adding a surrogate key with serial data type (contd.)
INSERT INTO cars
VALUES ('Opel', 'Astra', 'green', 1);
duplicate key value violates unique constraint "id_pkey"
DETAIL: Key (id)=(1) already exists.
- "id" uniquely identifies records in the table – useful for referencing!
Another type of surrogate key
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);
Let's try this!
Introduction to Relational Databases in SQL
Surrogate keys Introduction to Relational Databases in SQL Timo Grossenbacher Data Journalist
