PHP:无法添加或更新子行:外键约束失败
PHP:无法添加或更新子行:外键约束失败
提问于 2018-06-23 01:24:45
所以我有3个表，如下所示：
表company
表address
表company_address
我试图通过从$_GET获取company_id并使用mysqli_insert_id获取address_id来将地址信息添加到特定的公司。但是当我提交的时候，它只会给我错误
无法添加或更新子行:外键约束失败(
chkdcrm。company_address，constraintcompany_address_company_idforeign key (company_id)在DELETE CASCADE上引用company(company_id) )
问题出在哪里？我想不出来...
php代码：
<?php
require_once '../conn.php';
$company_id = isset($_GET['company_id'])? $_GET['company_id'] : '';
//Defining variables and parameters...
$sql1 = "INSERT INTO address ... VALUES ...";
//Binding parameters...
$address_id = mysqli_insert_id($conn);
mysqli_stmt_close($stmt1);
$sql2 = "INSERT INTO company_address (company_id, address_id) VALUES
($company_id, $address_id)";
mysqli_query($conn, $sql2) or die(mysqli_error($conn));
mysqli_close($conn);}
?>表定义：
CREATE TABLE IF NOT EXISTS `chkdcrm`.`company_address` (
`address_id` INT(11) NOT NULL,
`company_id` INT(11) NOT NULL,
PRIMARY KEY (`address_id`, `company_id`),
CONSTRAINT `company_address_address_id`
FOREIGN KEY (`address_id`)
REFERENCES `chkdcrm`.`address` (`address_id`)
ON DELETE CASCADE,
CONSTRAINT `company_address_company_id`
FOREIGN KEY (`company_id`)
REFERENCES `chkdcrm`.`company` (`company_id`)
ON DELETE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `chkdcrm`.`address` (
`address_id` INT(11) NOT NULL AUTO_INCREMENT,
`type` ENUM('Invoice', 'Shipping', 'Site', 'Mailing') NOT NULL,
`street` VARCHAR(100) NOT NULL,
`city` VARCHAR(100) NOT NULL,
`state` VARCHAR(30) NOT NULL,
`zip` VARCHAR(10) NOT NULL,
`country` VARCHAR(40) NOT NULL,
PRIMARY KEY (`address_id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `chkdcrm`.`company` (
`company_id` INT(11) NOT NULL AUTO_INCREMENT,
`name_de` VARCHAR(150) NOT NULL ,
`website` VARCHAR(255) NULL ,
`parent_company` INT(11) NULL,
PRIMARY KEY (`company_id`),
CONSTRAINT `parent_company`
FOREIGN KEY (`parent_company`)
REFERENCES `chkdcrm`.`company` (`company_id`)
ON DELETE SET NULL)
ENGINE = InnoDB;回答 2
Stack Overflow用户
发布于 2018-06-23 02:46:23
我已经经历过很多次了..我能告诉你的唯一一件事是，你必须删除其他表中的所有引用，才能删除有约束的东西。
我有一种感觉，您正试图将table1中不存在的ID值插入到table2中。(如果工作繁忙，我建议在对表执行任何操作之前禁用外键检查。
你只需要简单地
SET FOREIGN_KEY_CHECKS=0这将禁用与任何其他表的外键匹配。用完该表后，再次启用该表
SET FOREIGN_KEY_CHECKS=1Stack Overflow用户
发布于 2018-06-23 02:48:59
需要检查上报的公司id在其上级表(公司)中是否存在。如果是这样，那么它应该是有效的。
$checkcomp = "SELECT 1 FROM company WHERE company_id = $company_id";
$checking = $conn->query($checkcomp);
if($checking->num_rows > 0){
//execute your insert
}
else{some error handling
}附注，我宁愿使用POST而不是GET
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50992886
复制相关文章
相似问题