Re: constraints and sql92 information_schema compliance

On Sat, Feb 25, 2006 at 04:50:41PM -0500, Rod Taylor wrote:
| Can out point it out in the below psql output?
|
| k=# CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
| for table "x"
| CREATE TABLE
| k=# CREATE TABLE a (b text, c text);
| CREATE TABLE
| k=# ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
| ALTER TABLE

-> this alter _should_ fail, since there isn't a canidate key
on the table x matching (z, y)

| "x_pkey" PRIMARY KEY, btree (y, z)
| "a_b_fkey" FOREIGN KEY (b, c) REFERENCES x(z, y)

The problem is that PostgreSQL is maintaining information that is/should
not be available to an SQL processor: the ordering of the _referenced_
columns. That a_b_fkey happens to reference (z, y) is not available in
the SQL INFORMATION_SCHEMA, and thus should not be used to interpret
standard SQL statements affected by the foreign key constraint.

| k=# insert into x values ('foo', 'bar');
| INSERT 0 1
| k=# insert into a values ('foo', 'bar');
| ERROR: insert or update on table "a" violates foreign key constraint
| "a_b_fkey"
| DETAIL: Key (b,c)=(foo,bar) is not present in table "x".

Assuming that you _could_ create the FOREIGN KEY reference above, if you
are strictly using the meta-data available in the information_schema,
this insert should succeed

| k=# insert into a values ('bar', 'foo');
| INSERT 0 1

and this insert should fail. The opposite happens beacuse PostgreSQL
is storing _more_ information than what is specified and has over
interpreted the meaning of the reference clause.

On Sat, Feb 25, 2006 at 02:01:00PM -0800, Stephan Szabo wrote:
| > On Sat, 25 Feb 2006, Clark C. Evans 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.
| >
| > I disagree because the spec doesn't say that the columns must be equal
| > or the list of columns must be the equal but that the set of columns must
| > be equal. And in the definitions section, set is defined as an unordered
| > collection of distinct objects.

Let's use the example Rod gave us above. If the comparison for foreign
key constraints should be done as an unorderd set, then why does the
following fail?

| k=# insert into x values ('foo', 'bar');
| INSERT 0 1
| k=# insert into a values ('foo', 'bar');
| ERROR: insert or update on table "a" violates foreign key constraint
| "a_b_fkey"
| DETAIL: Key (b,c)=(foo,bar) is not present in table "x".

While the SQL1992 specification may be horribly incorrect; the current
behavior is not compliant with it... so this isn't a great defense. If
PostgreSQL didn't store the order of the columns referenced, it couldn't
provide the error above (which makes sense, given the extension).

| Okay, I'll take that back for SQL2003. They must have realized that this
| was broken with information schema and changed it.

Ok.

| That's an interesting incompatibility with old versions,
| but it's easy to implement.

This would be great; it would reduce the chances of an external program
generating SQL from making incorrect joins and causing a very strange
behavior and incorrect results.

Cheers,

Clark