Re: Re: starting to review the Extend NOT NULL representation to pg_constraint patch

On Mon, Jun 27, 2011 at 3:08 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 27 June 2011 03:31, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sat, Jun 25, 2011 at 2:15 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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.

OK, I see your point, and I agree with you.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company