Last active
March 23, 2019 23:43
-
-
Save parsonsmatt/ffdf4538358c33bfe2e1912eab5db0d0 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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. | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-- 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.