Re: Bug of ALTER TABLE DROP CONSTRAINT

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