MySql on delete cascade

Question

Im having trouble with on delete cascade.

drop table orders;
CREATE TABLE orders(
    o_id        INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c_id        INTEGER NOT NULL REFERENCES Customer(c_id),
    a_id        INTEGER NOT NULL REFERENCES Address(a_id),
    order_date  DATE NOT NULL,
    value       DECIMAL(10,2) NOT NULL CHECK (value >= 0.00),
    method      VARCHAR(20),
    order_type      VARCHAR(30),
    order_interval  VARCHAR(20),
    start_date  DATE NOT NULL,
    admin       VARCHAR(30),
    active      INTEGER NOT NULL,
    notes       VARCHAR(300)
);

drop table order_details;
CREATE TABLE order_details(
    o_id            INTEGER NOT NULL REFERENCES orders(o_id) ON DELETE CASCADE,
    wp_id           INTEGER NOT NULL REFERENCES wp_details(wp_id),
    pack_revision   INTEGER NOT NULL,
    pack_order      INTEGER NOT NULL,
    delivery_date   DATE NOT NULL,
    dispatched_date DATE NOT NULL,
    qty             INTEGER NOT NULL,
    f_id            INTEGER REFERENCES freebies(f_id),
    notes           VARCHAR(300),
    CONSTRAINT  PRIMARY KEY (wp_id, o_id) 
);

When i delete an entry from orders, it does not delete from the order_details table. Are my table definitions wrong?

Make sure you create your tables as InnoDB, since MyISAM doesn't support referencial integrity. I'm not sure if MySQL does it automatically when you use `REFERENCES`. — El Barto, Mar 11 '12 at 13:52
Following on @AndrésGattinoni comment, use "show create table order_details" to see the storage engine. It should be InnoDB. http://stackoverflow.com/questions/511361/how-do-i-use-on-delete-cascade-in-mysql — aingram, Mar 11 '12 at 21:05

score 0 · Answer 1 · answered Jun 09 '16 at 00:05

-- Eliminar la red social Facebook en forma de cascada
--Aqui agrego la posibilidad de On Update Cascade y On Delete Cascade a las
-- reglas generales de la tabla y sus claves foraneas
ALTER TABLE Usuarios WITH CHECK ADD CONSTRAINT 
usuarios_redessociales_fk FOREIGN KEY(red_social)
REFERENCES RedesSociales (Id_red_social)
ON UPDATE CASCADE
ON DELETE CASCADE
-- Efectivamente se borra
DELETE FROM RedesSociales
WHERE Id_red_social = 1
-- Simboliza modificacion (drop) en cierta forma de la estructura de la BD
DROP TABLE Usuarios
DROP TABLE RedesSociales

I've already done this with the DB that We're doing at a practice work, I hope this work for you. Almost the logic is fine, it works. Ask me if you have doubts :)

MySql on delete cascade

1 Answers1