×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

can't add FOREIGN KEY constraint

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

RE: can't add FOREIGN KEY constraint

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

RE: can't add FOREIGN KEY constraint

(OP)
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

RE: can't add FOREIGN KEY constraint

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

RE: can't add FOREIGN KEY constraint

(OP)
Martijn,

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

Database Workbench is a developer tool for InterBase and Firebird that I created - it's for sale at 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

RE: can't add FOREIGN KEY constraint

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

RE: can't add FOREIGN KEY constraint

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close