By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,692 Members | 1,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,692 IT Pros & Developers. It's quick & easy.

Foreign key constraint accepted even when not same data type

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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)

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

P: n/a
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

Nov 11 '05 #4

P: n/a
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

Nov 11 '05 #5

P: n/a
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 | (610) 359-1001
+ 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

Nov 12 '05 #6

P: n/a
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

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.