UPDATE … FROM for database normalisation

Normalisation?

Normalisation is the process of reducing the “copies” of data in a database, for example, in a fixtures table having a foreign-key to a sport table rather than repeating the value Football in each of the fixtures.

fixtures

id	sport	home_team	away_team
1	Football	Liverpool	Everton
2	Football	Newcastle	Chelsea
3	Football	Sunderland	Arsenal

Might become:

sports

id	sport
1	Football

fixtures

id	sport_id	home_team	away_team
1	1	Liverpool	Everton
2	1	Newcastle	Chelsea
3	1	Sunderland	Arsenal

home_team and away_team should probably also be foreign-keys to a teams table.

Migration queries

Create the new sports table:

CREATE TABLE sports (
  id SERIAL PRIMARY KEY NOT NULL,
  Name VARCHAR(100) NOT NULL
);

Insert all of the sports:

INSERT INTO sports (name)
  SELECT DISTINCT sport FROM fixtures;

Add the sport_id foreign-key but don’t validate as there won’t be any values yet:

ALTER TABLE fixtures 
  ADD COLUMN sport_id INT;
ALTER TABLE fixtures
  ADD CONSTRAINT fixtures_sport_id
    FOREIGN KEY (sport_id)
    REFERENCES sports (id) NOT VALID;

Update all of the fixtures with the correct value for sport_id:

UPDATE fixtures SET sport_id = sports.id
FROM sports
WHERE fixtures.sport = sports.name;

Validate the foreign-key constraint:

ALTER TABLE fixtures
  VALIDATE CONSTRAINT fixtures_sport_id;