Skip site navigation (1) Skip section navigation (2)

Re: constraints and sql92 information_schema compliance

From: Rod Taylor <pg(at)rbt(dot)ca>
To: "Clark C(dot) Evans" <cce(at)clarkevans(dot)com>
Cc: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, Bruno Wolff III <bruno(at)wolff(dot)to>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: constraints and sql92 information_schema compliance
Date: 2006-02-25 21:50:41
Message-ID: 1140904241.5092.212.camel@home (view raw or flat)
Thread:
Lists: pgsql-hackers
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


-- 


In response to

Responses

pgsql-hackers by date

Next:From: Stephan SzaboDate: 2006-02-25 21:51:37
Subject: Re: constraints and sql92 information_schema compliance
Previous:From: Alvaro HerreraDate: 2006-02-25 21:36:19
Subject: Re: Pl/Python -- current maintainer?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group