Re: how to create a non-inherited CHECK constraint in CREATE TABLE

On ons, 2012-01-18 at 18:17 -0500, Robert Haas wrote:
> I agree with Peter that we should have we should have CHECK ONLY.
> ONLY is really a property of the constraint, not the ALTER TABLE
> command -- if it were otherwise, we wouldn't need to store it the
> system catalogs, but of course we do.  The fact that it's not a
> standard property isn't a reason not to have proper syntax for it.

Clearly, we will eventually want to support inherited and non-inherited
constraints of all types.  Currently, each type of constraint has an
implicit default regarding this property:

check - inherited
not null - inherited
foreign key - not inherited
primary key - not inherited
unique - not inherited
exclusion - not inherited

As discussed above, we need to have a syntax that is attached to the
constraint, not the table operation that creates the constraint, so that
we can also create these in CREATE TABLE.

How should we resolve these different defaults?

Also, in ALTER TABLE, if you want to add either an inherited or not
inherited constraint to a parent table, you should really say ALTER
TABLE ONLY in either case.  Because it's conceivably valid that ALTER
TABLE foo ADD CHECK () NOINHERIT would add an independent, not inherited
check constraint to each child table.

So, there are all kinds of inconsistencies and backward compatibility
problems lurking here.  We might need either a grand transition plan or
document the heck out of these inconsistencies.