Обсуждение: beta4 accepts foreign key of different type than column

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
NOTNULL, -- 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
 

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); --
oldperson_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.

cheers

andrew

On Sun, Jan 06, 2008 at 10:18:35AM -0500, Andrew Dunstan wrote:
> 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.

Sorry, false alarm. I just realised that I had previously also changed 
the person_type.person_type data type to the same enum. 

Cheers,

"Andrew Dunstan" <andrew@dunslane.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!

On Sun, Jan 06, 2008 at 04:07:40PM +0000, Gregory Stark wrote:
> "Andrew Dunstan" <andrew@dunslane.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,