Re: Error 356: Unable to create foreign key. Differences not
- From: Jonathan Leffler <jleffler.iiug@xxxxxxxxx>
- Date: Wed, 26 Oct 2005 21:32:35 -0700
On 10/26/05, Obnoxio The Clown <obnoxio@xxxxxxxxxxxxxxx> wrote:
>
> 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 [...]
> >
> > 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 );
No, you got it. When you say 'references foo', it is a short hand for
'references the primary key of table foo', which in this case is a compound
key and the first column of it is not the room type. The error mesage is
atrocious; you could attempt to get a bug entered on that -- it could say
something like "cannot create a foreign key when the number of columns in
the foreign key does not match the number of columns in the referenced
primary key", for example, or "cannot create foreign key referencing
foo.prop because of a type mismatch". Either of those would at least point
you in the right direction. There are technical reasons why it is hard to
get more than one string substituted into a message transferred to the
client code (hint: think about where the data comes from in the SQLCA
structure!).
If you want to use some other column, then you say 'references
foo(room_type)'.
There then needs to be at least an index with foo.room_type as the leading
field - probably an index on just foo.room_type (someone would need to
experiment). However, I'd suggest that normally, you'd have a separate table
that defines valid room_types, and both foo and bar would cross-reference
the primary key of that table.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@xxxxxxxxxxxxx, jleffler@xxxxxxxxxx
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
sending to informix-list
.
- Prev by Date: Re: Informix 4GL Help !!
- Next by Date: Re: IDS 7.30 do not start - NT
- Previous by thread: David Williams looking for a new job!
- Next by thread: Problem with recursive SPL procedure
- Index(es):
Relevant Pages
|
