Referential Integrity Problems

[Oppenhiemer]

Hi -

Having just used DataPump to import all tables from an Access 97 database to Interbase - there seems to be a problem when trying to create referential integrity constriants.

When initially importing the data - the referential constriants were create by datapump. The only problem was that I wanted to add the CASCADE clause to the constraints. So I dropped the constraint created and attempted to create a new one with IBConsole.

Here is the statement I used to try create the constraint :

alter table CUSTRATINGS
add constraint FK_CUSTRATINGS
foreign key (CUSTRATE)
references RATINGS(RATING)
on delete CASCADE
on update CASCADE

Syntactically I can see no problem with the statement. I can also see no problem with the tables concerned (the data has referential integrity - no orphan records should prevent one being set up. Also it has just had the same constraint applied without the cascade clause.) However I recieve the same OBSCURE error message when trying yo execute the above statement :

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 CUSTRATINGS
add constraint FK_CUSTRATINGS foreign key (CUSTRATING) references RATINGS(RATING) on delete CASCADE on update CASCADE

Could anyone please shed some light on what the ACTUAL problem may be (the error messages created are next to useless.)

Many thanks..

Opp.

 

[hstijnen]

I had the same problem. Martijn gave the answer! See my topic "can't add foreign key"