Re: CHECK Constraint Deferrable

I can think of one scenario, as below

1) any department should have an employee
2)any employee should be assigned to a department
so, the employee table has a FK to the department table, and another check constraint should be added to the department table to ensure there should be one/more employees in this department. It's kind of a deadlock situation, each one depends on the other one. We cant insert a new department, coz there is no employee. Also, we can't insert new employee belongs to this new department, coz the department hasn't been and cant be added. So if we have a check constraint defined as deferrable we can solve this problem.

‘postgres[685143]=#’CREATE FUNCTION checkEmpPresent(did int) RETURNS int AS $$ SELECT count(*) from emp where emp.deptno = did $$ IMMUTABLE LANGUAGE SQL;
CREATE FUNCTION
‘postgres[685143]=#’alter table dept add constraint check_cons check (checkEmpPresent(deptno) > 0);
ALTER TABLE
‘postgres[685143]=#’\d dept;
                    Table "public.dept"
  Column  |     Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 deptno   | integer       |           | not null |
 deptname | character(20) |           |          |
Indexes:
    "dept_pkey" PRIMARY KEY, btree (deptno)
Check constraints:
    "check_cons" CHECK (checkemppresent(deptno) > 0)
Referenced by:
    TABLE "emp" CONSTRAINT "fk_cons" FOREIGN KEY (deptno) REFERENCES dept(deptno)

‘postgres[685143]=#’insert into dept values (1, 'finance');
ERROR:  23514: new row for relation "dept" violates check constraint "check_cons"
DETAIL:  Failing row contains (1, finance             ).
SCHEMA NAME:  public
TABLE NAME:  dept
CONSTRAINT NAME:  check_cons
LOCATION:  ExecConstraints, execMain.c:2069
‘postgres[685143]=#’\d emp;
                   Table "public.emp"
 Column |     Type      | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
 empno  | integer       |           |          |
 ename  | character(20) |           |          |
 deptno | integer       |           |          |
Foreign-key constraints:
    "fk_cons" FOREIGN KEY (deptno) REFERENCES dept(deptno)

‘postgres[685143]=#’insert into emp values (1001, 'test', 1);
ERROR:  23503: insert or update on table "emp" violates foreign key constraint "fk_cons"
DETAIL:  Key (deptno)=(1) is not present in table "dept".
SCHEMA NAME:  public
TABLE NAME:  emp
CONSTRAINT NAME:  fk_cons
LOCATION:  ri_ReportViolation, ri_triggers.c:2608

I have tried with v1 patch as below;

‘postgres[685143]=#’alter table dept drop constraint check_cons;
ALTER TABLE
‘postgres[685143]=#’alter table dept add constraint check_cons check (checkEmpPresent(deptno) > 0) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE

‘postgres[685143]=#’BEGIN;
BEGIN
‘postgres[685143]=#*’insert into dept values (1, 'finance');
INSERT 0 1
‘postgres[685143]=#*’insert into emp values (1001, 'test', 1);
INSERT 0 1
‘postgres[685143]=#*’commit;
COMMIT
‘postgres[685143]=#’select * from dept;
 deptno |       deptname      
--------+----------------------
      1 | finance            
(1 row)

‘postgres[685143]=#’select * from emp;
 empno |        ename         | deptno
-------+----------------------+--------
  1001 | test                 |      1
(1 row)

Thanks,

Himanshu