所以我有3个表,如下所示:
表company
表address
表company_address
我试图通过从$_GET
获取company_id
并使用mysqli_insert_id
获取address_id
来将地址信息添加到特定的公司。但是当我提交的时候,它只会给我错误
无法添加或更新子行:外键约束失败(
chkdcrm
。company_address
,constraintcompany_address_company_id
foreign 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;
发布于 2018-06-23 02:46:23
我已经经历过很多次了..我能告诉你的唯一一件事是,你必须删除其他表中的所有引用,才能删除有约束的东西。
我有一种感觉,您正试图将table1中不存在的ID值插入到table2中。(如果工作繁忙,我建议在对表执行任何操作之前禁用外键检查。
你只需要简单地
SET FOREIGN_KEY_CHECKS=0
这将禁用与任何其他表的外键匹配。用完该表后,再次启用该表
SET FOREIGN_KEY_CHECKS=1
发布于 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
https://stackoverflow.com/questions/50992886
复制相似问题