
> > Seems with above you are not able to constrain what qualifies for a
> > supertable row, you would only be able to specify constraints that
> > apply to all it's subtables.
> 
> 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.

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

> 
> > The SQL inheritance is a class/subclass thing. All tables have
> > instances (==rows) that are not (by itself) related. (Even if
> > they happen to share all attribute values with another row of a
> > supertable.) If you want that, then you need to resort to 3NF (or
> > ROWREF's which iirc is another SQL99 feature).
> 
> 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.
If the supertable has a PK, then of course an exact match of columns is not 
possible in supertable/subtable rows.
 
> Am I to take it that you think the inheritance should be inheritance
> of type information only? That is, if I have supertable A and
> subtable A', inserting a row into A' does not make a row appear in
> A? If so, I've got not real problem with that at present, but it's
> not what postgres currently does, nor would it conform to SQL99.

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 *not* want this as default behavior when creating a
constraint though.

Andreas

Mon, 19 Aug 2002 11:34:53 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Inheritance 
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 all light.  --XTC


Mon, 19 Aug 2002 22:56:04 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance 