Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- We're modeling the following Haskell datatype:
--
-- data Animal = Cat Name Age | Dog Name OwnerId
--
-- We're going to factor the common 'Name' field into the animal table.
--
-- The data that is specific for each field will go on a table with that field.
-- First we create the animal_type.
CREATE TYPE animal_type AS ('cat', 'dog');
-- Then we create the animal table with a primary key consisting of an auto
-- incremented integer and the animal type. The animal table is the "supertype"
-- representation. Every animal will have an entry in this table, as well as an
-- entry in a "subtype" table. The subtype table for the animal in question is
-- indicated by the `type` column.
CREATE TABLE animal (
-- Which animal is it?
id SERIAL NOT NULL,
-- What kind of animal is it?
type animal_type NOT NULL,
-- What is the animal's name?
name TEXT NOT NULL,
PRIMARY KEY (id, type)
);
CREATE TABLE cat (
-- Which animal is it?
id INTEGER NOT NULL,
-- It *must* be a 'cat'. No choice here.
type animal_type
NOT NULL
DEFAULT ('cat')
CHECK (type = 'cat'),
-- How old is it?
age INTEGER NOT NULL,
-- Cats do not have owners, they have staff.
-- This foreign key constraint requires that the (id, type) pair is present
-- in the `animal` table. More specifically, it requires that the `id` in
-- the `animal` table have a `cat` type.
FOREIGN KEY (id, type) REFERENCES animal(id, type)
);
CREATE TABLE owner (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE dog (
-- Which animal is it?
id INTEGER NOT NULL,
-- Again it *must* be a dog! Cna't b
type animal_type
NOT NULL
DEFAULT ('dog')
CHECK (type = 'dog'),
-- All pups have a favorite person.
owner_id INTEGER NOT NULL REFERENCES owner (id),
-- As with `cat`, this ensures that the animal entry for each dog has the
-- right type.
FOREIGN KEY (id, type) REFERENCES animal(id, type)
);
INSERT INTO owner ('Jim');
SELECT * FROM owner;
-- (1, 'Jim')
-- Now we insert a cat.
BEGIN;
INSERT INTO animal (type, name) VALUES ('cat', 'macho');
-- Returns id 1
INSERT INTO cat (id, age) VALUES (1, 8);
COMMIT;
-- Now we insert a dog.
BEGIN;
INSERT INTO animal (type, name) VALUES ('dog', 'asher');
-- Returns id 2
INSERT INTO dog (id, owner_id) VALUES (1, 1);
COMMIT;
SELECT * FROM animal;
-- (1, 'cat', 'macho')
-- (2, 'dog', 'asher')
-- Let's try doing something illegal. We want to insert a 'dog' record with
-- id 1.
INSERT INTO dog (id, owner_id) VALUES (1, 1);
-- This will fail, because the fully specified record we are trying to insert
-- is:
-- (id = 1, type = 'dog', owner_id = 1)
-- When it goes to do the foreign key check, it will look in `animal` for a
-- record with that:
SELECT * FROM animal WHERE id = 1 AND type = 'dog';
-- This query will fail to return anything.
-- The schema forbids us from inserting invalid data.
@mbbx6spp

This comment has been minimized.

Copy link

mbbx6spp commented May 15, 2019

I fixed a couple of syntax issues in this forked Gist: https://gist.github.com/mbbx6spp/81482db6e3c8f61e1768651e200edc58/revisions

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.