Обсуждение: bug in 8.3? foreign key refers to different type
These create table commands succeed, even tho the foreign key refers to a 'different' type (int2 product_id column refersto an int8 column): CREATE TABLE products ( id int8 primary key, name text not null ); CREATE TABLE orders ( id int8 PRIMARY KEY, product_id int2 not null REFERENCES products (id) ); If I replace int2 with numeric, real, text, etc it fails as expected ("key columns are of incompatible types"). Expected: integer types should match exactly (or at least be large enough to hold the referenced value). thanks, --craig ____________________________________________________________________________________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
On Tue, 26 Feb 2008, craigp wrote: > These create table commands succeed, even tho the foreign key refers to > a 'different' type (int2 product_id column refers to an int8 column): The requirements in recent SQL specs appears to be that the column types are comparable, not the same. SQL2003 11.8 <referential constraint definition> "The declared type of each referencing column shall be comparable to the declared type of the corresponding referenced column." As far as I can tell the spec requires two numbers to be comparable, which would make a failure for numeric or real an actual bug, but I don't have an 8.3 system available at the moment to confirm against.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > On Tue, 26 Feb 2008, craigp wrote: >> These create table commands succeed, even tho the foreign key refers to >> a 'different' type (int2 product_id column refers to an int8 column): > The requirements in recent SQL specs appears to be that the column types > are comparable, not the same. The 8.3 code requires the two column datatypes to participate in the same btree operator family, which is a fairly strong form of "comparable". Offhand I think the available numeric-type families are int2/int4/int8, float4/float8, and numeric. > As far as I can tell the spec requires two numbers to be comparable, which > would make a failure for numeric or real an actual bug, but I don't have > an 8.3 system available at the moment to confirm against. If anyone actually complains, we could possibly extend the set of opclasses to cover more cases. I'm not real clear on the sanity of linking (for instance) a float8 PK to a numeric FK, though. I think you'd constantly be getting bit by precision issues. regards, tom lane