Dropping Related Tables

[SpenBabe]

I have 3 tables (ACCOUNT, BALANCES & ORDERS) which all have primary keys created by their own trigger/generators.

BALANCES also has a foreign key that references ACCOUNT's primary key.

ORDERS also has a foreign key that references ACCOUNT's primary key.

Everything works fine, but I cannot drop the tables.
In Interactive SQL I type :-

DROP TABLE ORDERS;
DROP TABLE BALANCES;
DROP TABLE ACCOUNT;

but I get an error telling me that table BALANCES is in use ! (It has managed to drop ORDERS) I know it has something to do with the foreign keys, but I cannot seem to drop them either.

Any ideas ?


Thanks in advance



Spencer Window (not a joke name)
spencer.window@eastmidlandcomputers.ltd.uk

 

[svanels]

First you have to drop all constraints like foreign keys
that would be:
alter table balances
drop constraint FK_Lookup_In_Account

After this you can drop the table

It is good practice to give your foreign keys and other constraints a name, other wise you have to search for the name interbase automatically assigned.

Regards
S. van Els
SAvanEls@cq-link.sr

 

[SpenBabe]

I have already tried dropping the foreign keys. When I try:-

ALTER TABLE SALRECON DROP CONSTRAINT FK_SALRECON_TRANID

I get the message :-

Unsuccessful metadata update object INDEX is in use
Statement: ALTER TABLE SALRECON DROP CONSTRAINT FK_SALRECON_TRANID

When I try to drop the index I get the message :-

Unsuccessful metadata update
ERASE RDB$INDICES failed action cancelled by trigger (1) to preserve data integrity
Cannot delete index used by an Integrity Constraint
Statement: drop index RDB$FOREIGN20

Surely this is telling me that I cannot drop the key because it is used by an index & I cannot drop the index because it is used by a key !!!!

This is the metadata for the table in question :-

CREATE TABLE "SALRECON"
(
"RECONID" INTEGER DEFAULT 0 NOT NULL,
"ACCID" INTEGER,
"TRANID" INTEGER,
"ASSOCID" INTEGER,
"ALLOCATED" DOUBLE PRECISION,
"BATCHNO" INTEGER DEFAULT 0,
"YEARNO" SMALLINT,
"PERIODNO" SMALLINT,
"ENTRYDATE" TIMESTAMP,
"USERID" VARCHAR(100),
PRIMARY KEY ("RECONID&quot
);
ALTER TABLE "SALRECON" ADD CONSTRAINT "FK_SALRECON_ACCID" FOREIGN KEY ("ACCID&quot REFERENCES SALACCOUNT ("ACCID&quot ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "SALRECON" ADD CONSTRAINT "FK_SALRECON_ASSOCID" FOREIGN KEY ("ASSOCID&quot REFERENCES SALTRAN ("TRANID&quot ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "SALRECON" ADD CONSTRAINT "FK_SALRECON_TRANID" FOREIGN KEY ("TRANID&quot REFERENCES SALTRAN ("TRANID&quot ON UPDATE CASCADE ON DELETE CASCADE; Spencer Window (not a joke name)
spencer.window@eastmidlandcomputers.ltd.uk

 

[svanels]

Do you have triggers, generators, views or stored procedures working with the SALRECON table?

Anything that is linked to this table will prevent altering of it.

You must drop all constraints!!

Check your procedures and triggers


Regards S. van Els
SAvanEls@cq-link.sr