Re: constraints and sql92 information_schema compliance

On Sat, 2006-02-25 at 16:35 -0500, Clark C. Evans wrote:
> On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote:
> | > > * for foreign-key and check constraints, the default names
> | > > are $1, $2, etc.; it would be great if they were "upgraded"
> | > > to use the default names given by primary and unique key
> | > > constraints: table_uk_1stcol, table_pk
> | >
> | > Err... what version are you using? I get constraint names like tt_a_fkey
> | > from devel, and I thought at least 8.1 does the same.
>
> 7.4.8, so it's a bit old -- glad to hear this made it!
>
> | > > * when creating a foreign key constraint on two columns, say
> | > > from A (x, y) to B (x, y), if the unique index on B is (x,y)
> | > > you can make a foreign key from A->B using (y,x)
> | >
> | > I don't understand which particular case you're complaining about, but as
> | > far as I can see, we have to allow that case by the rest of the spec.
>
> To be clear, I'm talking about...
>
> 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);
>
> For this case, the information schema details:
>
> 1. the foreign key constraint as a reference to the
> primary key constraint and lists the tuple (b,c)
>
> 2. the primary key constraint lists the keys (y,z)

I'm afraid I don't follow what the issue is.

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
k=# \d x
Table "public.x"
Column | Type | Modifiers
--------+------+-----------
y | text | not null
z | text | not null
Indexes:
"x_pkey" PRIMARY KEY, btree (y, z)

k=# \d a
Table "public.a"
Column | Type | Modifiers
--------+------+-----------
b | text |
c | text |
Foreign-key constraints:
"a_b_fkey" FOREIGN KEY (b, c) REFERENCES x(z, y)

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".
k=# insert into a values ('bar', 'foo');
INSERT 0 1

--