15:42:45 0x7fb734174700 Cannot drop table `sakila`.`actor`īecause it is referenced by `sakila`. RW-shared spins 0, rounds 757, OS waits 384 OS WAIT ARRAY INFO: reservation count 1607 Srv_master_thread log flush and writes: 85803 Srv_master_thread loops: 84 srv_active, 0 srv_shutdown, 85720 srv_idle Per second averages calculated from the last 46 seconds I want that when I delete a book, all the tags to be deleted as well. We can find this same information and the error cause displaying the state of the InnoDB storage engine through the “SHOW ENGINE INNODB STATUS” command: mysqld3-( ) > show engine innodb statusG I have a secondary table called tag with fields bookid and tag. This foreign key constraint let data being consistent over different tables and that’s also the reason why we could not drop the parent table. | film_actor | actor_id | fk_film_actor_actor | actor | actor_id | | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | > WHERE REFERENCED_TABLE_SCHEMA = 'sakila' > FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE In this example, the ‘actor’ table is referenced by the ‘film_actor’ one: mysqld3-( ) > SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
PHPMYADMIN FOREIGN KEY DELETE CASCADE UPDATE
Sometimes, when we must drop an InnoDB table in MySQL, we could encounter the following error due to foreign keys: mysqld3-( ) > drop table actor ĮRROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails Mysqld3-( ) > select * from film_actor where actor_id=300 Mysqld3-( ) > select * from actor where actor_id=300 Then this will cause an automatic update of the matching rows in the child table (‘film_actor’): mysqld3-( ) > select * from actor where actor_id=1 Mysqld3-( ) > update actor set actor_id=300 where actor_id=1 Mysqld3-( ) > select * from film_actor where actor_id=1
| actor_id | first_name | last_name | last_update | The “ON UPDATE CASCADE” clause means that if we update values in the parent table (‘actor’ in our example), mysqld3-( ) > select * from actor where actor_id=1 `actor_id` smallint(5) unsigned NOT NULL,ĬONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,ĬONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8Īnd a foreign key is defined on the child table by using the “FOREIGN KEY… REFERENCES” syntax: mysqld3-( ) > show create table film_actorGĬreate Table: CREATE TABLE `film_actor` ( `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, In our example, we have the following parent table in a MySQL 5.7.21 server: mysqld3-( ) > show create table actorG MySQL requires InnoDB storage engine to support foreign keys. As you know, foreign keys establish a sort of relationship between 2 tables.
0 kommentar(er)
