Oninit cdi / Archive
Talk to an expert →

FOREIGN KEY Problem

Posted in 2005

Topics: Data Types & Schema Design, Triggers, Constraints & Referential Integrity

Victor Dario Martinez — — source: Usenet: comp.databases.informix

Hi, i am a dude on foreign key creation in informix 9.20 , why this code fails with -356 error ( SQL Error (-356) : Data type of the referencing and referenced columns do not match.) ? --DROP TABLE A; CREATE TABLE A ( E_N integer NOT NULL, N varchar(5) NOT NULL, R char(1) NOT NULL ); ALTER TABLE A ADD CONSTRAINT PRIMARY KEY (E_N, N) CONSTRAINT pk_A; ALTER TABLE A ADD CONSTRAINT UNIQUE (N, R) CONSTRAINT pk_U_A; --DROP TABLE B; CREATE TABLE B ( N varchar(5) NOT NULL, R char(1) NOT NULL ); ALTER TABLE B ADD CONSTRAINT FOREIGN KEY (N,R) REFERENCES A CONSTRAINT fk_A_B; sending to informix-list

↪ replying to Victor Dario Martinez
Art S. Kagel — — source: Usenet: comp.databases.informix

Victor Dario Martinez wrote: You posted this earlier today on the IIUG IDS Forum and got several perfect answers there. Art S. Kagel > Hi, i am a dude on foreign key creation in informix 9.20 , why this > code fails with -356 error ( SQL Error (-356) : Data type of the > referencing and referenced columns do not match.) ? > > --DROP TABLE A; > CREATE TABLE A ( > E_N integer NOT NULL, > N varchar(5) NOT NULL, > R char(1) NOT NULL > );> > ALTER TABLE A > ADD CONSTRAINT PRIMARY KEY (E_N, N) > CONSTRAINT pk_A;> > ALTER TABLE A > ADD CONSTRAINT UNIQUE (N, R) > CONSTRAINT pk_U_A;> > --DROP TABLE B; > CREATE TABLE B ( > N varchar(5) NOT NULL, > R char(1) NOT NULL > );> > ALTER TABLE B > ADD CONSTRAINT FOREIGN KEY (N,R) > REFERENCES A > CONSTRAINT fk_A_B;> > > sending to informix-list

↪ replying to Victor Dario Martinez
spamntrapf@yahoo.com — — source: Usenet: comp.databases.informix

I didn't see the IIUG forum (Hi Art.) so I'll answer more directly: Your primary key is a composite of <integer, varchar> The foreign key that you want to reference it is a composite of <varchar, char>. The integer in position[1] the primary key mismatches the varchar in position[1] of the intended foreign key. And the varchar in position[2] of the primary key likely mismatches the char in position[2] of the intended foreign key. Make the components' data types match. +--------------------------------------------------- Jacob Salomon --+ |Ten days and nights, with sleepless eye, I watched that wretched man| |And since, I never dare to write as funny as I can. | +------------ Oliver Wendell Holmes "The Height of the Ridiculous" --+

Related threads