Thread: 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