Re: CHECK Constraint Deferrable
От | Himanshu Upadhyaya |
---|---|
Тема | Re: CHECK Constraint Deferrable |
Дата | |
Msg-id | CAPF61jCRXxCo9wX=RW2mqc+bnB=DPijU1ruGDki55TYYiSu7yw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: CHECK Constraint Deferrable (Dilip Kumar <dilipbalaut@gmail.com>) |
Ответы |
Re: CHECK Constraint Deferrable
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | pgsql-hackers |
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.
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
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
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)
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@gmail.com> wrote:
On Wed, Jul 5, 2023 at 3:08 PM Himanshu Upadhyaya
<upadhyaya.himanshu@gmail.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
--
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Stephen FrostДата:
Сообщение: Re: pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption?