CODEWITHSUNDEEP
mysqlspringmodel-view-controllerjdbctemplate
NO_name
NO_name

Reputation: 3

Update/Delete in Spring mvc with JdbcTemplate with mysql database?

I'm performing CRUD in Spring MVC on two tables.My two table employeedetail [master table] and login[child table] with employeeid as foreign key. When i'm running my Query

Query 1. 

DELETE e.* ,l.* FROM  employeedetail e JOIN login l ON l.employeeid=e.employeeid WHERE l.employeeid=10;

Error:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (springmvcmaven.login, CONSTRAINT employee_login_id_fk FOREIGN KEY (employeeid) REFERENCES employeedetail (employeeid) ON DELETE NO ACTION ON UPDATE NO ACTION)

Query 2:

 DELETE e.* ,l.* FROM  employeedetail e JOIN login l ON l.employeeid=e.employeeid WHERE employeeid=10;

ERROR :

Error Code: 1052. Column 'employeeid' in where clause is ambiguous

can any help me with this.

Upvotes: 0

Views: 1995

Answers (1)

notionquest
notionquest

Reputation: 39196

Solution 1:-

In the foreign key constraints, you can declare it as ON DELETE CASCADE. So that when the record in master table is deleted, all the records in the child table will be deleted automatically.

Refer delete on cascade

Solution 2:-

Delete the child table records i.e. login table first

Delete the master table record i.e. employeedetail table

DELETE FROM  login l WHERE l.employeeid=10;
DELETE FROM  employeedetail e WHERE e.employeeid=10;

Upvotes: 0

Related Questions