Re: [GENERAL] Inherited constraints and search paths

On Fri, 2005-05-20 at 11:51 -0400, Tom Lane wrote:
> Simon Riggs  writes:
> > Doing anything to restrict dropping of inherited constraints seems like
> > wasted effort and potentially annoying anyhow.
> 
> Uh, why?  Arguably the constraints are as much part of the parent table
> definition as the columns themselves.  If you had "check (f1 > 0)" in
> the definition of a table, wouldn't you be pretty surprised to select
> from it and find rows with f1 < 0?
> 
> regression=# create table parent(f1 int check (f1 > 0));
> CREATE TABLE
> regression=# create table child() inherits(parent);
> CREATE TABLE
> regression=# alter table child drop constraint parent_f1_check;
> ALTER TABLE
> regression=# insert into child values(-1);
> INSERT 0 1
> regression=# select * from parent;
>  f1
> ----
>  -1
> (1 row)
> 
> I think a good argument can be made that the above behavior is a bug,
> and that the ALTER command should have been rejected.  We've gone to
> great lengths to make sure you can't ALTER a child table to make it
> incompatible with the parent in terms of the column names and types;
> shouldn't this be true of check constraints as well?

Thats a good case. I retract my comment on potentially annoying.

If you were going to fix that by adding a column that allows me to tell
the difference between inherited and non-inherited relations, that would
be a very useful piece of info for partition elimination. In that case I
would also retract my comment on wasted effort. :-)

If you're looking for other inheritance wierdies, you may also be
interested in this one. When you create a table that inherits from a
master, it copies across constraints with exactly matching names. If a
constraint is then added to the master, the constraint is copied across
to the child but does not have the same name. So the name of inherited
constraints differs depending upon whether CREATE or ALTER puts them
there.

FWIW, fixing either of those won't get in my way on partitioning...

Best Regards, Simon Riggs