Skip site navigation (1)
Skip section navigation (2)
## Re: constraints and sql92 information_schema compliance

### In response to

### Responses

### pgsql-hackers by date

On Sat, 25 Feb 2006, 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) > > In particular, the column ordering (z, y) in the reference > clause is *lost*. Hence, if you were to blindly reconstruct > a join critiera from the information schema, you'd wrongly > assume that useful join critiera is: > > ON (a.b == x.y AND a.c == x.z) > > when the correct join critiera should be: > > ON (a.b == x.z AND a.c == x.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. > | > If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) > | > and B(y), all of A(x,y)->B(x,y), A(y,x)->B(x,y), A(x,y)->B(y,x) and > | > A(y,x)->B(y,x) seem to be allowed by the definition in the constraint > | > section (as only the sets must be equal, with no mention of ordering). > > Ordering of tuples (fields within a row object) are significant > in SQL; and hence the two above are not comparable. You misunderstand what comparable means in the above. Comparable is the constraint on the column types (for example numeric types are comparable to other numeric types).

- Re: constraints and sql92 information_schema compliance at 2006-02-25 21:35:38 from Clark C. Evans

- Re: constraints and sql92 information_schema compliance at 2006-02-25 22:01:00 from Stephan Szabo

Next: From:Stephan SzaboDate:2006-02-25 22:01:00Subject: Re: constraints and sql92 information_schema compliancePrevious: From: Rod TaylorDate: 2006-02-25 21:50:41Subject: Re: constraints and sql92 information_schema compliance