Thread: 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
my bad (was: Re: beta4 accepts foreign key of different type than column
From
Louis-David Mitterrand
Date:
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,