Re: constraints and sql92 information_schema compliance

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

| create table ta(a int, b int, primary key(a,b));
| create table tb(a int, b int, foreign key (a,b) references ta);
| create table tc(a int, b int, foreign key (b,a) references ta);

When <reference column list> is omitted, it implies that the primary
key of the referenced table is used; hence, these are equivalent to:

create table tb(a int, b int, foreign key (a,b) references ta (a,b));
create table tc(a int, b int, foreign key (b,a) references ta (a,b));

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

Very Best,

Clark