Skip to content

Instantly share code, notes, and snippets.

@parsonsmatt parsonsmatt/animal.sql
Last active Mar 23, 2019

Embed
What would you like to do?
-- 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.
@xzilla

This comment has been minimized.

Copy link

xzilla commented Mar 23, 2019

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

@xzilla

This comment has been minimized.

Copy link

xzilla commented Mar 23, 2019

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

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.