CODEWITHSUNDEEP
phpmysqltestingforeign-keys
Parris Varney
Parris Varney

Reputation: 11478

Ignore MySQL foreign key constraints in PHP

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.

Upvotes: 14

Views: 20723

Answers (4)

TomaszSobczak
TomaszSobczak

Reputation: 2926

$pdo->query('SET foreign_key_checks = 0');
//do some stuff here
$pdo->query('SET foreign_key_checks = 1');

Upvotes: 24

Álvaro González
Álvaro González

Reputation: 146460

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;

Upvotes: 2

intgr
intgr

Reputation: 20466

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)

Upvotes: 1

netcoder
netcoder

Reputation: 67705

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;

Upvotes: 7

Related Questions