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

Hi,

So, I have a patch for this. This patch introduces support for

CHECK ONLY syntax while doing a CREATE TABLE as well as during the usual
ALTER TABLE command.

Example:

create table atacc7 (test int, test2 int CHECK ONLY (test>0), CHECK
(test2>10));
create table atacc8 () inherits (atacc7);

postgres=# \d+ atacc7
Table "public.atacc7"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
test | integer | | plain |
test2 | integer | | plain |
Check constraints:
"atacc7_test2_check" CHECK (test2 > 10)
"atacc7_test_check" CHECK ONLY (test > 0)
Child tables: atacc8
Has OIDs: no

postgres=# \d+ atacc8
Table "public.atacc8"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
test | integer | | plain |
test2 | integer | | plain |
Check constraints:
"atacc7_test2_check" CHECK (test2 > 10)
Inherits: atacc7
Has OIDs: no

This patch removes the support for :

ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0);

and uses

ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK ONLY (b > 0);

Is this what we want? Or we would want the earlier support in place for
backward compatibility as well? We are actually introducing this in 9.2 so
I guess we can remove this.

This is a much cleaner implementation and we might not even need the
changes in pg_dump now because the pg_get_constraintdef can provide the
info about the ONLY part too. So some cleanup can be done if needed.

I know it's a bit late in the commitfest, but if this patch makes this
feature more "complete", maybe we should consider...

Thoughts?

P.S Here's the discussion thread in its entirety for reference:
http://postgresql.1045698.n5.nabble.com/how-to-create-a-non-inherited-CHECK-constraint-in-CREATE-TABLE-td5152184.html

Regards,
Nikhils

On Thu, Feb 2, 2012 at 1:32 AM, Peter Eisentraut
<peter_e(at)gmx(dot)net> wrote:

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