473,326 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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


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

Nov 11 '05 #1
6 1980
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: dcp | last post by:
I just installed the 4.1.0-alpha-max-nt version of MySql and have just started playing around with it. My first test was to try to create a couple of tables, one with a foreign key constraint. ...
0
by: Jeremiah Jacks | last post by:
I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using = the pre-compiled binaries. I have a database with INNODB tables. When I insert a row into one of the child tables, I get...
10
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed...
31
by: Robert Brown | last post by:
Let's say I have a type hierarchy: (just an example) the general entity customer: CREATE TABLE customer(customer_id int, customer_name varchar(250), customer_type int) three specific...
4
by: teddysnips | last post by:
This is a rather abstract question about data design, but I ask it here because a) the database is SQL Server, and b) you're such a learned bunch! Let's assume the classic relation of Customers...
0
by: Jean-Christian Imbeault | last post by:
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: ...
13
by: Tibor | last post by:
I am using PostgreSQL 7.4.1 (only through psql) I know, that the command ALTER TABLE OFFICES DROP PRIMARY KEY (CITY); and its foreign key equivalent: ALTER TABLE SALESREPS DROP CONSTRAINT
2
by: kal stevens | last post by:
I have been trying to write a database schema in mysql, and I cant figure this out. Here is a database schema DROP DATABASE IF EXISTS d; CREATE DATABASE d;
3
by: moorcroft | last post by:
Hi I am trying to write some sql to because we have a missing foregin key constraint. The following is what I have been asked to do: 1. COMPULSORY_WORK.reference to LISTED_BUILDING.reference ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.