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

On Fri, Jul 7, 2023 at 5:21 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> On Wed, Jul 5, 2023 at 3:08 PM Himanshu Upadhyaya
> <upadhyaya(dot)himanshu(at)gmail(dot)com> wrote:
> >
> > Hi,
> >
> > Currently, there is no support for CHECK constraint DEFERRABLE in a
> create table statement.
> > SQL standard specifies that CHECK constraint can be defined as
> DEFERRABLE.
>
> I think this is a valid argument that this is part of SQL standard so
> it would be good addition to PostgreSQL. So +1 for the feature.
>
> But I am wondering whether there are some real-world use cases for
> deferred CHECK/NOT NULL constraints? I mean like for foreign key
> constraints if there is a cyclic dependency between two tables then
> deferring the constraint is the simplest way to insert without error.
>
> --
> Regards,
> Dilip Kumar
> EnterpriseDB: http://www.enterprisedb.com
>

--
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com