Error 356: Unable to create foreign key. Differences not

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave Thacker - 26 Oct 2005 19:58 GMT

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

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

Reply to this Message

Obnoxio The Clown - 26 Oct 2005 21:05 GMT

Dave Thacker said:

> IDS 9.4FC6 on AIX 5.3. ISQL 7.32FC2
>
[quoted text clipped - 69 lines]
> Support says to check *really carefully* to find differences in the two
> columns. Does anyone have a suggestion on another place to check?

I'm probably missing the point here, but isn't the PK / FK relationship
supposed to be on the whole key?

primary key (*prop,*room_type)

vs

alter table bar add constraint (foreign
key (room_type) references foo constraint fk_room_type );

Signature

Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
- Coluche

"You are an index and a prologue to the history of lust and foul thoughts."
- William Shakespeare
sending to informix-list

Reply to this Message

Richard Harnden - 27 Oct 2005 00:50 GMT

> IDS 9.4FC6 on AIX 5.3. ISQL 7.32FC2
>
[quoted text clipped - 11 lines]
> 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
> ( [...]
[quoted text clipped - 4 lines]
> primary key (prop,room_type) constraint "informix".foorooms_pk
> );

Both foo and bar have the same primary key; is that what you want?

Reply to this Message

Art S. Kagel - 27 Oct 2005 23:13 GMT

> IDS 9.4FC6 on AIX 5.3. ISQL 7.32FC2
>
[quoted text clipped - 11 lines]
> 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
[quoted text clipped - 58 lines]
> Dave Thacker
> sending to informix-list

Reply to this Message