trying to combine foreign key with trigger

Question

I have problem with cleaning my database up when some row and relation is deleted.

CREATE TABLE IF NOT EXISTS `cms_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `id_account_type` smallint(2) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

Above are some users. There are children of those users and they are stored in other table:

CREATE TABLE IF NOT EXISTS `cms_users_relations` (
  `id_user` int(10) unsigned NOT NULL,
  `id_parent` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `id_user` (`id_user`),
  KEY `constraint_9` (`id_parent`)
);


ALTER TABLE `cms_users_relations`
  ADD CONSTRAINT `constraint_8` FOREIGN KEY (`id_user`) REFERENCES `cms_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `constraint_9` FOREIGN KEY (`id_parent`) REFERENCES `cms_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

The problem is that when I remove parent from cms_users all children should be removed too but they don't. I created a triger for this purpose but it don't do the job:

DELIMITER //
CREATE TRIGGER `delete_user` AFTER DELETE ON `cms_users_relations`
 FOR EACH ROW BEGIN 
  DELETE FROM cms_users WHERE OLD.id_user = cms_users.id; 
 END
//
DELIMITER ;

It looks like mysql server don't bother that we are deleting the user, then deleting relation by foreign key and then we should put the trigger...

Any ideas?

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, Oct 01 '13 at 04:56

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

That trigger should be created on cms_users table, not on cms_users_relations and then delete on cms_users_relations, like the following:

DELIMITER //
CREATE TRIGGER `delete_user` AFTER DELETE ON `cms_users`
 FOR EACH ROW BEGIN 
  DELETE FROM cms_users_relations WHERE OLD.id_user = cms_users_relations.id; 
 END
//
DELIMITER ;

The trigger is the delete you make of the parent in cms_users, so this is where you want to create it.

I only looked at how you created your trigger, but missed the part where you did:

ALTER TABLE `cms_users_relations`
  ADD CONSTRAINT `constraint_8` FOREIGN KEY (`id_user`) REFERENCES `cms_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `constraint_9` FOREIGN KEY (`id_parent`) REFERENCES `cms_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Using this, you shouldn't need to create a trigger to delete the elements on cms_users relations.

See this fiddle to see it working.

You might want to take a look at the answers on this question to see what you might have different for this not to work.

edited May 23 '17 at 11:43

Community

1
1

answered Sep 30 '13 at 22:32

Filipe Silva

21,189
5
53
68

I've added the trigger, checked if my engine is set to MyISAM and still don't work :( When I delete parent, childrens are still there... – webrama.pl Oct 01 '13 at 19:11
1

@PAM . From the question i linked, i think you should have the engine InnoDB, not MyISAM for that to work. – Filipe Silva Oct 01 '13 at 19:24
Of course I have ENGINE=InnoDB DEFAULT CHARSET=utf8. Sorry it's a mistake. MyISAM don't support foreign keys. – webrama.pl Oct 01 '13 at 19:27
@PAM . With ENGINE=InnoDB and the way you created your FOREIGN KEYS you don't need the trigger. Just remove the trigger and try again. If it doesn't work, try creating a fiddle with some sample data you have. – Filipe Silva Oct 01 '13 at 21:07
1

@PAM. Another thing. You have id as int(11) on cms_users and as int(10) on the second table. They should have the same type in both tables. – Filipe Silva Oct 02 '13 at 08:42

trying to combine foreign key with trigger

1 Answers1