can't add FOREIGN KEY constraint
can't add FOREIGN KEY constraint
(OP)
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
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
RE: can't add FOREIGN KEY constraint
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
RE: can't add FOREIGN KEY constraint
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
RE: can't add FOREIGN KEY 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
RE: can't add FOREIGN KEY constraint
Thank you very much. It works. Is also usefull for another topic. I shall post a hint.
What is Database Workbench?
Henk
RE: can't add FOREIGN KEY constraint
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
RE: can't add FOREIGN KEY constraint
RE: can't add FOREIGN KEY constraint
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