Have one entry automatically delete when its key is deleted mysql

Question

I've got a user table with an id column

a profile table with an id column and a user_id col which refers to id in the user table

I want to automatically delete the profile when I delete the user.

Is this possible in mysql?

possible duplicate of [MySQL foreign key constraints, cascade delete](http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete) — jpw, Aug 15 '15 at 19:29
It is indeed possible using foreign keys. See this: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html for more information (or look at the duplicate question). — jpw, Aug 15 '15 at 19:32

score 2 · Answer 1 · edited May 23 '17 at 12:15

2

You can do this ON DELETE CASCADE command in the table definition.

See: MySQL foreign key constraints, cascade delete

Or you can use a trigger to do the same:

CREATE
TRIGGER  `delete_from_profile`
AFTER DELETE ON `user`
FOR EACH ROW
BEGIN    
    DELETE 
    FROM profile
    WHERE user_id = old.id; 
END

edited May 23 '17 at 12:15

Community

1
1

answered Aug 15 '15 at 19:38

Praveen

8,945
4
31
49

BentCoder · Accepted Answer · 2015-08-15T20:11:06.477

Assume that there is 1 to N relationship in between PARENT and CHILD entities so when you delete a PARENT then the associated CHILD(s) will be deleted as you wanted. For that you use ON DELETE CASCADE like below.

What you need to be aware of is, depending on your design if the CHILD entity is associated with one or more entities then you might cause unexpected issue such as breaking relationships, removing records in associated tables etc. Also read up on the storage engines in link above as well, you cannot use MuISAM.

PARENT

CREATE TABLE `parent` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CHILD

CREATE TABLE `child` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `FK_1_idx` (`parent_id`),
  CONSTRAINT `FK_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

EXAMPLE

Populate Parent

mysql> INSERT INTO parent (name) VALUES ('parent_1'), ('parent_2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM parent;
+----+----------+
| id | name     |
+----+----------+
|  1 | parent_1 |
|  2 | parent_2 |
+----+----------+
2 rows in set (0.00 sec)

Populate Child

mysql> INSERT INTO child (name, parent_id) VALUES ('child_1', 1), ('child_2', 1), ('child_3', 2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM child;
+----+---------+-----------+
| id | name    | parent_id |
+----+---------+-----------+
|  1 | child_1 |         1 |
|  2 | child_2 |         1 |
|  3 | child_3 |         2 |
+----+---------+-----------+
3 rows in set (0.00 sec)

Delete

mysql> DELETE FROM parent WHERE name = 'parent_1';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM child;
+----+---------+-----------+
| id | name    | parent_id |
+----+---------+-----------+
|  3 | child_3 |         2 |
+----+---------+-----------+
1 row in set (0.00 sec)

Have one entry automatically delete when its key is deleted mysql

2 Answers2