Re: ENABLE/DISABLE CONSTRAINT NAME

From: wangshuo(at)highgo(dot)com(dot)cn
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: David Johnston <polobo(at)yahoo(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>, Jeff Davis <pgsql(at)j-davis(dot)com>
Subject: Re: ENABLE/DISABLE CONSTRAINT NAME
Date: 2013-09-13 02:03:44
Message-ID: 641404c1a796c784742201c72e1273e2@highgo.com.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 09/13/2013 05:23, Peter Eisentraut wrote:
> => create table test1 (a int constraint pk primary key, b text);
> => create view test2 as select a, b from test1 group by a;
> => alter table test1 drop constraint pk;
> ERROR: 2BP01: cannot drop constraint pk on table test1 because other
> objects depend on it
> DETAIL: view test2 depends on constraint pk on table test1
> HINT: Use DROP ... CASCADE to drop the dependent objects too.
>
> (This has to do with whether ungrouped columns are allowed in the
> select
> list when the presence of constraints ensures well-defined results.)
>
> When trying to drop the constraint, the choice is to abort the drop
> or
> to drop dependent objects. When you are talking about
> enabling/disabling the constraint, it's not clear what to do.

Thanks for your reply.
First, I had said that I I only made ​​a few modifications to the check
and the
foreign key constraint, and did nothing with primary key constraint.

On 08/30/2013 02:03 PM, I wrote:
>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;

In the alter_table.sgml, I wrote:
>This form enables or disables a foreign key or check constraint.

Second, I tested the check and the foreign key constraint as your test
above.
And no error found, as fellow:

postgres=# create table a1 (a1 int check(a1>4));
CREATE TABLE
postgres=# create view a11 as select * from a1;
CREATE VIEW
postgres=# alter table a1 disable constraint a1_a1_check;
ALTER TABLE
postgres=# insert into a1 values (3);
INSERT 0 1
postgres=# select * from a11;
a1
----
3
(1 row)

postgres=# alter table a1 drop constraint a1_a1_check;
ALTER TABLE

postgres=# create table bb(b1 int primary key);
CREATE TABLE
postgres=# create table cc(c1 int references bb(b1));
CREATE TABLE
postgres=# create view c11 as select * from cc;
CREATE VIEW
postgres=# alter table cc disable constraint cc_c1_fkey;
ALTER TABLE
postgres=# insert into cc values (1);
INSERT 0 1
postgres=# select * from c11;
c1
----
1
(1 row)

postgres=# alter table cc drop constraint cc_c1_fkey;
ALTER TABLE

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2013-09-13 02:50:42 [PATCH] pg_upgrade: Split off pg_fatal() from pg_log()
Previous Message Robert Haas 2013-09-13 01:42:31 background workers, round three