因此,在插入新用户帐户时,我从Node控制台收到错误1452。下面是错误
{错误: ER_NO_REFERENCED_ROW_2:无法添加或更新子行:外键约束失败(
myeherlpertst2
.customers
,constraintcustomers_ibfk_1
外键(user_id
)引用users
(user_id
))
代码:'ER_NO_REFERENCED_ROW_2',错误号: 1452,sqlMessage:‘无法添加或更新子行:外键约束失败(myeherlpertst2
.customers
,CONSTRAINT customers_ibfk_1
foreign key (user_id
) REFERENCES users
(user_id
))',sqlState:'23000',索引: 0,
我非常确定我得到这个错误的原因是因为customers表试图引用一些不存在的东西,但我不明白为什么它没有引用它。在下面的节点代码中,我首先插入user表,它会自动递增user_id,并且不为null。在插入到customers表之前,我是否必须结束第一次插入的事务,然后启动一个新的事务?
connection.beginTransaction(function(error){
if(error){
console.log('Error Caught');
return res;
}
if (error) {throw error;
console.log('Error Caught');
return;}
connection.query('Insert into users( user_name, user_password, user_type) values (?,?,?)', [customer.body.userName, customer.body.password, userType=1], function (error, results, fields) {
if (error) {
console.log(error);
return;
}
connection.query('Insert into customers(cust_first_name, cust_last_name, cust_email, cust_city, cust_address, cust_zip_code, cust_state, cust_phone_num, cust_role, cust_website, cust_business_name) values (?,?,?,?,?,?,?,?,?,?,?)', [customer.body.firstName, customer.body.lastName, customer.body.email, customer.body.city, customer.body.address, customer.body.zipCode, customer.body.state, customer.body.phoneNumber, customer.body.custRole=1, customer.body.website, customer.body.businessName], function (error, results, fields) {
if (error) {
console.log(error);
}
});
res.end(JSON.stringify(results));
});
console.log('End Transaction')
});
发布于 2018-07-12 05:19:52
您需要在customers
表中正确设置user_id
列。MySQL's LAST_INSERT_ID() function是您需要的值的来源。下面是如何用SQL实现这一点。
Insert into users( user_name, user_password, user_type) values (?,?,?);
Insert into customers(user_id, cust_first_name, cust_last_name, cust_email,
cust_city, cust_address, cust_zip_code, cust_state,
cust_phone_num, cust_role, cust_website, cust_business_name)
values (LAST_INSERT_ID(),?,?,?,?,?,?,?,?,?,?,?);
请注意,插入到customers表中会将LAST_INSERT_ID()的值更改为该表中自动递增ID的值。因此,如果需要重用users表中的值,请使用三个查询而不是两个:
Insert into users( user_name, user_password, user_type) values (?,?,?);
SET @userid := LAST_INSERT_ID();
Insert into customers(user_id, cust_first_name, cust_last_name, cust_email,
cust_city, cust_address, cust_zip_code, cust_state,
cust_phone_num, cust_role, cust_website, cust_business_name)
values (@userid,?,?,?,?,?,?,?,?,?,?,?);
我将把查询放到您的node程序中。
发布于 2018-07-13 04:36:40
不需要另一个查询:插入的结果包含最后一个自动增量插入id
此信息可通过results.insertId获得。
因此,纠正应该是这样的:
connection.query(
"Insert into users( user_name, user_password, user_type) values (?,?,?)",
[customer.body.userName, customer.body.password, (userType = 1)],
function(error, results, fields) {
if (error) {
console.log(error);
return;
}
connection.query(
"Insert into customers(user_id, cust_first_name, cust_last_name, cust_email, cust_city, cust_address, cust_zip_code, cust_state, cust_phone_num, cust_role, cust_website, cust_business_name) values (?,?,?,?,?,?,?,?,?,?,?,?)",
[
results.insertId,
customer.body.firstName,
customer.body.lastName,
customer.body.email,
customer.body.city,
customer.body.address,
customer.body.zipCode,
customer.body.state,
customer.body.phoneNumber,
(customer.body.custRole = 1),
customer.body.website,
customer.body.businessName
],
function(error, results, fields) {
if (error) {
console.log(error);
}
}
);
res.end(JSON.stringify(results));
}
);
顺便说一句:参数userType=1可能是错误的
https://stackoverflow.com/questions/51293710
复制相似问题