如何使用PHP检查删除限制上的外键约束 [英] how can I check foreign key constrain on delete restrict using PHP
问题描述
我有两个表产品和购买.我在带有 purchase.purchase_id
列的 product.purchase_id
列中设置了一个删除限制
FK.然后,如果我尝试用FK删除product.product_id,它会显示错误,就像
I have two tables Product and purchase.
I set a on delete restrict
FK in product.purchase_id
column with purchase.purchase_id
column. Then if I try to delete product.product_id with FK, It shows error just like
A Database Error Occurred
Error Number: 1451
Cannot delete or update a parent row: a foreign key constraint fails (`another_bata`.`product_purchase_item`, CONSTRAINT `FK_product_purchase_item_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON UPDATE CASCADE)
delete from product where product_id='158'
Filename: C:\xampp\htdocs\rima_shoe\system\database\DB_driver.php
Line Number: 330
但是我需要一个用户友好的错误,假设您无法删除此产品列表,Beause您已经有此产品了
所以我需要在php中检查此FK,我该怎么做?
So I need to check this FK in php , How can I do this?
我的第二个问题..在某些特殊情况下,是否可以删除删除限制上已经存在的'product.product_id'?假设
My second question.. Is it possible to delete a 'product.product_id' which already exist on delete restrict
FK in some special case? suppose
DELETE p,q FROM product AS p INNER JOIN purchase AS q
WHERE p.purchase_id=q.purchase_id AND q.purchase_quan=0
推荐答案
使用例外:
try {
$db->query(...);
} catch (Exception $e) {
echo "an error occured during query";
}
您也可以创建自己的例外来专门处理FK错误:
You can also make your own Exceptions to speciallize for FK errors:
class ForeignKeyException extends Exception {
public function __construct($msg = 0, $code = 0) {
parent::__construct($msg, $code);
}
}
发生FK错误时,您抛出新的ForeignKeyException('FK failed');
try {
$db->query(...);
} catch (ForeignKeyException $e) {
echo "FK error";
} catch (Exception $e) {
echo "general error";
}
更新:
mysqli已经有例外:
mysqli has exceptions already:
} catch (mysqli_sql_exception $e) {
if ($e->getCode() == 'CODE FOR FK') {
//...
}
}
PHP mysqli异常: http://php.net/manual/en/class.mysqli-sql-exception.php
PHP mysqli exception: http://php.net/manual/en/class.mysqli-sql-exception.php
请参见MYSQLI错误代码: http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html
See MYSQLI error codes: http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html
另一个有用的问题:在PHP中处理外键异常
第二个问题的更新:
通过
SET foreign_key_checks = 0;
<your delete query here>
SET foreign_key_checks = 1;
这篇关于如何使用PHP检查删除限制上的外键约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!