CODEWITHSUNDEEP
mysqlconstraints
Rodrigo
Rodrigo

Reputation: 231

Update values with a foreign key contraint

I'm a bit new to SQL, so I'm not sure if I'm doing the right thing. I have two tables which are built this way:

CREATE TABLE `TableA` (
  `id_A` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id_A`),
  UNIQUE KEY `name_UNIQUE` (`name`)
);

CREATE TABLE `TableB` (
  `id_B` int(11) NOT NULL,
  `id_A` int(11) NOT NULL,
  PRIMARY KEY  (`id_B`),
  KEY `id_A` (`id_A`),
  CONSTRAINT `fk_id_A` FOREIGN KEY (`id_A`) REFERENCES `TableA` (`id_A`),
);

So, these tables are linked by field id_A and the constraint shown in TableB. Now, because some external reasons, I must update some values of field id_A in TableA and of course I have to update the associated rows in TableB.

My first attempt was directly use an UPDATE query, but I got and error saying the constraint doesn't allow the change, since the id_A was being used by TableB (doh!):

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails ('myserver/TableB', CONSTRAINT 'k_id_A' FOREIGN KEY ('id_A') REFERENCES 'TableA' ('id_A'))

So my first thought was to disable the constraint, modify the table and set the constraint up again. However, this is meant to be part of a tool designed to edit somethings in the database and I would like to avoid disabling (or dropping) the constraint, since somebody might close the tool in the middle of the update or something and leave the table without it.

I've been trying using a transaction holding the queries I need, instead of just doing several queries, but I'm still getting that previous error. So far my ideas are:

  1. Drop the constraint, update and set the constraint again.
  2. Duplicate everything in TableA using the new values, update TableB to use the new values in TableA and the delete the previous entries in TableA.
  3. Fetch everything, drop everything in db, modify the local data and then put everything back into the database.

Is there any better way to do this? I'm using MySQL 5.0.27 and to test my queries (and transactions) I'm using MySQL Workbench 6.2.

Thanks everybody.

Upvotes: 1

Views: 57

Answers (1)

Alessandro Lai
Alessandro Lai

Reputation: 2274

First of all, editing the ID key of a row is generally a bad idea, but I will not question this.

You should use the CASCADE behaviour on your constraint, altering your current table. 

[...] CONSTRAINT `fk_id_A` FOREIGN KEY (`id_A`) REFERENCES `TableA` (`id_A`) ON UPDATE CASCADE

[edit] More about this topic in this question.

Upvotes: 1

Related Questions