Thread: Bug of ALTER TABLE DROP CONSTRAINT

Considering the following sequence:   create table t(a int primary key);   alter table t drop constraint t_pkey;
insertinto t values(null);   ERROR:  null value in column "a" violates not-null constraint
 

My question is, why "null" is not allowed to be inserted after primary key 
constraint has been dropped.

On Thu, Apr 2, 2009 at 3:25 AM, Jacky Leng <lengjianquan@163.com> wrote:
> Considering the following sequence:
>    create table t(a int primary key);
>    alter table t drop constraint t_pkey;
>    insert into t values(null);
>    ERROR:  null value in column "a" violates not-null constraint
>
> My question is, why "null" is not allowed to be inserted after primary key
> constraint has been dropped.

Making a column into the primary key forces the column to NOT NULL.
You'll need to DROP NOT NULL separately.

It's probably possible to beat on the code hard enough to fix this,
but I'm not really sure there's much point, since the situation is
rare and the workaround is easy.

...Robert

On Thu, Apr 2, 2009 at 8:24 AM, Nikhil Sontakke
<nikhil.sontakke@enterprisedb.com> wrote:
> Hi,
>>
>> > Considering the following sequence:
>> >    create table t(a int primary key);
>> >    alter table t drop constraint t_pkey;
>> >    insert into t values(null);
>> >    ERROR:  null value in column "a" violates not-null constraint
>> >
>> > My question is, why "null" is not allowed to be inserted after primary
>> > key
>> > constraint has been dropped.
>>
>> Making a column into the primary key forces the column to NOT NULL.
>> You'll need to DROP NOT NULL separately.
>>
>> It's probably possible to beat on the code hard enough to fix this,
>
> Yeah it will be a matter of finding the affected column entries and invoking
> the removal of the not null entry from their corresponding pg_attribute
> rows.

Actually it's more complicated than that.  You'd need to remember
whether or not the NOT NULL was added when the primary key was added,
or whether it was there before, and only drop it if it wasn't there
before.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:
> Actually it's more complicated than that.  You'd need to remember
> whether or not the NOT NULL was added when the primary key was added,
> or whether it was there before, and only drop it if it wasn't there
> before.

We've discussed before the idea that NOT NULL constraints should be
explicitly represented in pg_constraint, just like general CHECK
constraints (this would allow them to be named, have sane inheritance
behavior, etc).  If we had that, then pg_attribute.attnotnull could
indicate the OR of "there is a NOT NULL on this column" and "there is
a pkey constraint on this column", and you'd just have to recompute it
properly after dropping either kind of constraint.

Not happening for 8.4, but maybe someday someone will get around to it.
        regards, tom lane

Nikhil Sontakke wrote:
> Hi,
> 
> 
> > We've discussed before the idea that NOT NULL constraints should be
> > explicitly represented in pg_constraint, just like general CHECK
> > constraints (this would allow them to be named, have sane inheritance
> > behavior, etc).  If we had that, then pg_attribute.attnotnull could
> > indicate the OR of "there is a NOT NULL on this column" and "there is
> > a pkey constraint on this column", and you'd just have to recompute it
> > properly after dropping either kind of constraint.
> >
> > Not happening for 8.4, but maybe someday someone will get around to it.
> >
> 
> Warrants an entry in the TODO items list:
> 
> * make NOT NULL constraints have pg_constraint entries, just like CHECK
> constraints

This is now a TODO item (I just updated the description):
Store the constraint names of NOT NULL constraints    Currently NOT NULL constraints are stored in pg_attribute
withoutanydesignation of their origins, e.g. primary keys. One manifestproblem is that dropping a PRIMARY KEY
constraintdoes not remove theNOT NULL constraint designation.        *
http://archives.postgresql.org/message-id/19768.1238680878@sss.pgh.pa.us
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:
> Nikhil Sontakke wrote:
>> Warrants an entry in the TODO items list:
>> 
>> * make NOT NULL constraints have pg_constraint entries, just like CHECK
>> constraints

> This is now a TODO item (I just updated the description):

>     Store the constraint names of NOT NULL constraints

I was intending to do that yesterday, but lost interest after
discovering how many duplicate, obsolete, and/or mutually contradictory
TODO entries there are related to constraints.  That needs to be cleaned
up and consolidated sometime.

Also, what is wrong with the precise statement of the TODO item that
Nikhil gave?  The one you gave would encourage someone to waste time on
a 100% wrong implementation (like adding a constraint name column to
pg_attribute).
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Nikhil Sontakke wrote:
> >> Warrants an entry in the TODO items list:
> >> 
> >> * make NOT NULL constraints have pg_constraint entries, just like CHECK
> >> constraints
> 
> > This is now a TODO item (I just updated the description):
> 
> >     Store the constraint names of NOT NULL constraints
> 
> I was intending to do that yesterday, but lost interest after
> discovering how many duplicate, obsolete, and/or mutually contradictory
> TODO entries there are related to constraints.  That needs to be cleaned
> up and consolidated sometime.
> 
> Also, what is wrong with the precise statement of the TODO item that
> Nikhil gave?  The one you gave would encourage someone to waste time on
> a 100% wrong implementation (like adding a constraint name column to
> pg_attribute).

I felt the original wording would be unclear in explaining the problem
behavior;  you are right the original wording was clearer for correcting
the problem.  This updated wording works for both audiences, I think:
Move NOT NULL constraint information to pg_constraint    Currently NOT NULL constraints are stored in pg_attribute
withoutanydesignation of their origins, e.g. primary keys. One manifestproblem is that dropping a PRIMARY KEY
constraintdoes not remove theNOT NULL constraint designation.
*http://archives.postgresql.org/message-id/19768.1238680878@sss.pgh.pa.us
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +