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?

Devart · Accepted Answer · 2014-06-20T12:19:37.397

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   |
+---------+--------+

Everything works. There is only one problem, because not every user is an administrator. I badly clarified the relationship. It is rather the relationship one to zero or one. — user3106462, Jun 16 '14 at 08:56
In my application are administrators, teachers and students. Each type of user can perform characteristic for your group operations (also has its own specific data, for example, only teacher has classes, only student has friends, etc.). But each of them is user of my application and has login, password, email, firstname, lastname, etc. In PHP, for example, class Student inherits User class. From the point of view of a database, users is table for common data, and admins, teachers, students for specific data. — user3106462, Jun 17 '14 at 08:41
The problem is that deleting a row from users table deletes the corresponding row of admins table, teachers or students, but it does not work vice versa. For example, deleting a row from the table admins will not delete a row from the users table. — user3106462, Jun 17 '14 at 08:47
I have added complete example with CREATE TABLE, INSERT and DELETE statements. — Devart, Jun 20 '14 at 12:20

One to one relationship in MySQL and cascade delete

1 Answers1