How to update a foreign key column?

Question

I have below tables:

create table TABLE1 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, deptId int(11) NOT NULL, DeptName varchar(32) NOT NULL, PRIMARY KEY (id), KEY Dept (deptId, DeptName))

create table TABLE2 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, empId int(11) NOT NULL, DeptName varchar(32) NOT NULL, PRIMARY KEY (id), KEY DeptName (DeptName), CONSTRAINT T2FK FOREIGN KEY (DeptName) REFERENCES TABLE1 (DeptName))

TABLE1 has a MUL key defined with both dept id and dept name. TABLE2 has a Foreign key which references only Dept name from TABLE1

The DTO for TABLE2 gets created like below:

@org.hibernate.annotations.NotFound(action = org.hibernate.annotations.NotFoundAction.IGNORE)
   @javax.persistence.ManyToOne(targetEntity = org.amru.persistence.dto.TABLE1DTOImpl.class, fetch=javax.persistence.FetchType.EAGER)
   @javax.persistence.JoinColumn(name = "deptName")
   public org.amru.persistence.dto.TABLE1DTO getTABLE1() {
      return TABLE1;
   }

When I try to insert a row in TABLE2, it fails with foreign key constraint violation exception.

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`amru`.`TABLE2`, CONSTRAINT `T2FK` FOREIGN KEY (`DeptName`) REFERENCES `TABLE1` (`DeptName`))

I also see a EntityExistsException when I debug

What is possibly wrong? Is it recommended to refer a part of MUL key as foreign key in another table?

I am using jpa, hibernate, jboss, ejb and mysql

Can you share the raw insert statement here? My guess is that you are referring to a `DeptName` in table 1 which does not exist. By the way, what is `MUL`? Do you mean `NULL`? — Tim Biegeleisen, Jan 26 '18 at 06:22
not sure what are you trying to achieve by creating so many keys and foreign keys incorrectly — Ravi, Jan 26 '18 at 06:22
@TimBiegeleisen - you can read about MUL keys here https://stackoverflow.com/questions/5317889/sql-keys-mul-vs-pri-vs-uni — EmeraldTablet, Jan 26 '18 at 06:26
TABLE1 has only one row and TABLE2 has no rows at all. Insert happens using entity manager. — EmeraldTablet, Jan 26 '18 at 06:29
@Ravi, this is just an example from my otherwise real big DB design. And please mention clearly what you found as incorrect. — EmeraldTablet, Jan 26 '18 at 06:31
@EmeraldTablet for example why do you have composite key on `deptId, DeptName` when you already have primary key on `id` ? Then you are using `DeptName` for reference instead of `id` column from `dept` table? — Ravi, Jan 26 '18 at 06:37
@Ravi, because this accentutaes and best describes the entity when it is looked up. I have MUL key using both deptId and DeptName because, same deptId may have different department name based on regions. Hence MUL key. Referring DeptName from TABLE2 is because this table might be populated regionally and hence deptName best suits to look it up. Ok, I mentioned earlier this is rather complicated legacy design. Both TABLE1 and TABLE2 has lot more columns in it. For simplicity, I given a bare minimum example. — EmeraldTablet, Jan 26 '18 at 06:54
@EmeraldTablet If you got the solution, then share with us or else accept helpful answer. — Ravi, Jan 29 '18 at 16:42

score 0 · Answer 1 · answered Jan 26 '18 at 07:02

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: 
Cannot add or update a child row: a foreign key constraint fails

What is possibly wrong?

This is very clear that, DeptName is foreign key in TABLE2, which is referring from TABLE1. So, you are not allowed to change the value in parent table as the data is being referenced in other table.

If you are supposed to do this, then you need to alter your table to apply cascade changes to child table as well for your Foreign Key

FOREIGN KEY (DeptName) REFERENCES TABLE1 (DeptName) ON DELETE CASCADE ON UPDATE CASCADE

How to update a foreign key column?

1 Answers1