alternative to trigger. Cascade

Question

I have 2 tables:

table_a:

id int,
user_id int,
job_id int

and table_b:

id int,
user_id int,
job_id int

I would like achieve something like when I delete a row from table_a it also delete a row from table_b when the user_id and job_id on both tables matches

Today I used this:

CREATE TRIGGER `delete_b`
BEFORE DELETE ON `table_a`
FOR EACH ROW
BEGIN
  DELETE FROM table_b WHERE OLD.user_id = user_id AND OLD.job_id = job_id; 
END $$

It is working fine. But I dont want to use trigger. I would like to use CONSTRAINT cascade on table creation if possible.

like this:

CONSTRAINT `delete_b_k` FOREIGN KEY (`job_id`) REFERENCES `table_b` (`job_id`) ON DELETE CASCADE ON UPDATE CASCADE

But this constraint does not care about the user_id. It will delete all job_id no matter the user_id....

Can I do it with cascade?

Edited with the real tables name:

Sorry for my english I will show my current setup:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

insert into users(name, password) values ('bob', 'passwd'); 

// a user can have multiple stocks

CREATE TABLE `stock` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `code` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
   `value` double DEFAULT NULL,
    `user_id` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `stock_user_id_foreign` (`user_id`),
    CONSTRAINT `stock_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

insert into stock(code, value, user_id) values('AABC', 10, 1);
insert into stock(code, value, user_id) values('BBCC', 4.5, 1);
insert into stock(code, value, user_id) values('EEFF', 7, 1);

-- all the sectors

CREATE TABLE `sector_a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
   PRIMARY KEY (`id`)
);

insert into sector_a(name) values('FOOD');
insert into sector_a(name) values('CAR');
insert into sector_a(name) values('BUILDING');

-- the relationship between the users -> stock -> sector

CREATE TABLE `stock_sector_a` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `user_id` int(10) unsigned DEFAULT NULL,
   `stock_id` int(10) unsigned DEFAULT NULL,
   `sector_id` int(10) unsigned DEFAULT NULL,
   PRIMARY KEY (`id`)
);

insert into stock_sector_a(user_id, stock_id, sector_id) values(1, 1, 1);
insert into stock_sector_a(user_id, stock_id, sector_id) values(1, 1, 2);
insert into stock_sector_a(user_id, stock_id, sector_id) values(1, 1, 3);
insert into stock_sector_a(user_id, stock_id, sector_id) values(1, 2, 2);
insert into stock_sector_a(user_id, stock_id, sector_id) values(1, 2, 3);

-- the current trigger

delimiter //
DROP TRIGGER IF EXISTS delete_sector_a//
create trigger delete_sector_a 
before delete on stock 
for each row 
begin 
     delete from stock_sector_a where OLD.user_id = user_id and OLD.id = stock_id;

end//
delimiter ;

Test:

 mysql> select * from users;
 +----+------+----------+
 | id | name | password |
 +----+------+----------+
 |  1 | bob  | passwd   |
 +----+------+----------+
 1 row in set (0.00 sec)


 select * from stock_sector_a;
 +----+---------+----------+-----------+
 | id | user_id | stock_id | sector_id |
 +----+---------+----------+-----------+
 |  1 |       1 |        1 |         1 |
 |  2 |       1 |        1 |         2 |
 |  3 |       1 |        1 |         3 |
 |  4 |       1 |        2 |         2 |
 |  5 |       1 |        2 |         3 |
 +----+---------+----------+-----------+
 5 rows in set (0.05 sec)

 mysql> select * from stock;
 +----+------+-------+---------+
 | id | code | value | user_id |
 +----+------+-------+---------+
 |  1 | AABC |    10 |       1 |
 |  2 | BBCC |   4.5 |       1 |
 |  3 | EEFF |     7 |       1 |
 +----+------+-------+---------+
 3 rows in set (0.02 sec)

 mysql> delete from stock where id = 1;
 Query OK, 1 row affected (0.10 sec)

 mysql> select * from stock_sector_a;
 +----+---------+----------+-----------+
 | id | user_id | stock_id | sector_id |
 +----+---------+----------+-----------+
 |  4 |       1 |        2 |         2 |
 |  5 |       1 |        2 |         3 |
 +----+---------+----------+-----------+

As you can see it is working. I would like the same result without using trigger if possible.

Answer 1

I'm doing a fair amount of guessing here, but this is how I imagine that your model should look like. In a couple of tables I removed id columns to make it clearer, you may want to put them back in case the keys I have chosen is not stable enough:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `password` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE(`name`) -- questionable
);

insert into users(name, password) values ('bob', 'passwd');

CREATE TABLE `stock` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (stock_code)
);

insert into stock(stock_code) values('AABC'), ('BBCC'), ('EEFF');

-- a user can have 0 or more stocks, a stock have 1 user 
create table user_stock (
    user_id int(10) unsigned NOT NULL,
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `value` double DEFAULT NULL,
    PRIMARY KEY (stock_code),
    constraint fk1_user_stock foreign key (user_id)
        references `users` (id)
            on delete cascade
            on update cascade,
    constraint fk2_user_stock foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade
);

insert into user_stock(stock_code, value, user_id) 
values('AABC', 10, 1), ('BBCC', 4.5, 1), ('EEFF', 7, 1);

CREATE TABLE `sector_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE(name)
);

insert into sector_a(name) values('FOOD'), ('CAR'), ('BUILDING');

-- a stock can belong to 0 or more sectors, a sector can have 0 or more stocks
CREATE TABLE `stock_sector_a` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `sector_id` int(10) unsigned not NULL,
    PRIMARY KEY (stock_code, sector_id),
    constraint fk1_stock_sector_a foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade,
    constraint fk2_stock_sector_a foreign key (sector_id)
        references sector_a (id)
            on delete cascade
            on update cascade
);

insert into stock_sector_a(stock_code, sector_id)
values('AABC', 1), ('AABC', 2), ('AABC', 3), ('BBCC', 2), ('BBCC', 3);

MariaDB [test]> delete from stock where stock_code = 'AABC';
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from user_stock;
+---------+------------+-------+
| user_id | stock_code | value |
+---------+------------+-------+
|       1 | BBCC       |   4.5 |
|       1 | EEFF       |     7 |
+---------+------------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from stock_sector_a;
+------------+-----------+
| stock_code | sector_id |
+------------+-----------+
| BBCC       |         2 |
| BBCC       |         3 |
+------------+-----------+
2 rows in set (0.00 sec)