Re: Inheritance - Mailing list pgsql-hackers

On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > Suppose you have a table CITIZEN with table-level constraint IS_GOOD
> > which is defined as kills_not_others(CITIZEN). and there is table
> > CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
> > (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD....
> 
> This I very much disagree with.
> 
> In most object-oriented languages (Eiffel being a notable exception, IIRC),
> you can't specify constraints on objects. But in a relational database,
> you can specify constraints on tables, and it should *never* *ever* be
> possible to violate those constraints, or the constraints are pointless.

That's not how real world (which data is supposed to model) operates ;)

As Greg already pointed out, there are two kinds of constraints -
database integrity constraints (foreign key, unique, not null, check),
which should never be overridden and business-rule constraints which
should be overridable in child tables.

one can argue that the latter are not constraints at all, but they sure
look like constraints to me ;)

To elaborate on Gregs example if you have table GOODS and under it a
table CAMPAIGN_GOODS then you may place a general overridable constraint
valid_prices on GOODS which checks that you dont sell cheaper than you
bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
you override the constraint for CAMPAIGN_GOODS.

> So if I have a constraint that says, "no rows appearing in this
> table will ever violate constraint X," and then you go and create
> a way of inserting rows into that table that violate that constraint,
> I think you've just made the database into a non-relational database.

SQL standard constraints should be non-overridable. I still think that
Constraint triggers should be overridable/dynamic. 

Or maybe it is better to just make the check function should be
dynamically dispatched, so the constraint will always hold, it just can
mean different things for different types.

> I really don't want to break postgres' relational side for some
> inheritance features of dubious utility. Constraints should be explicitly
> removed from tables if they are no longer needed, not implicitly removed
> through the creation of another table.
> 
> I think we should settle this point before going any further.

It seems that the dynamic dispatch of trigger function should be enough
for business-rule constraints. 

And it is also simpler and cleaner (both conceptually and to implement)
if constraints themselves are not overridable.

So in my CAMPAIGN_GOODS example you just have different
valid_prices(GOODS) and valid_prices(CAMPAIGN_GOODS), but one constraint
on GOODS which states that price must be valid . 

Doing it this way ensures that you are not able to have a record in
GOODS for which valid_price(ROW) does not hold.

If you don't want inherited tables to be able to override valid_price()
use it in CHECK constraint in GOODS, which should use the
valid_prices(cast(ROW as GOODS)) for any inherited type.

-----------------
Hannu