CODEWITHSUNDEEP
mysql
Tomasz Mularczyk
Tomasz Mularczyk

Reputation: 36179

MYSQL on delete cascade example

I can't figure out how to use on delete cascade in my example.

CREATE TABLE `users` (
  `login` varchar(16) NOT NULL,
  `password` varchar(16) NOT NULL,
  PRIMARY KEY (`login`),
  UNIQUE KEY `login` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `employees` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) NOT NULL,
  `Surname` varchar(25) NOT NULL,
  `Birthday` date NOT NULL,
  `Adres` varchar(50) NOT NULL,
  `Telephone` varchar(25) NOT NULL,
  `login` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `login` (`login`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`login`) REFERENCES `users` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

One table stores informations about employee and if he has account(login) there is another table with logins and passwords.

My goals:

  1. Not every employee needs to have user account(that means - unique record in Users table) so Employees.Login shouldn't be restricted with NOT NULL.
  2. I want possiblity to add new user in Users and then assign it to some employee.
  3. When I delete employee, related unique user is also deleted(if he had one).

First two are already done. I don't know how to make 3 happen now.

Upvotes: 1

Views: 228

Answers (3)

Tomasz Mularczyk
Tomasz Mularczyk

Reputation: 36179

Based on suggestion from Hammerite now my tables definion looks like this:

CREATE TABLE `employees` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) NOT NULL,
  `Surname` varchar(25) NOT NULL,
  `Birthday` date NOT NULL,
  `Adres` varchar(50) NOT NULL,
  `Telephone` varchar(25) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `users` (
  `login` varchar(16) NOT NULL,
  `password` varchar(16) NOT NULL,
  `employee_id` int(11) NOT NULL,
  PRIMARY KEY (`login`),
  UNIQUE KEY `login` (`login`),
  UNIQUE KEY `employee_id` (`employee_id`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1. I have deleted login column from employees table
  2. I have created another column employee_id in User table. It's unique foreign key and cannot be null since I dont want user accounts that are not used by anybody.

Now If I do this:

INSERT INTO users VALUES('login123','password',2);

then this row will be deleted after I do this:

DELETE FROM employees WHERE id=2;

I was sure that I need to make some reference in employees table to users table, thats why I didn't think about solution like this.

THANKS!

Upvotes: 0

Shyam Ranpara
Shyam Ranpara

Reputation: 624

Use this query : 

        CREATE TABLE `users` (
      `login` varchar(16) NOT NULL,
      `password` varchar(16) NOT NULL,
      PRIMARY KEY (`login`),
      UNIQUE KEY `login` (`login`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


    CREATE TABLE `employees` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Name` varchar(20) NOT NULL,
      `Surname` varchar(25) NOT NULL,
      `Birthday` date NOT NULL,
      `Adres` varchar(50) NOT NULL,
      `Telephone` varchar(25) NOT NULL,
      `login` varchar(16) DEFAULT NULL,
      PRIMARY KEY (`ID`),
      UNIQUE KEY `login` (`login`),
      CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`login`) REFERENCES `users` (`login`)  ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 0

Hammerite
Hammerite

Reputation: 22340

You've put the CONSTRAINT in the wrong CREATE TABLE statement. It goes on the referencing table, which is "users", because that's the table you want to automatically delete from when a row in the other table is deleted.

You want to put a nullable "ID" in the "users" table, which is unique and a foreign key to "employees".

Upvotes: 1

Related Questions