animal.sql

-- 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.
CREATE TABLE animal (
    id SERIAL NOT NULL,
    type animal_type NOT NULL,

    PRIMARY KEY (id, type),
    UNIQUE id
);

CREATE TABLE cat (
    id INTEGER NOT NULL,
    type animal_type NOT NULL DEFAULT ('cat') CHECK (type = 'cat'),
    name TEXT NOT NULL,

    FOREIGN KEY (id, type) REFERENCES animal(id, type)
);

CREATE TABLE owner (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE dog (
    ID INTEGER NOT NULL,
    type animal_type NOT NULL DEFAULT ('dog') CHECK (type = 'dog'),
    owner_id INTEGER NOT NULL REFERENCES owner (id),
    
    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 ('cat');
-- Returns id 1
INSERT INTO cat (id, name) VALUES (1, 'macho');
COMMIT;

-- Now we insert a dog.
BEGIN;
INSERT INTO animal ('dog');
-- Returns id 2
INSERT INTO dog (id, owner_id) VALUES (1, 1);
COMMIT;

SELECT * FROM animal;
-- (1, 'cat')
-- (2, 'dog')

-- 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.

-- First we create the animal_type.
CREATE TYPE animal_type AS ENUM ('cat', 'dog');

-- Then we create the animal table with a primary key consisting of an
-- auto-incremented integer and the animal type.
CREATE TABLE animal (
id SERIAL UNIQUE NOT NULL,
type animal_type UNIQUE NOT NULL,

PRIMARY KEY (id, type)

);

INSERT INTO animal (type) VALUES ('cat') RETURNING *;
-- 1,'cat'
INSERT INTO animal (type) VALUES ('dog') RETURNING *;
-- 2,'dog'

CREATE TABLE cat (
id INTEGER NOT NULL DEFAULT (1) CHECK (id = 1),
name TEXT NOT NULL,
FOREIGN KEY (id) REFERENCES animal(id)
);

CREATE TABLE owner (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE dog (
ID INTEGER NOT NULL DEFAULT (2) CONSTRAINT "animal_type_is_dog" CHECK (id=2) CHECK animal_type (id=2),
owner_id INTEGER NOT NULL REFERENCES owner (id),
FOREIGN KEY (id) REFERENCES animal(id)
);

INSERT INTO owner (name) VALUES ('Jim') RETURNING *;
-- (1, 'Jim')

-- Now we insert a cat.
INSERT INTO cat (id, name) VALUES (1, 'macho');

-- Now we insert a dog.
INSERT INTO dog (id, owner_id) VALUES (2, 1);

-- 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
-- The schema forbids us from inserting invalid data.
-- Error should look like "ERROR: new row for relation "dog" violates check constraint "animal_type_is_dog"

-- VERSION 2

-- First we create the animal_type.
CREATE TYPE animal_type AS ENUM ('cat', 'dog');

-- Then we create the animal table with a primary key consisting of an
-- auto-incremented integer and the animal type.
CREATE TABLE animal (
type animal_type PRIMARY KEY
);

INSERT INTO animal (type) VALUES ('cat');
INSERT INTO animal (type) VALUES ('dog');

CREATE TABLE cat (
type animal_type NOT NULL DEFAULT 'cat' CHECK (type='cat'),
name TEXT PRIMARY KEY NOT NULL,
FOREIGN KEY (type) REFERENCES animal
);

CREATE TABLE owner (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE dog (
type animal_type NOT NULL CHECK (type='dog'),
owner_id INTEGER NOT NULL REFERENCES owner (id),
FOREIGN KEY (type) REFERENCES animal
);

INSERT INTO owner (name) VALUES ('Jim') RETURNING *;
-- (1, 'Jim')

-- Now we insert a cat.
INSERT INTO cat (name) VALUES ('macho') RETURNING *;

-- Now we insert a dog.
INSERT INTO dog (type, owner_id) VALUES ('dog', 1);

SELECT * FROM animal;
-- ('cat')
-- ('dog')

-- Let's try doing something illegal. We want to insert a 'dog' record with
-- id 1.

INSERT INTO dog (type, owner_id) VALUES ('cat', 1);
-- This will fail
-- The schema forbids us from inserting invalid data.