它应该做的是：从客户表中删除一个客户。
借款人和客户表的SQL：
create table customer
(customer_name varchar(15) not null,
customer_street varchar(12) not null,
customer_city varchar(15) not null,
primary key(customer_name))ENGINE=InnoDB;
create table borrower
(customer_name varchar(15) not null,
loan_number varchar(15) not null,
primary key(customer_name, loan_number),
foreign key(customer_name) references customer(customer_name),
foreign key(loan_number) references loan(loan_number)
ON UPDATE CASCADE ON DELETE CASCADE)ENGINE=InnoDB;
我知道，由于引用完整性约束，我需要先删除带有外键的表。我怎样才能先放下带外键的桌子？错误
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`databaseName`.`borrower`, CONSTRAINT `borrower_ibfk_1` FOREIGN KEY (`customer_name`) REFERENCES `customer` (`customer_name`))' in /banking_update.php:18
Stack trace:
#0 /banking_update.php(18): PDOStatement->execute(Array)
#1 {main}
thrown in /banking_update.php on line 18
到目前为止的PHP代码
<!DOCTYPE html>
<html>
<body>
<div style = "width:30%; float:left">
</div>
<?php
include_once 'banking_db.php';
# form data
$customer_name=isset($_POST['customer_name']) ? $_POST['customer_name'] : '';
$customer_street=isset($_POST['customer_street']) ? $_POST['customer_street'] : '';
$customer_city=isset($_POST['customer_city']) ? $_POST['customer_city'] : '';
$query = "update customer set customer_name = :customer_name, customer_street = :customer_street, customer_city = :customer_city;";
$data = array( 'customer_name' => $customer_name, 'customer_street' => $customer_street, 'customer_city' => $customer_city);
$stmt = $conn->prepare($query);
if($stmt -> execute($data))
{
$rows_affected = $stmt->rowCount();
echo "<h2>".$rows_affected." row updated sucessfully!</h2>";
include 'banking_display.php';
display("SELECT * FROM customer;");
}
else
{
echo "update failed: (" . $conn->errno . ") " . $conn->error;
}
$conn->close();
?>
</body>
</html>
正如前面所说，您需要将ON UPDATE CASCADE ON DELETE CASCADE添加到外键中。
但是您的查询会用相同的信息更新所有客户，因此在您的查询中添加`WHERE子句
create table customer (customer_name varchar(15) not null, customer_street varchar(12) not null, customer_city varchar(15) not null, primary key(customer_name))ENGINE=InnoDB; create table borrower (customer_name varchar(15) not null, loan_number varchar(15) not null, primary key(customer_name, loan_number), foreign key(customer_name) references customer(customer_name) ON UPDATE CASCADE ON DELETE CASCADE)ENGINE=InnoDB;
INSERT INTO customer VALUES ('TEST','teststreet','Testcity')
INSERT INTO borrower VALUES ('TEST','loan1')
UPDATE customer SET customer_name = 'test1', customer_street = 'teststreet1' WHERE customer_name = 'TEST'
SELECT * FROM customer
customer_name | customerstreet | customercity：------------|：---------------|：------------test1|teststreet1|Testcity
db<gt；小提琴这里