beta4 accepts foreign key of different type than 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); -- 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.

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,