Foreign key constraint accepted even when not same data type

[Jean-Christian Imbeault]

Is it right for postgres to accept a foreign key constraint when the
type of the field is not the same as that of the foreign key?

For example:

# Create table a (id int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE TABLE
# Create table b (id2 text references a(id));

NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE

# \d a
Table "public.a"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Indexes: a_pkey primary key btree (id)

# \d b
Table "public.b"
Column | Type | Modifiers
--------+------+-----------
id2 | text |
Foreign Key constraints: $1 FOREIGN KEY (id2) REFERENCES a(id) ON UPDATE
NO ACTION ON DELETE NO ACTION
Jean-Christian Imbeault


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

On Mon, 22 Sep 2003, Jean-Christian Imbeault wrote:

Is it right for postgres to accept a foreign key constraint when the
type of the field is not the same as that of the foreign key?

IIRC in SQL92 it's said that they need to be the same type, but in SQL99
it says that the two types must be comparable. We basically implement the
latter, basically using the existance of a usable equality operator as the
determination of comparable.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Stephan Szabo <ss****@megazone.bigpanda.com> writes:

On Mon, 22 Sep 2003, Jean-Christian Imbeault wrote:

Is it right for postgres to accept a foreign key constraint when the
type of the field is not the same as that of the foreign key?

IIRC in SQL92 it's said that they need to be the same type, but in SQL99
it says that the two types must be comparable. We basically implement the
latter, basically using the existance of a usable equality operator as the
determination of comparable.

Note however that performance may be poor with a cross-type foreign key
reference, if the planner is unable to figure out how to use an index
for the check queries.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Dennis Bjorklund]

On Mon, 22 Sep 2003, Stephan Szabo wrote:

it says that the two types must be comparable. We basically implement the
latter, basically using the existance of a usable equality operator as the
determination of comparable.

Is it possible to drop the equality operator when one have FK that needs
it?

--
/Dennis
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Stephan Szabo]

On Tue, 23 Sep 2003, Dennis Bjorklund wrote:

On Mon, 22 Sep 2003, Stephan Szabo wrote:

it says that the two types must be comparable. We basically implement the
latter, basically using the existance of a usable equality operator as the
determination of comparable.

Is it possible to drop the equality operator when one have FK that needs
it?

Actually, right now, I think it is (as are necessary casts). That's
probably not good, but since the actual constraint isn't that you can't
drop the equality operator, but that the types must still be comparable
after doing so, I'm not sure how one would represent that right now (for
example, given an int->foo equality operator and foreign key, if there was
say a numeric->foo equality operator, dropping the int one is probably
okay assuming an implicit int->numeric cast).
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bruce Momjian]

Tom Lane wrote:

Stephan Szabo <ss****@megazone.bigpanda.com> writes:

On Mon, 22 Sep 2003, Jean-Christian Imbeault wrote:

Is it right for postgres to accept a foreign key constraint when the
type of the field is not the same as that of the foreign key?

IIRC in SQL92 it's said that they need to be the same type, but in SQL99
it says that the two types must be comparable. We basically implement the
latter, basically using the existance of a usable equality operator as the
determination of comparable.

Note however that performance may be poor with a cross-type foreign key
reference, if the planner is unable to figure out how to use an index
for the check queries.

Didn't we agree to throw a NOTICE in cases of a mismatch? (I think
Peter agreed to a NOTICE but not a WARNING) Is that completed?

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Stephan Szabo]

On Wed, 24 Sep 2003, Bruce Momjian wrote:

Tom Lane wrote:

Stephan Szabo <ss****@megazone.bigpanda.com> writes:

On Mon, 22 Sep 2003, Jean-Christian Imbeault wrote:
> Is it right for postgres to accept a foreign key constraint when the
> type of the field is not the same as that of the foreign key?

IIRC in SQL92 it's said that they need to be the same type, but in SQL99
it says that the two types must be comparable. We basically implement the
latter, basically using the existance of a usable equality operator as the
determination of comparable.

Note however that performance may be poor with a cross-type foreign key
reference, if the planner is unable to figure out how to use an index
for the check queries.

Didn't we agree to throw a NOTICE in cases of a mismatch? (I think
Peter agreed to a NOTICE but not a WARNING) Is that completed?

Did that get decided upon? In any case, I don't think domains were talked
about. Should it be decided upon the base type of the domain(s) involved
or just that the final types are different?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly