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

Re: beta4 accepts foreign key of different type thancolumn

From: Louis-David Mitterrand <vindex+lists-pgsql-hackers(at)apartia(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: beta4 accepts foreign key of different type thancolumn
Date: 2008-01-06 16:12:00
Message-ID: 20080106161200.GA23681@apartia.fr (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sun, Jan 06, 2008 at 04:07:40PM +0000, Gregory Stark wrote:
> "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.

I jumped the gun a bit too fast (should refrain from working on sundays 
before a good nap). You are of course right. As I said in a previous 
message, I was positive the person_type.person_type was still text even 
though I had changed it to the same enum (and forgot about it). 

Thanks for your answer and time,  

In response to

pgsql-hackers by date

Next:From: Robert TreatDate: 2008-01-06 17:25:55
Subject: Re: Dynamic Partitioning using Segment Visibility Maps
Previous:From: Gregory StarkDate: 2008-01-06 16:07:40
Subject: Re: beta4 accepts foreign key of different type than column

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