Skip site navigation (1) Skip section navigation (2)

Re: beta4 accepts foreign key of different type than column

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Andrew Dunstan" <andrew(at)dunslane(dot)net>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: beta4 accepts foreign key of different type than column
Date: 2008-01-06 16:07:40
Message-ID: 87d4sflyyb.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
"Andrew Dunstan" <andrew(at)dunslane(dot)net> writes:

> Louis-David Mitterrand wrote:
>> Hi, 
>>
>> I changed the data type on a column (to an enum) but the previous foreign key
>> to the old table (replaced by the enum) is still accepted event though the
>> data types are now different. Is that the expected behavior?
>>
>> CREATE TABLE person_to_event (
>>     id_person integer NOT NULL,
>>     id_event integer NOT NULL,
>>     person_type person_type_new NOT NULL, -- new ENUM type
>>     "character" text
>> )
>>
>> ALTER TABLE ONLY person_to_event
>>     ADD CONSTRAINT person_to_event_person_type_fkey FOREIGN KEY (person_type) REFERENCES person_type(person_type); -- old person_type table, text column
>
> Please send us a more complete example. Also, using the same name for a table,
> a field and a type makes reading it very confusing. Please make sure your
> example uses names for just one purpose.

(Well you can't use the same name for a table and a type since a table defines
a type of that name implicitly.)

I think what's missing here is the definition of the person_type table. Your
comment says "text column" but I get this with a text column target:

postgres-# ERROR:  foreign key constraint "person_to_event_person_type_fkey" cannot be implemented
DETAIL:  Key columns "person_type" and "person_type" are of incompatible types: person_type_new and text.

I get the same thing if it's an integer field.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

In response to

Responses

pgsql-hackers by date

Next:From: Louis-David MitterrandDate: 2008-01-06 16:12:00
Subject: Re: beta4 accepts foreign key of different type thancolumn
Previous:From: Louis-David MitterrandDate: 2008-01-06 15:59:32
Subject: my bad (was: Re: beta4 accepts foreign key of different type thancolumn

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group