How to update FK linked to multiple table - Cascade on Update

68
January 13, 2019, at 03:00 AM

I have 3 tables which are linked to each other

  1. task
  2. client
  3. compliance

The relationship is as shown in diagram below

The compliance table has the foreign key as below

The task table has the foreign key as below

Issue: When I edit/update clientno in client table, I get

1452: Cannot add or update a child row: a foreign key constraint fails
(`task`, CONSTRAINT `task_ibfk_1` FOREIGN KEY (`officeid`, `clientid`) REFERENCES `client` (`officeid`, `clientno`) ON UPDATE CASCADE)

I expected that when clientno was changed in client table, the same will be updated in both complaince and task table.

I guess I am hitting a known limitation of InnoDB engine. Which goes not allow cascading updates to FK. If this is true, then what is the solution to updating the 3 tables with new clientno?

EDIT 1: As pointed out by @pankaj, how to overcome

If ON UPDATE CASCADE recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE operations. This is to prevent infinite loops resulting from cascaded updates.

Edit 2:

create table client
(
  officeid     char(6)                 not null,
  clientno     char(10)                not null,
  fname        varchar(40)             not null,
  primary key (officeid, clientno)
);
create index officeid_clientno
  on client (officeid, clientno);

create table compliance
(
  officeid    char(6)                   not null,
  id          smallint(5) unsigned      not null,
  clientid    char(10)                  not null,
  primary key (officeid, id),
  constraint compliance_ibfk_2
  foreign key (officeid, clientid) references client (officeid, clientno)
    on update cascade
    on delete cascade
);
create index officeid_clientid
  on compliance (officeid, clientid, id);

create table task
(
  officeid        char(6)                      not null,
  taskno          char(10)                     not null,
  clientid        char(10)                     not null,
  taskname        varchar(50)                  not null,
  complianceid    smallint(5) unsigned         null,
  primary key (officeid, taskno),
  constraint task_ibfk_1
  foreign key (officeid, clientid) references client (officeid, clientno)
    on update cascade,
  constraint task_ibfk_4
  foreign key (officeid, clientid, complianceid) references compliance (officeid, clientid, id)
    on update cascade
);
create index officeid_clientid_complianceid
  on task (officeid, clientid, complianceid);

FYI: I tried in mariadb 10.3 as well as mysql 8.0

Answer 1

The problem is related to the way relationships are declared.

First of all, as commented by @Nick, there is no need for a relation between task and client, as this is already covered by the relation to compliance. Commenting the declaration of this superfluous constraint is enough the make the error disappear, as you can see in this db fiddle.

create table task
(
  officeid        char(6)                      not null,
  ...
  primary key (officeid, taskno),
  -- constraint task_ibfk_1
   -- foreign key (officeid, clientid) references client (officeid, clientno)
   -- on update cascade,
  constraint task_ibfk_4
  foreign key (officeid, clientid, complianceid) references compliance (officeid,     clientid, id)
    on update cascade
); 

Another suggestion is to use an autoincremented primary key in all tables (you can use an UNIQUE index to enforce composite referential integrity rules). This is the most usual way to proceed with MySQL, with which handling relationships is pretty straighforward.

READ ALSO
How do I convert a JSON object into a MySQL row?

How do I convert a JSON object into a MySQL row?

I'd like to take a JSON string representing an object from a 3rd party API and insert it into a MySQL tableThe JSON object properties match the table fields 1-to-1

61
How to substantially decrease mySQL query processing time?

How to substantially decrease mySQL query processing time?

I have a trouble that my query in mySQL is being processing about 8-10 minutes before report finally appearsI have created few indexes and time has fallen till 2-3 minutes

30
How to get multiple rows/columns matched from another table in mysql

How to get multiple rows/columns matched from another table in mysql

I have a records table and a history table that adds timestamps as records flow through our systemThey look something like this

24
Trouble with creating a table on jsp page having a column with blob images from MySQL

Trouble with creating a table on jsp page having a column with blob images from MySQL

I am trying to create a table with a column of text description of an image and a second column with blob images from MySQL

43