0

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.

  • Thanks for the edit, the ddl makes it much easier to understand what it is you are trying to do. I'll update my answer according to this new info – Lennart Sep 6 '14 at 19:22
  • I don't understand the stock_sector_a table, can a stock belong to different sectors for different users? Should that not be split into two tables, say stock_sector and user_stock? – Lennart Sep 6 '14 at 19:30
  • One additional question, in stock there is an attribute user_id, does it mean that a stock can no be owned by more than one user? – Lennart Sep 6 '14 at 19:37
  • Thanks. A user can have multiples stock; A stock belong to only one user; The stock can have multiple sectors; The user choose whish sectors their stock belong; When I delete a users, all the stock of this user(user_id of the stock table) are deleted; Your question help me with one thing: if a stock belong to only 1 user. I do not need the user_id on the sector table. So I can add a CONSTRAINT in the table like I put in the stock table. – ricardo Sep 7 '14 at 19:56
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)
  • Hi I edited my question. Sorry about my english – ricardo Sep 6 '14 at 18:30
  • This answer is correct. I do not need the user_id in the stock_sector. – ricardo Sep 7 '14 at 20:47
  • Given your comment above, user_id is functionally dependent of stock_code in user_stock, so the primary key should be stock_code instead of (user_id, stock_code). I'll update this. – Lennart Sep 7 '14 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.