One to one relationship in MySQL and cascade delete

Question

I have two tables: User and Admin.

+-------------------------+
| TABLE : user
+-------------------------+
| id: PRIMARY_KEY
| username
| password
+-------------------------+

+-------------------------+
| TABLE: admin
+-------------------------+
| user_id: FOREIGN_KEY
| e-mail
+-------------------------+

Admin is also user, therefore, both tables are together in relation 1:1. When a row is deleted from the User table, it is also removed from the table Admin (at admin.user_id is setted relationship ON DELETE CASCADE), but what if I delete a row from the table Admin? Then it isn't removed from the User table and this makes mess in the database. Is this any solution for this? How to make realtionship in both sides?

Answer 1

Add similar foreign key to admin table -

ALTER TABLE user
  ADD CONSTRAINT FK_user_admin_user_id FOREIGN KEY (id)
    REFERENCES admin(user_id) ON DELETE CASCADE ON UPDATE RESTRICT;

Now, you can remove rows from user or admin table, and related records will be removed.

Use FOREIGN_KEY_CHECKS variable to add new records, e.g. -

SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO user(id) VALUES(1);
INSERT INTO admin(user_id) VALUES(1);

SET FOREIGN_KEY_CHECKS = 1;

Full example with output -

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(255) DEFAULT NULL,
  password varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FK_user_admin_user_id FOREIGN KEY (id)
  REFERENCES admin (user_id) ON DELETE CASCADE ON UPDATE RESTRICT
)
ENGINE = INNODB;

CREATE TABLE admin (
  user_id int(11) NOT NULL AUTO_INCREMENT,
  `e-mail` varchar(255) DEFAULT NULL,
  PRIMARY KEY (user_id),
  CONSTRAINT FK_admin_user_id FOREIGN KEY (user_id)
  REFERENCES user (id) ON DELETE CASCADE ON UPDATE RESTRICT
)
ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO user(id) VALUES(1);
INSERT INTO user(id) VALUES(2);
INSERT INTO user(id) VALUES(3);

INSERT INTO admin(user_id) VALUES(1);
INSERT INTO admin(user_id) VALUES(2);
INSERT INTO admin(user_id) VALUES(3);

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM user WHERE id = 1;
DELETE FROM admin WHERE user_id = 2;

SELECT * FROM user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  3 | NULL     | NULL     |
+----+----------+----------+

SELECT * FROM admin;
+---------+--------+
| user_id | e-mail |
+---------+--------+
|       3 | NULL   |
+---------+--------+