On 27 June 2011 03:31, Robert Haas <robertmh...@gmail.com> wrote: > On Sat, Jun 25, 2011 at 2:15 AM, Dean Rasheed <dean.a.rash...@gmail.com> > wrote: >> Really? I would expect the reverse, namely that the not-nullness is >> part of the PK constraint and dropping the PK *would* then start >> allowing NULLs. > > Hmm, OK. I had assumed we were only trying to fix the problem that > parent and child inheritance tables could get out of step, but maybe > you're right. > > If we go with that approach, then consider: > > CREATE TABLE foo (a int); > CREATE TABLE bar () INHERITS (foo);> > Now if someone adds a primary key foo (a), what happens currently is > that foo.a becomes NOT NULL, but bar.a still allows NULLs. Should > that remain true (on the theory that a primary key constraint is not > inherited) or become false (on the theory that parent and child tables > should match)? >
I'm not sure, but my real problem with the current behaviour is its inconsistency. Consider this case: CREATE TABLE foo (a int PRIMARY KEY); CREATE TABLE bar () INHERITS (foo); Currently this results in bar not allowing NULLs, which is inconsistent with adding the PK after defining the inheritance. Then if the PK is dropped, the non-nullness is left behind on both foo and bar. I would summarise the consistency requirements as: 1). ADD CONSTRAINT should leave both parent and child tables in the same state as they would have been if the constraint had been defined at table creation time. 2). DROP CONSTRAINT should leave both parent and child tables in the same state as if the constraint had never existed (completely reversing the effects of ADD CONSTRAINT). I don't have a strong opinion as to whether or not the NOT NULL part of a PK should be inherited, provided that it is consistent with the above. I guess that if I were forced to choose, I would say that the NOT NULL part of a PK should not be inherited, since I do think of it as part of the PK, and PKs are not inherited. But I wouldn't be too upset if it were inherited (consistently!) and I can't think of a use case where that would be a problem. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers