如何解决此约束错误

Question

I have table1 with 3 columns: id varchar(20), name, mother_id varchar(20). mother_id is a foreign key referring to the id column in the same table.

Here is my table:

|id     |name    |mother_id|  
|dog1   |my_dog  | (null)    
|dog2   |my_dog2 | dog1    
|dog3   |my_dog3 | dog1

If I tried to update dog1 and change dog4 I have this error:

 Cannot delete or update a parent row: a foreign key constraint fails (`dogs`.`table1`, CONSTRAINT `FK_dogs1` FOREIGN KEY (`mother_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) 

How to solve this problem? I'm using wamp 2.0 h and Sqlyog enterprise.

Answer 1

You can not directly change a Primary key value which is referenced by a Foreign key value. but there is multi ways to change it one way is:

drop the Foreign key constraint and Primary key -> update the values (PK and FK) -> and then add the primary key and Foreign Key constraint again

alter table table1 drop foreign key thenameofFK;
alter table table1 drop primary key;

update table1
set id='dog4'
where id='dog1';

update table1 
set mother_id='dog4'
where mother_id='dog1';

alter table table1  add primary key(id);
alter table table1 
add constraint thenameofFK
foreign key(mother_id)
references table1(id);

also please remove PHP tag from question, I don't see any issue or content related to PHP.