Thread: ENABLE/DISABLE CONSTRAINT NAME

Hi hackers,

       In order to achieve enable/disable constraint name，I made ​​a few
modifications to the code.

       First, someone used to build the constraints while building
table. Then inserting data must follow a certain order.
       And people usually like to insert the data but not affected by
foreign keys or check.

       Second, the check or the foreign key constraint will waste much
time while inserting the data into the table.

       Due to the above reasons，I realized this command.

       I add a field named 'conenabled' to pg_constraint, identifying
whether a constraint is enable or not;
       I enable or disable a foreign key constraint, by enable or
disable the triggers of the foreign key;
       Our database will depend on the value of 'conenabled' to use the
check constrint or not;

       I think the internal trigger's naming can be changed,and the
function ATExecValidateConstraint can be changed too,
       but I think that together we can discuss, to decide what to do.

Now,we can do those:

Syntax:
      alter table disable constraint <constraint_name> ;
      alter table enable constraint <constraint_name> [ no valid ];
      alter table add constraint table_constriant [ disable ];

       CREATE TABLE aa
(
a1 INT CHECK(a1>4),
a2 INT
);
ALTER TABLE aa ADD CONSTRAINT aa_a2_check CHECK(a2>10) DISABLE;
INSERT INTO aa VALUES (10,1);
DELETE FROM aa;
ALTER TABLE aa DROP CONSTRAINT aa_a2_check;

ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
INSERT INTO aa VALUES (5,2);
UPDATE aa SET a1=2 WHERE a2=2;
INSERT INTO aa VALUES (1,1);
ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check NOT VALID; //don't
validate the data
DELETE FROM aa;
ALTER TABLE aa DISABLE CONSTRAINT aa_a1_check;
ALTER TABLE aa ENABLE CONSTRAINT aa_a1_check;   //validate the data

CREATE TABLE bb
(
b1 INT PRIMARY KEY,
b2 INT
);
CREATE TABLE cc
(
c1 INT REFERENCES bb(b1),
c2 INT
);

ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
INSERT INTO cc VALUES (1,1);
INSERT INTO bb VALUES (2,2);
INSERT INTO cc VALUES (2,2);
UPDATE cc SET c1=1 WHERE c2=2;
ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey NOT VALID;   //don't
validate the data
ALTER TABLE cc DISABLE CONSTRAINT cc_c1_fkey;
DELETE FROM cc;
ALTER TABLE cc ENABLE CONSTRAINT cc_c1_fkey;  //validate the data



I packed a patch about this modification.This is my first time to send
the patch,
I hope you give me some advice.

      Best Regards!

      Yours,
      Wang Shuo
      HighGo Software Co.,Ltd.
      August 30, 2013

wangshuo@highgo.com.cn writes:
>        In order to achieve enable/disable constraint name，I made ​​a few 
> modifications to the code.
>        First, someone used to build the constraints while building 
> table. Then inserting data must follow a certain order.
>        And people usually like to insert the data but not affected by 
> foreign keys or check.
>        Second, the check or the foreign key constraint will waste much 
> time while inserting the data into the table.
>        Due to the above reasons，I realized this command.

Uh ... why not just drop the constraint, and re-add it later if you want
it again?  This seems like adding a lot of mechanism (and possible bugs)
for a rather marginal use-case.
        regards, tom lane

> Uh ... why not just drop the constraint, and re-add it later if you want
> it again?

My 0.02€ : maybe because you must keep track of the constraint details to 
do so, this it is significantly more error prone than disable / enable 
when the bookkeeping is done by the system and if everything is in a 
transaction... If the ENABLE is automatically done on the next COMMIT, 
that would be even better.

> This seems like adding a lot of mechanism (and possible bugs) for a 
> rather marginal use-case.

That is possible!

-- 
Fabien.

于 2013-08-30 21:27, Tom Lane 回复:
> wangshuo@highgo.com.cn writes:
>>        In order to achieve enable/disable constraint name，I made ​​a 
>> few
>> modifications to the code.
>>        First, someone used to build the constraints while building
>> table. Then inserting data must follow a certain order.
>>        And people usually like to insert the data but not affected 
>> by
>> foreign keys or check.
>>        Second, the check or the foreign key constraint will waste 
>> much
>> time while inserting the data into the table.
>>        Due to the above reasons，I realized this command.

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Uh ... why not just drop the constraint, and re-add it later if you 
> want
> it again?

Thanks for your reply.

If you drop the constraint,you must record the sql of the constraint.
ENABLE/DISABLE just turn off or trun on that.The sql2008 support this.

And, Oracle,DB2,SQL Server,MySQL all support this feature, new users 
ever used Oracle are accustomed to
use, besides, this feature benefits data migration, so we have enough 
reasons to
add this feature.

> This seems like adding a lot of mechanism (and possible bugs)
> for a rather marginal use-case.
>
>             regards, tom lane


I changed the pg_constraint system table , the  ConstrCheck struct, the
CreateTrigger function, the  CreateConstraintEntry function and some 
grammars.
I have passed the pgtest,and this may has some bugs.

I refer to the validation feature to do this feature.
The validation feature only works while adding constraint,
my work is a supplement to the validation feature.
If possible, I would like to merge the two features together.

For all above, I wrote this letter to community, to let more people to 
talk about
this and correct possible bugs.


     Wang Shuo     HighGo Software Co.,Ltd.     September 1, 2013