CODEWITHSUNDEEP
mysqlsqldatabase
OFRBG
OFRBG

Reputation: 1778

Correct way to remove entry from a SQL table along with the relations

If we have TableA and TableB related by TableAB where TableAB has foreign keys for the first two table, then what's the go-to way of deleting an entry from TableA? Up to now if used a property such as IsActive with a bit to describe if the entry is still valid. However, that makes it a little problematic when there are "ghost entries" in the relation tables, such as TableAB.

How should I proceed?

One chaining table in question.

CREATE TABLE EntradaContadorCliente (

    ClaveECC            int AUTO_INCREMENT not null,
    ClaveCliente        int not null,
    ClaveContador       int not null,
    ClaveEntrada        int not null,

    PRIMARY KEY (ClaveECC),
    FOREIGN KEY (ClaveCliente) REFERENCES Cliente(ClaveCliente),
    FOREIGN KEY (ClaveContador) REFERENCES Contador(ClaveContador),
    FOREIGN KEY (ClaveEntrada) REFERENCES EntradaBitacora(ClaveEntrada)

);

Upvotes: 0

Views: 96

Answers (2)

david sam
david sam

Reputation: 531

first you disable all foreign key with:-

 alter table table_name
 nocheck constraint fk_constraint

then you delete data in parent table.

Upvotes: 0

Rahul
Rahul

Reputation: 77896

Since TableA and TableB related by TableAB; which means TableAB is a chaining table. One way is to use ON DELETE CASCADE for cascading the delete operation on primary table.

Alternative is to, manually delete the entries from your chaining table once the entry has been deleted from primary table.

You can use a ALTER statement to re-create the FK constraint like

ALTER TABLE `TableAB` DROP FOREIGN KEY FK_KEY_Test;
ALTER TABLE `TableAB` ADD CONSTRAINT FK_KEY_Test FOREIGN KEY ('some_column') 
REFERENCES `TableA` ('Test_column1') ON UPDATE CASCADE ON DELETE CASCADE;

From MySQL Documentation:

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.

Upvotes: 1

Related Questions