I was recently debugging some database infrastructure and I ran across an issue with postgres "merging" inherited constraints with pre-existing constraints. Please see the following minimal example (run on postgres 9.5):
begin;
create table my_table (my_col int);
create table my_table_child () inherits (my_table);
-- Experiment with constraint on child table.
alter table my_table_child add constraint my_col_constraint check (my_col >= 0);
-- Some time later, add it to the parent table.
alter table my_table add constraint my_col_constraint check (my_col >= 0);
-- Postgres gives a warning, not an error, and says it will merge the constraints.
-- By all appearances, my_table_child now has a single inherited constraint.
-- For example, attempting to drop the constraint on my_table_child will fail because it's inherited.
-- Now we want to update the constraint.
alter table my_table drop constraint my_col_constraint;
alter table my_table add constraint my_col_constraint check (my_col >= 1337);
-- Error! After we dropped the constraint on the parent table, the non-inherited constraint
-- "re-appeared" on the child table and we cannot add this new constraint.
I would expect one of the following behaviors: A) it should be obvious that there is more than one constraint on the child table (show both inherited and non-inherited constraint), B) postgres should fail rather than "merging" the constraints, or C) "merging" the constraints should delete the non-inherited constraint on the child table. Right now, "merging" doesn't seem to actually do anything. Perhaps this is unintended behavior?
It's also entirely possible I'm going about this completely wrong. I appreciate any input.
