问题:

Is there a way to override mysql foreign key constraints in a php script?

I have a query passed to mysql from php, but it fails a foreign key constraint, is there any way to get around this without altering the db schema?

I'm just doing some testing, so I'll be removing the row when I'm done.

回答1:

mysql_query('SET foreign_key_checks = 0');
//do some stuff here
mysql_query('SET foreign_key_checks = 1');

回答2:

You can execute that MySQL query to disable foreign keys check:

SET FOREIGN_KEY_CHECKS=0;

Don't forget to enable it when you're done:

SET FOREIGN_KEY_CHECKS=1;

回答3:

Run the query: set FOREIGN_KEY_CHECKS=0;

mysql> insert into bar values(1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`foo_id`) ON UPDATE CASCADE)
mysql> set FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into bar values(1);
Query OK, 1 row affected (0.00 sec)

回答4:

I have something like this in my snippet collection:

SET @BACKUP_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; 
SET @@FOREIGN_KEY_CHECKS=0;

-- Do stuff here

SET @@FOREIGN_KEY_CHECKS=@BACKUP_FOREIGN_KEY_CHECKS;
SET @BACKUP_FOREIGN_KEY_CHECKS=NULL;