BrevFeed
Sign in

11. Changing tables and linking them

📖 Reading · 6 min
💡 Most code boxes below are live — edit one and hit Run. Boxes without a Run button are reference-only (they need a real Postgres server).

Two things you'll do constantly once tables exist: change a table's shape after the fact, and connect one table to another.

ALTER TABLE

Tables aren't set in stone. ALTER TABLE changes them — most often to add a column:

CREATE TABLE product (id INTEGER, name VARCHAR);
ALTER TABLE product ADD COLUMN price DECIMAL(6,2);

INSERT INTO product VALUES (1, 'Pen', 1.25);
SELECT * FROM product;

Other common forms (syntax varies a little by engine):

ALTER TABLE product RENAME COLUMN name TO title;
ALTER TABLE product ALTER COLUMN price SET NOT NULL;
ALTER TABLE product DROP COLUMN price;

Foreign keys: linking tables

Real data is spread across tables that reference each other. A foreign key says "this column must point at a real row in that other table." It's how the database keeps relationships honest.

An order belongs to a buyer, so orders.buyer_id references buyer.id:

CREATE TABLE buyer (
  id   INTEGER PRIMARY KEY,
  name VARCHAR NOT NULL
);

CREATE TABLE orders (
  id       INTEGER PRIMARY KEY,
  buyer_id INTEGER REFERENCES buyer(id),
  total    DECIMAL(8,2)
);

INSERT INTO buyer VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO orders VALUES (100, 1, 25.00), (101, 2, 40.00);

SELECT o.id, b.name, o.total
FROM orders o
JOIN buyer b ON b.id = o.buyer_id
ORDER BY o.id;

Because of the foreign key, an order pointing at a customer who doesn't exist is rejected:

INSERT INTO orders VALUES (102, 999, 10.00);
-- error: no buyer with id 999 to reference

What happens when the parent is deleted?

You decide, with ON DELETE:

  • ON DELETE RESTRICT (default) — refuse to delete a buyer who still has orders.
  • ON DELETE CASCADE — delete the buyer and their orders.
  • ON DELETE SET NULL — keep the orders but blank out buyer_id.
CREATE TABLE orders (
  id       INTEGER PRIMARY KEY,
  buyer_id INTEGER REFERENCES buyer(id) ON DELETE CASCADE,
  total    DECIMAL(8,2)
);

Try it

Create a team table (id INTEGER PRIMARY KEY, name VARCHAR) and a player table (id INTEGER PRIMARY KEY, name VARCHAR, team_id INTEGER REFERENCES team(id)). Insert one team (1, 'Red') and two players on it, then list each player's name next to their team name, ordered by player id.

-- create both tables, insert 1 team and 2 players, then join them

CREATE TABLE team (
  id   INTEGER PRIMARY KEY,
  name VARCHAR
);
CREATE TABLE player (
  id      INTEGER PRIMARY KEY,
  name    VARCHAR,
  team_id INTEGER REFERENCES team(id)
);
INSERT INTO team VALUES (1, 'Red');
INSERT INTO player VALUES (1, 'Sam', 1), (2, 'Lee', 1);
SELECT player.name, team.name AS team
FROM player
JOIN team ON team.id = player.team_id
ORDER BY player.id;
← Previous Next →