CODEWITHSUNDEEP
mysqlforeign-keyssql-delete
S R Sharma
S R Sharma

Reputation: 31

Delete records from parent table and child table in mysql

I want to delete record from table with foreign key. Table structure is:

CREATE TABLE `employees` (
`employeeNumber` int(11) NOT NULL,
`lastName` varchar(50) NOT NULL,
`firstName` varchar(50) NOT NULL,
`extension` varchar(10) NOT NULL,
`email` varchar(100) NOT NULL,
`officeCode` varchar(10) NOT NULL,
`reportsTo` int(11) DEFAULT NULL,
`jobTitle` varchar(50) NOT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `reportsTo` (`reportsTo`),
KEY `officeCode` (`officeCode`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES   `employees` (`employeeNumber`),
CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Second table:

Create Table">CREATE TABLE `offices` (
`officeCode` varchar(10) NOT NULL,
`city` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`postalCode` varchar(15) NOT NULL,
`territory` varchar(10) NOT NULL,
PRIMARY KEY (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I'm facing this error:

delete from offices where officeCode=7 Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`classicmodels`.`employees`, CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)) 0.093 sec

Upvotes: 1

Views: 3301

Answers (2)

Barmar
Barmar

Reputation: 780724

Since employees.officeCode is declared as a foreign key to offices.officeCode, all office codes that are used in the employees table have to exist in the offices table. You can't delete an office if there are any employees in that office.

You either have to delete those employees first, or you can tell MySQL to do this automatically by adding the ON DELETE CASCADE option to the FOREIGN KEY constraint.

If you allow employees.officeCode to be NULL, you could also use ON DELETE SET NULL to leave the employees there, but set their officeCode to NULL.

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

You have one option to delete child table entry first then after delete to master table. like this

delete from employees where officeCode=7
delete from offices where officeCode=7

Upvotes: 1

Related Questions