0

My app has the requirement that every table has a "created_by" column that references to the id column of user table. Even the user table. That means the created_by column would be a circular reference for the user table, so I made created_by default to 0 in this table, and first inserted user with id=0 will be created by himself.

But now I am facing another related issue with the creation of the schema, where referenced column are circular references. This is the simplified schema now:

SET CONSTRAINTS ALL DEFERRED; CREATE TABLE public.user( id int8 NOT NULL, store_id int8, created_by int8 NOT NULL DEFAULT 0, CONSTRAINT user_pk PRIMARY KEY (id), CONSTRAINT user_created_by FOREIGN KEY (created_by) REFERENCES public.user(id) ON DELETE SET DEFAULT, CONSTRAINT user_store_id FOREIGN KEY (store_id) REFERENCES public.store(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ) CREATE TABLE public.store( id int8 NOT NULL, created_by int8 NOT NULL, CONSTRAINT store_pk PRIMARY KEY (id), CONSTRAINT store_created_by FOREIGN KEY (created_by) REFERENCES public.user(id) ON DELETE CASCADE )

I get the error that "public.store" table does not exist in the user table creation statement.

This runs in a transaction, and I was under impression that you can defer constraint checks after the transaction ends by using the DEFERRED keywords, but for some reason it does not work. Why is the check is still made in the first create table query?

Improve this question

1 Answer 1

Reset to default
2

Deferred constraints only work for DML, not for DDL.

You must create the tables first, then create the constraints.

CREATE TABLE public."user"( id bigint NOT NULL, store_id bigint, created_by bigint, CONSTRAINT user_pk PRIMARY KEY (id), CONSTRAINT user_created_by FOREIGN KEY (created_by) REFERENCES public."user"(id) ON DELETE SET NULL ); CREATE TABLE public.store( id bigint NOT NULL, created_by bigint NOT NULL, CONSTRAINT store_pk PRIMARY KEY (id), CONSTRAINT store_created_by FOREIGN KEY (created_by) REFERENCES public."user"(id) ON DELETE CASCADE ); ALTER TABLE public."user" ADD CONSTRAINT user_store_id FOREIGN KEY (store_id) REFERENCES public.store(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;

I find the choice of a "magic user" with ID=0 questionable.

This forces you to have a a dummy row in the "user" table, just to be able to support the default 0. Using some kind of "dummy" user defeats the whole purpose of the requirement to reference the user that created the row. Where is the difference between a dummy using with the magic id 0 and NULL?

Improve this answer
2
  • but if it only works with alter table, I dont understand the point of deferred. Alter table you can always put later, so you dont need to use deferred Commented Sep 3, 2022 at 13:54
  • As I wrote: deferrable refers to the point when DML (update, insert, delete) are validated. It will not defer the check if a table exists or not. Commented Sep 3, 2022 at 14:36

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.