代码之家 › 专栏 › 技术社区 › Wahrenheit Sucher

错误1452 mysql和nodejs。为什么数据库不能引用我的表?

mariadb node.js mysql

Wahrenheit Sucher · 技术社区 · 8 年前

因此,在插入新用户帐户时,我从节点控制台收到错误1452。这是错误

错误:er_no_referenced_row_2:无法添加或更新子行:外键约束失败( myeherlpertst2 . customers ,约束 customers_ibfk_1 外键( user_id )参考文献 users ( 用户ID ))

代码:'er_no_referenced_row_2', 错误号:1452, sqlmessage:'无法添加或更新子行:外键约束失败( 髓鞘细胞st2 . 客户 ,约束 客户 外键( 用户ID )参考文献 用户 ( 用户ID ))', sqlstate:'23000', 索引:0,

我非常肯定我之所以会收到这个错误,是因为客户表试图引用一些不存在的东西,但我不理解为什么它不引用它。在下面的节点代码中,我首先插入用户表,该表自动增加用户ID,并且不为空。是否必须先结束第一次插入的事务,然后再开始新的事务,然后才能插入到客户表中?

    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')
  });

2 回复 | 直到 8 年前

O. Jones 8 年前

user_id customers MySQL's LAST_INSERT_ID() function

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(),?,?,?,?,?,?,?,?,?,?,?);

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,?,?,?,?,?,?,?,?,?,?,?);

Diego Dupin 8 年前

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));
      }
    );