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

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. 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