CODEWITHSUNDEEP
mysql
andrew
andrew

Reputation: 9583

Using foreign key constrains to prevent update of parent row

Is it possible to use foreign key constrains to prevent update of parent row when the index is in use by a child row?

Here is the schema of the parent table:

create table if not exists incident_options (
id int  not null auto_increment primary key,
idx int not null,
field int not null , 
value varchar(50));

ALTER TABLE `incident_options` ADD unique (`idx`, field);

And the child table:

create table if not exists incident_values (
optn int,
value varchar(1000));

ALTER TABLE `incident_values` ADD INDEX (`optn`);

Here is the constraint that I have tried:

ALTER TABLE `incident_values` 
ADD FOREIGN KEY (`optn`) REFERENCES `incident_options`(`id`) 
ON DELETE NO ACTION ON UPDATE RESTRICT;




Supposing I have the the following data:

incident_options
 __________________________
|id | idx  | field | value |    
| 1 |  0   |   1   | foo   |
| 2 |  1   |   1   | bar   |
 --------------------------

incident_values

 ______________
| optn | value |    
| 1    |  baz  |
 --------------



What I'm trying to achieve is to prevent 'foo' from being updated because the row is in use in incident_values

To update 'bar' would be fine

Here is the query I'm using to modify the row:

insert into incident_options (`idx`,`field`,`value`)
values ('0','1','test')
on duplicate key update
`idx` = values(`idx`),
`field` = values(`field`),
`value` = values(`value`)

At present 'foo' will be updated to 'test'

Upvotes: 0

Views: 552

Answers (2)

andrew
andrew

Reputation: 9583

With a bit of hackery I did actually achieve what I wanted.

By forcing an increment of 'id' when 'value' has changed, the update of 'id' will not be permitted if there is a relationship

on duplicate key update
idx = values(idx),
field = values(field),
id = if(
        values(`value`)=`value`,
        id,
        (SELECT Auto_increment FROM information_schema.tables WHERE table_name='incident_options')
        ),
values= values(value)

Though I'll see if there is any advice against using this solution before implementing it in my application

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16691

The problem is that you've set the constraint on the wrong table. You have put ON UPDATE RESTRICT on the incident_values table, but when you are doing the insert and update this is all happening on incident_options.

Unfortunately, you can't set this constraint inside the incident_options table because you may not always have a reference back to the incident_values table, so I do not think this is possible using a constraint. As Barmar mentioned in the comments, you may be able to implement a trigger in some way to preform verification before updating a field.

Upvotes: 1

Related Questions