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


--