can't add FOREIGN KEY constraint

[hstijnen]

Hi
Suddenly I can't add foreign key constraints.Got the following message:
<<
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 temp ADD CONSTRAINT FK1_temp FOREIGN KEY (RID) REFERENCES BEDRIJF (RID) ON UPDATE CASCADE ON DELETE NO ACTION
>>
What can be on hand?

Thanks for help
Henk Stijnen

 

[MartijnTonies]

Henk,

Do you have a primary or unique constraint on table BEDRIJF?



Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[hstijnen]

Just back from holidays. The problem still exists, also after a full backup and restore of my database.

I did the following:

create table tmp1 (key1 varchar(5) not null)
OK
create unique index tmp1_ndx on tmp1(key1)
OK
create table tmp2 (key1 varchar(5))
OK
ALTER TABLE tmp2 ADD CONSTRAINT FK_tmp2 FOREIGN KEY (key1) REFERENCES tmp1 (key1) ON UPDATE CASCADE ON DELETE no action;

Then error message appears:

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 tmp2 ADD CONSTRAINT FK_tmp2 FOREIGN KEY (key1) REFERENCES tmp1 (key1) ON UPDATE CASCADE ON DELETE no action

 

[MartijnTonies]

A unique INDEX is not the same as a unique CONSTRAINT.

Drop the index, create a unique or primary key constraint. Then try again.

btw, if you would be using Database Workbench to create your Foreign Key constraint, you would instantly see there was something wrong and you couldn't create the FK.


Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[hstijnen]

Martijn,

Thank you very much. It works. Is also usefull for another topic. I shall post a hint.

What is Database Workbench?

Henk

 

[MartijnTonies]

Database Workbench is a developer tool for InterBase and Firebird that I created - it's for sale at

http://www.upscene.com

- a trial is available.

Have a look at it if you like - it's packed with features and will make your database development a lot easier.

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[hstijnen]

When I have a primary key on a table, does this act as an index? Or have I to re-create the index?

 

[MartijnTonies]

Hi Henk,

A UNIQUE or PRIMARY KEY constraint automatically creates an ascending index on the target columns.

The same goes for a FK constraint - it automatically creates an ASC index on the columns in the child table.


Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com