----- Original Message -----
From: "Alexander V.Nevsky" <alex_vnru@...>
To: <ib-support@yahoogroups.com>
Sent: Monday, October 08, 2001 11:25 AM
Subject: [ib-support] Re: Can't insert foreign key constraint
> --- In ib-support@y..., "Carsten Schaefer" <ca_schaefer@g...> wrote:
> > Hi,
> > i have a table:
> > create table T_CH_APOS_ZUORD (
> > F_ID_CHARGE INTEGER NOT NULL,
> > F_ID_APOS INTEGER NOT NULL
> > );
> > ALTER TABLE T_CH_APOS_ZUORD
> > ADD PRIMARY KEY
> > (F_ID_CHARGE,F_ID_APOS);
> >
> ...
> >
> > Unsuccessful metadata update
> > STORE RDB$REF_CONSTRAINTS failed
> > action cancelled by trigger (1) to preserve data integrity
> > Name of Referential Constraint not defined in constraints table
> > Statement: ALTER TABLE T_CH_APOS_ZUORD
> > ADD CONSTRAINT FK_CH_APOS_ZUORD_ID_CHARGE FOREIGN KEY
> > (f_id_charge)
> > REFERENCES T_CHARGE
> > (ID_CHARGE)
> > ON UPDATE NO ACTION
> > ON DELETE CASCADE
>
> Hi, Carsten. What indexes (system and general) are defined on
table
> T_CHARGE? If there is 2 indexes
>
> rdb$primaryN (f_id_charge)
> and
> (
> rdb$foreignN (f_id_charge)
> or
> yourindex (f_id_charge)
> )
>
> this is the reason. Indexes that doubles primary or unique used for
> new reference often should be dropped before it and re-created
after.
>
You are right.
This was the problem.
I have two indexes on id_charge.
1 system index cause it is primary key and 1 descending index to get
the max(id_charge) fast. (Question: When adding primary key: How can i
set the order of the system index that will be created ?)
I deleted the descending index and i can insert the foreign key
constraint.
Thank you.
(nevertheless i think its a bug!)