Bug of ALTER TABLE DROP CONSTRAINT
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.
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 constraintMy 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
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 constraintMy 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.
but I'm not really sure there's much point, since the situation is
rare and the workaround is easy.
Yeah and it is documented already. Although it is not obvious immediately
that a not-null attribute gets tagged onto the involved columns separately
for primary, unique-not-null types of constraints.
Regards,
Nikhils
--
http://www.enterprisedb.com
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 constraintMy 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
Hi,
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.
Hmm, and maybe that is the reason why this is not clubbed with the removal
of the primary constraint. Otherwise it seems to be a matter of decompiling
the conkey and generating AT_DropNotNull nodes for the involved columns
within ATPrepCmd and the rest should happen automatically. So I guess we can
let it be.
Regards,
Nikhils
--
http://www.enterprisedb.com
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
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
Regards,
Nikhils
--
http://www.enterprisedb.com
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 without
any designation of their origins, e.g. primary keys. One manifest
problem is that dropping a PRIMARY KEY constraint does not remove the
NOT 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
constraintsThis 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 without
any designation of their origins, e.g. primary keys. One manifest
problem is that dropping a PRIMARY KEY constraint does not remove the
NOT 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. +