can't add FOREIGN KEY constraint

• Jul 3, 2003

• #2

MartijnTonies

Programmer

Jun 5, 2002

212

NL

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

 

Upvote 0 Downvote

• Jul 29, 2003

• Thread starter
• #3

hstijnen

Programmer

Nov 13, 2002

172

NL

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

 

Upvote 0 Downvote

• Jul 29, 2003

• #4

MartijnTonies

Programmer

Jun 5, 2002

212

NL

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

 

Upvote 0 Downvote

• Jul 29, 2003

• Thread starter
• #5

hstijnen

Programmer

Nov 13, 2002

172

NL

Martijn,

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

What is Database Workbench?

Henk

 

Upvote 0 Downvote

• Jul 29, 2003

• #6

MartijnTonies

Programmer

Jun 5, 2002

212

NL

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

 

Upvote 0 Downvote

• Aug 13, 2003

• Thread starter
• #7

hstijnen

Programmer

Nov 13, 2002

172

NL

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

 

Upvote 0 Downvote

• Aug 13, 2003

• #8

MartijnTonies

Programmer

Jun 5, 2002

212

NL

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

 

Upvote 0 Downvote