Error 356: Unable to create foreign key. Differences not
IDS 9.4FC6 on AIX 5.3. ISQL 7.32FC2
Goal: All data entered into the bar.room_type column should match a
valid room type in foo.room_type.
Solution attempted: Put a foreign key on bar.room_type referring to
foo.room_type.
Problem.
alter table bar add constraint (foreign
key (room_type) references foo constraint fk_room_type );
356: Data type of the referencing and referenced columns do not match.
Error in line 2Near character position 47
Schemas:
create table "informix".bar
(
prop char(8) not null ,
group_code integer not null ,
room_type char(6) not null ,
who_stamp char(8) not null ,
when_stamp datetime year to second not null ,
primary key (prop,room_type) constraint "informix".bar_pk
);
{ TABLE "informix".foo row size = 306 number of columns = 12 index size
= 19 }
create table "informix".foo
(
prop char(8) not null ,
room_type char(6) not null ,
room_cat integer not null ,
bed_type char(2),
smoking char(1),
max_adults integer,
rollaways integer,
cribs integer,
to_sell_qty integer,
room_desc varchar(255),
max_persons integer,
hotel_only char(1),
check (smoking IN ('Y' ,'N' ,'R' )) constraint
"informix".foo_smk_check,
primary key (prop,room_type) constraint "informix".foorooms_pk
);
revoke all on "informix".foo from "public";
I looked at the column info in the syscolumns table to find the
difference in the the two columns.....
tabname foo
colname room_type
tabid 812
colno 2
coltype 256
collength 6
colmin
colmax
extended_id 0
tabname bar
colname room_type
tabid 815
colno 3
coltype 256
collength 6
colmin
colmax
extended_id 0
Support says to check *really carefully* to find differences in the two
columns. Does anyone have a suggestion on another place to check?
TIA
Dave Thacker
sending to informix-list
Dave Thacker wrote:
> IDS 9.4FC6 on AIX 5.3. ISQL 7.32FC2
>
> Goal: All data entered into the bar.room_type column should match a
> valid room type in foo.room_type.
>
> Solution attempted: Put a foreign key on bar.room_type referring to
> foo.room_type.
>
> Problem.
> alter table bar add constraint (foreign
> key (room_type) references foo constraint fk_room_type );>
> 356: Data type of the referencing and referenced columns do not match.
> Error in line 2> Near character position 47
But foo's primary key is (prop,room_type), the error is complaining that
the number of columns don't match, rather than the 'room_types' don't.
You'd need:
alter table foo
add constraint unique (room_type);
alter table bar
add constraint foreign key (room_type) references foo(room_type);
Although, normally I'd expect 'room_type' to be the pk of a table called
'room_types'!
> create table "informix".bar
> ( [...]
> primary key (prop,room_type) constraint "informix".bar_pk
> );
> create table "informix".foo
> ( [...]
> primary key (prop,room_type) constraint "informix".foorooms_pk
> );
Both foo and bar have the same primary key; is that what you want?
Dave Thacker wrote:
> IDS 9.4FC6 on AIX 5.3. ISQL 7.32FC2
>
> Goal: All data entered into the bar.room_type column should match a
> valid room type in foo.room_type.
>
> Solution attempted: Put a foreign key on bar.room_type referring to
> foo.room_type.
>
> Problem.
> alter table bar add constraint (foreign
> key (room_type) references foo constraint fk_room_type );>
> 356: Data type of the referencing and referenced columns do not match.
> Error in line 2> Near character position 47
The columns referencing must be contained in either a primary key or unique
key constraint in the referenced table. The primary key of foo is
(prop,room_type) not just room_type. So, either include prop in the foreign
key of bar or create a unique key constraint on foo.room_type. Since bar
contains prop I'd guess the former is what you need. Don't you want to know
that values in bar.room_type are contained only in rows of foo that have the
same prop value? If not, then you need to create a room_types table and
reference the room_types.room_type as a foreign key in BOTH of these tables
(or just in foo and use the primary key of foo in bar as the foreign key).
Art S. Kagel
> Schemas:
> create table "informix".bar
> (
> prop char(8) not null ,
> group_code integer not null ,
> room_type char(6) not null ,
> who_stamp char(8) not null ,
> when_stamp datetime year to second not null ,
> primary key (prop,room_type) constraint "informix".bar_pk
> );
> { TABLE "informix".foo row size = 306 number of columns = 12 index size
> = 19 }
> create table "informix".foo
> (
> prop char(8) not null ,
> room_type char(6) not null ,
> room_cat integer not null ,
> bed_type char(2),
> smoking char(1),
> max_adults integer,
> rollaways integer,
> cribs integer,
> to_sell_qty integer,
> room_desc varchar(255),
> max_persons integer,
> hotel_only char(1),
>
> check (smoking IN ('Y' ,'N' ,'R' )) constraint
> "informix".foo_smk_check,
> primary key (prop,room_type) constraint "informix".foorooms_pk
> );
> revoke all on "informix".foo from "public";>
> I looked at the column info in the syscolumns table to find the
> difference in the the two columns.....
> tabname foo
> colname room_type
> tabid 812
> colno 2
> coltype 256
> collength 6
> colmin
> colmax
> extended_id 0
>
> tabname bar
> colname room_type
> tabid 815
> colno 3
> coltype 256
> collength 6
> colmin
> colmax
> extended_id 0
>
> Support says to check *really carefully* to find differences in the two
> columns. Does anyone have a suggestion on another place to check?
>
> TIA
>
> Dave Thacker
> sending to informix-list