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: IIUG Forums & Mailing Lists

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 ACONSTRAINT fk_A_B;

↪ replying to Victor Dario Martinez
ART KAGEL, BLOOMBERG/ 731 LEXIN — — source: IIUG Forums & Mailing Lists

You did not name the corresponding columns from table A in the foreign key definition. That defaults to using the referenced table's primary key which is e_n, n not n, r. The match is done by key not by name. Just change the constraint command as follows: ALTER TABLE B ADD CONSTRAINT FOREIGN KEY (N,R) REFERENCES A(N,R)CONSTRAINT fk_A_B; Art S. Kagel ----- Original Message ----- From: Victor Dari.... <dmartinez@siu.edu.ar> At: 5/12 17:05 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 ACONSTRAINT fk_A_B;

↪ replying to Victor Dario Martinez
Michael Hoffman — — source: IIUG Forums & Mailing Lists

Victor, When creating a foreign key, if you do not specifically mention the referenced columns, the constraint will use the referenced table's Primary Key. In your case, you are trying to match N, R with E_N, N, which is an obvious data mismatch. Try this: ALTER TABLE B ADD CONSTRAINT FOREIGN KEY (N,R) REFERENCES A(N,R)CONSTRAINT fk_A_B; > > 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; > > >

↪ replying to Victor Dario Martinez
Bob Roussey — — source: IIUG Forums & Mailing Lists

Just a note: While investigating another problem about an index, I found in the manuals (9.40 IDS) that the optimizer will not use an index on a varchar column. That may only apply to indexes where the varchar is the root of an index; I did not pursue it any further. (I just cursed the application vendor for using varchars in many places!)

Related threads