animal2.sql

DROP DATABASE IF EXISTS animalsdb;
CREATE DATABASE animalsdb;

\c animalsdb;

-- 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 ENUM ('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 (name) VALUES ('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.

INSERT INTO dog (id, type, owner_id) VALUES (1, 'cat', 1);

output.md

Now that the SQL is fixed you can run it via:

psql -U username < /path/to/sqlfile

Output would now look like this:

DROP DATABASE
CREATE DATABASE
You are now connected to database "animalsdb" as user "postgres".
CREATE TYPE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
 id | name
----+------
  1 | Jim
(1 row)

BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
BEGIN
INSERT 0 1
ERROR:  insert or update on table "dog" violates foreign key constraint "dog_id_fkey"
DETAIL:  Key (id, type)=(1, dog) is not present in table "animal".
ROLLBACK
 id | type | name
----+------+-------
  1 | cat  | macho
(1 row)

ERROR:  insert or update on table "dog" violates foreign key constraint "dog_id_fkey"
DETAIL:  Key (id, type)=(1, dog) is not present in table "animal".
 id | type | name
----+------+------
(0 rows)

ERROR:  new row for relation "dog" violates check constraint "dog_type_check"
DETAIL:  Failing row contains (1, cat, 1).