no_fk_enforced.sql

scratch=# CREATE TABLE bub (bub_id UUID PRIMARY KEY DEFAULT uuid_generate_v4());
CREATE TABLE
scratch=# CREATE TABLE foo (foo_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), bub_id UUID NOT NULL REFERENCES bub (bub_id) ON DELETE CASCADE);
CREATE TABLE
scratch=# CREATE TABLE subfoo (message TEXT) INHERITS (foo);
CREATE TABLE
scratch=# insert into bub DEFAULT VALUES;
INSERT 0 1
scratch=# insert into bub DEFAULT VALUES;
INSERT 0 1
scratch=# insert into bub DEFAULT VALUES;
INSERT 0 1
scratch=# select * from bub;
                bub_id                
--------------------------------------
 7996552e-ee9a-4e39-b0c3-04ab55151af1
 275b718b-f4b1-4b68-9273-a3db7476e321
 27bef356-79f6-4583-9900-2fa913b07f8f
(3 rows)

scratch=# insert into subfoo (bub_id) VALUES ('7996552e-ee9a-4e39-b0c3-04ab55151af1');
INSERT 0 1
scratch=# select * from bub;
                bub_id                
--------------------------------------
 7996552e-ee9a-4e39-b0c3-04ab55151af1
 275b718b-f4b1-4b68-9273-a3db7476e321
 27bef356-79f6-4583-9900-2fa913b07f8f
(3 rows)

scratch=# select * from foo;
                foo_id                |                bub_id                
--------------------------------------+--------------------------------------
 6292b1e5-7cb0-40d5-b618-9af1a3ca5d67 | 7996552e-ee9a-4e39-b0c3-04ab55151af1
(1 row)

scratch=# select * from subfoo;
                foo_id                |                bub_id                | message 
--------------------------------------+--------------------------------------+---------
 6292b1e5-7cb0-40d5-b618-9af1a3ca5d67 | 7996552e-ee9a-4e39-b0c3-04ab55151af1 | 
(1 row)

scratch=# delete from bub where bub_id = '7996552e-ee9a-4e39-b0c3-04ab55151af1';
DELETE 1
scratch=# select * from bub;
                bub_id                
--------------------------------------
 275b718b-f4b1-4b68-9273-a3db7476e321
 27bef356-79f6-4583-9900-2fa913b07f8f
(2 rows)

scratch=# select * from subfoo;
                foo_id                |                bub_id                | message 
--------------------------------------+--------------------------------------+---------
 6292b1e5-7cb0-40d5-b618-9af1a3ca5d67 | 7996552e-ee9a-4e39-b0c3-04ab55151af1 | 
(1 row)

scratch=# select * from foo
scratch-# ;
                foo_id                |                bub_id                
--------------------------------------+--------------------------------------
 6292b1e5-7cb0-40d5-b618-9af1a3ca5d67 | 7996552e-ee9a-4e39-b0c3-04ab55151af1
(1 row)

scratch=# drop table foo;
ERROR:  cannot drop table foo because other objects depend on it
DETAIL:  table subfoo depends on table foo
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
scratch=# drop table foo CASCADE;
NOTICE:  drop cascades to table subfoo
DROP TABLE
scratch=# drop table bub;
DROP TABLE
scratch=# CREATE TABLE bub (bub_id UUID PRIMARY KEY DEFAULT uuid_generate_v4());
CREATE TABLE
scratch=# CREATE TABLE foo (foo_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), bub_id UUID NOT NULL REFERENCES bub (bub_id) ON DELETE CASCADE);
CREATE TABLE
scratch=# CREATE TABLE subfoo (message TEXT, FOREIGN KEY bub (bub_id) ON DELETE CASCADE) INHERITS (foo);
ERROR:  syntax error at or near "bub"
LINE 1: CREATE TABLE subfoo (message TEXT, FOREIGN KEY bub (bub_id) ...
                                                       ^
scratch=# CREATE TABLE subfoo (message TEXT, FOREIGN KEY bub_id REFERENCES bub (bub_id) ON DELETE CASCADE) INHERITS (foo);
ERROR:  syntax error at or near "bub_id"
LINE 1: CREATE TABLE subfoo (message TEXT, FOREIGN KEY bub_id REFERE...
                                                       ^
scratch=# CREATE TABLE subfoo (message TEXT, FOREIGN KEY (bub_id) REFERENCES bub (bub_id) ON DELETE CASCADE) INHERITS (foo);
CREATE TABLE
scratch=# insert into bub DEFAULT VALUES;
INSERT 0 1
scratch=# insert into bub DEFAULT VALUES;
INSERT 0 1
scratch=# selct * from bub;
ERROR:  syntax error at or near "selct"
LINE 1: selct * from bub;
        ^
scratch=# select * from bub;
                bub_id                
--------------------------------------
 8653ae8c-b8cf-477e-9700-56cb8acba556
 4302cb8e-4c7a-4d8f-a83f-ffeaaa40f3bd
(2 rows)

scratch=# insert into subfoo (bub_id) VALUES ('8653ae8c-b8cf-477e-9700-56cb8acba556');
INSERT 0 1
scratch=# delete from bub where bub_id = '8653ae8c-b8cf-477e-9700-56cb8acba556';
DELETE 1
scratch=# select * from subfoo;
 foo_id | bub_id | message 
--------+--------+---------
(0 rows)