Re: Error 356: Unable to create foreign key. Differences not
Dave Thacker said:
>
> 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?
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 (foreignkey (room_type) references foo constraint fk_room_type );
--
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