Re: constraints and sql92 information_schema compliance

On Wed, 15 Mar 2006, Clark C. Evans wrote:

> On Tue, Mar 14, 2006 at 08:14:12PM -0800, Stephan Szabo wrote:
> | > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
> | > CREATE TABLE a (b text, c text);
> | > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
> ...
> | > I assert the problem here is that the FOREIGN KEY constraint
> | > construction should have *failed* since the *tuple* (z,y)
> | > does not infact match any unique key in table x.
> |
> | Looking at this more, I'm not sure that making it match the unique key
> | exactly helps information_schema.constraint_column_usage at least.
>
> My problem is that the column order can be provided in the reference
> clause in a way that does *not* match a canidate key: in the example
> above, there isn't a primary key nor a unique key index on (z,y).

I think that's only true if z1=z2 and y1=y2 could have a different answer
than y1=y2 and z1=z2. Otherwise, you're effectively definting both
uniquenesses in a single constraint.

> | I don't see how you can differentiate the foreign keys in the last two
> | without a position column, which doesn't seem to be in at least our
> | current view (although I haven't checked 2003 to see if they changed it).
> | Both of those should be valid, although the second is wierd.
>
> There isn't a problem with the examples you provided, although the
> resulting join isn't what the user intended. I think the ability

Actually, it's precisely what I intended. In the second case the labels
happen to represent the other value.

> to omit the <reference column list> is a bad idea; but alias, it
> is quite different from the problem I'm reporting.

The point is that because rows in a table don't have order (unless
information_schema has special rules) the two constraints above seem to
look the same to me in their representation in
information_schema.constraint_column_usage. If that's true then forcing
the referenced columns to match exactly doesn't actually fix the problem
with the representation in infomration schema. The same ambiguity exists.

We need to offer this for spec complience reasons, but I don't think it
actually fixes the problem you would have with information_schema.