Re: Inheritance - Mailing list pgsql-hackers

On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote:

> > Yes, that's the whole point. If I have a constraint on a table, I think
> > it should *never* be possible for that constraint to be violated. If a
> > subtable should not have constraint the supertable has, it shouldn't
> > inherit from the supertable.
>
> If you want that, you simply need to only create constraints that apply to
> all tables in the hierarchy. Note that you *can* do this. It should imho be
> the default behavior.

So what you're saying is that constraints shouldn't be inherited?

> > To do otherwise breaks the relational model.
>
> That is probably a point of argument. Imho the inheritance feature
> is something orthogonal to the relational model. It is something else, and
> thus cannot break the relational model.

So then constraints must be inherited. The relational model, if I
am not incorrect here, says that, given a table definition such as
this:
   CREATE TABLE my_table (my_key int PRIMARY KEY,my_value text UNIQUE,my_other_value int CHECK (my_other_value > 0)
)

You will never, ever, when selecting from this table, have returned to you
   1. two rows with the same value of my_key but different values   for the other columns,
   2. two rows with the same value of my_value but different values   for the other columns, or
   3. a row in which the value of my_other_value is not greater than zero.

Breaking these sorts of guarantees under any circumstances really
doesn't do it for me; what's the point of having guarantees if they
aren't guarantees?

> > As I understand it, SQL99 has the restriction that a row with the same
> > primary key appearing in a supertable and/or any of its subtables must
> > be the result of a single INSERT statement. Thus, SQL99 doesn't allow
> > what you're saying, if I understand what you're saying. (I'm not sure
> > that I do.)
>
> I was not talking about primary key, not all tables have a primary key.

Well, for those that do....

Also, I should amend that; I suspect (though I could well be wrong,
knowing how screwed up SQL is at times) that this really applies
to all candidate keys in the table.

(And this is one of my complaints about SQL; it's possible for a table to
exist without candidate keys. So much for set theory!)

> No, not at all. All I am saying is that I want to be able to create a
> constraint that only applies to the supertable rows, and not the
> subtable rows.

I would strongly object to that. It should not be possible to SELECT
data from a table that violates the constraints that that table is
guaranteeing on the data.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org   Don't you know, in this new Dark Age, we're
alllight.  --XTC