how to disable check constraint in postgresql

Question

I have below table with check constraint for salary column. I want to disable the check constraint temporarily. How to disable and enable the check constraints?

CREATE TABLE "Employee_Salary_Details"(
  empno int,
  ename varchar(100),
  sal numeric CONSTRAINT CK_SAL CHECK(sal>3500)
)

INSERT INTO "Employee_Salary_Details" VALUES(101,'RAM',200);

ALTER TABLE "Employee_Salary_Details" DISABLE  CONSTRAINT CK_SAL

I tried, but it is showing an error message. Is it possible to disable and enable heck constraints?

Tim Biegeleisen · Accepted Answer · 2019-10-16 06:45:25Z

5

Is it possible to disable and enable check constraints?

No, that's not possible. You need to drop and re-create it.

Use:

ALTER TABLE Employee_Salary_Details DROP CONSTRAINT CK_SAL;

Do your business, and then add it back:

ALTER TABLE Employee_Salary_Details ADD CONSTRAINT CK_SAL CHECK (sal > 3500);

edited Oct 16, 2019 at 6:45

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered Oct 16, 2019 at 6:43

user330315

Sign up to request clarification or add additional context in comments.

Comments

Xomo · Accepted Answer · 2019-10-16 07:11:20Z

2

There's another way around..

SELECT consrc,con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = 'public' and contype='c'

Try to find your constraint in this query result.. And then update 'conrelid' to 0 for that entry.

This will disable that constraint.

Hope this works..

And after your insertion work done.. Revert same value for 'conrelid'

answered Oct 16, 2019 at 7:11

Xomo

216 bronze badges

2 Comments

user330315 Over a year ago

Suggesting to manipulate the system tables directly is a really bad advice

Xomo Over a year ago

Yeah.. But this is one of the solution for this problem. I'm just giving another approach.

Collectives™ on Stack Overflow

how to disable check constraint in postgresql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related