Обсуждение: Foreign key constraint accepted even when not same data type
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
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 majordomo@postgresql.org)
Stephan Szabo <sszabo@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
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
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).
Tom Lane wrote: > Stephan Szabo <sszabo@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 pgman@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
On Wed, 24 Sep 2003, Bruce Momjian wrote: > Tom Lane wrote: > > Stephan Szabo <sszabo@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?
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 majordomo@postgresql.org)