MySQL delete records after deleting group which they belong

Question

There are tables:

drop table if exists groups;
create table groups
(
    symbol varchar(10);
    group_name varchar(10);
    deafult_discount int(10)
    PRIMARY KEY (symbol)
);

insert into groups(symbol,group_name,deafult_discount)
values
('medium','medium_company',5),
('small','small_company',0),
('big','big_company',10);

use exercise;

create table Companies 
(
    ID  int,
    Name    varchar(100),
    City    varchar(100),
    c_size varchar(100),
    PRIMARY KEY (ID)
);

insert into Companies(ID, Name, city, company_size) 
values
(222, 'Karma LLC','CITY2', 'big'),
(223, 'Manny Ind.','CITY1', 'medium'),
(224, 'Random PLC','CITY1', 'medium'),
(225, 'Hijack LLC','CITY1', 'medium'),
(226, 'Travels LLC','CITY1', 'small'),
(227, 'Mirana Ind.','CITY2', 'small'),
(228, 'Polla Ind.','CITY3', 'small'),
(229, 'Americano LLC','CITY3', 'small'),
(230, 'Macaroni LLC','CITY4', 'small');

I want to know the way of auto deleting companies, after one of the group is deleted. For example, I remove group "medium" from table groups and I want to remove at the same time remove all companies which are company_size='medium'.
I don't necesser need a query code, it may be description how to do it.

read about foreign keys and triggers – Iłya Bursov Jun 01 '14 at 22:19 — Iłya Bursov, Jun 01 '14 at 22:19

score 0 · Accepted Answer · edited Jun 01 '14 at 22:26

0

as I wrote in comments - foreign keys and triggers and your friends, also you can run this query to delete all orphaned companies to keep data integrity

delete from `Companies` where `company_size` NOT IN (select `symbol` from `groups`);

You can use triggers but in case you are on shared server then you can put the same query in some cron.

edited Jun 01 '14 at 22:26

Waqas Jamal

30
6

answered Jun 01 '14 at 22:21

Iłya Bursov

23,342
4
33
57

MySQL delete records after deleting group which they belong

1 Answers1