mysql - 如何在 phpMyAdmin 中设置表之间的关系

Question

我的问题是，当我创建一个表时，例如:table1 包含以下列:

• 客户编号
• 客户姓名
• 地址
• 状态

其中 customerId 是带有 AUTO_INCREMENT 的 PRIMARY KEY。

然后是 table2，例如列:

• purchaseId
• 客户编号
• 产品
• 费用

其中 PRIMARY KEY 是 purchaseId，外键是 table1 中的 customerId。

这应该意味着我已经使用customerId在table1和table2之间建立了关系。

这两个表最初都是空的，所以我写了这个 SQL 命令:

INSERT INTO table1 (CustomerName,Address,State) VALUES('value1','value2','value3')

这工作正常，但是当我尝试插入子表 (table2) 时，它告诉我:

ERROR a foreign key constraint

所以基本上我想做的是先插入父表，然后插入子表，这样 customerId 就会在 table2(子表)中显示为外部key 对应table1(父表)中的customerId。

我必须先在没有外键的情况下创建两个表，然后再尝试建立关系吗？它一直在说，只要存在关系，就会存在约束。

Answer 1

table2 外键约束意味着任何 table2 customerId 值必须作为 table1 中的 customerId 出现。您收到错误是因为您将 customerID 插入到 table2 中，而 table1 中没有。

由于 DBMS 通过自动递增生成表 1 customerID，如果您插入一行，则必须获取该值才能将使用该 customerID 的行插入表 2。

我猜你说“I already established a relationship between table1 and table2”的意思是“我声明了一个外键约束”。我猜你认为这意味着“在我插入 table1 之后，当我插入 table2 时，DBMS 将使用自动生成的键值作为外键值”。但这并不意味着。你必须自己做。外键约束只是意味着 DBMS 检查每个 table2 customerId 值是否显示为 table1 customerId 值。

当您插入到具有该键的外键的表中时，您可以而且必须使用任何先前插入的键值作为相应的值。

要取回 DBMS 生成的自动递增键值，请使用 LAST_INSERT_ID() :

INSERT INTO table1 (CustomerName,Address,State)
VALUES('value1','value2','value3');
INSERT INTO table2 (customerId,product,cost)
VALUES(LAST_INSERT_ID(),'valueA','valueB');

这就是它的用途。但是，如果您不使用它，就会出现问题。

首先，如果您不在序列化事务中，那么您必须使用 LAST_INSERT_ID()。因为在您的 table1 插入之后但在您的 table2 插入之前，其他人可能已经添加了行和/或删除了行，包括您的新行和/或更改了的行，包括您的新行。因此，您不能在 table1 的插入获得一些您知道已添加的 customerId 值后依赖查询 table1。

其次，假设您处于序列化事务中并且您不使用 LAST_INSERT_ID()。

如果 (CustomerName,Address,State)也是 table1 的 super 键，即它的值是唯一的，即在它的所有或部分列上声明了 SQL UNIQUE/KEY/PK，那么你可以使用它来查询关联的新 customerId:

set @customerId = (
    SELECT customerId
    FROM table1
    WHERE CustomerName = 'value1'
    AND Address = 'value2'
    AND State = 'value3');
INSERT INTO table2 (customerId,product,cost)
VALUES(@customerId,'valueA','valueB');

但是如果 (CustomerName,Address,State) 不是 table1 的 super 键，那么您不能这样做。因为与该子行重复的其他行可能在 table1 中。所以你可以得到多行。所以你不会知道哪个是最新的。相反，您必须在插入之前查询 table1，然后插入，然后找到新旧 customerId 集之间的差异:

CREATE TEMPORARY TABLE table1old (
    customerId (int) PRIMARY KEY
    );
INSERT INTO table1old
SELECT customerId FROM table1;

INSERT INTO table1 (CustomerName,Address,State)
VALUES('value1','value2','value3');

set @customerId = (
    SELECT customerId
    FROM table1
    WHERE CustomerName NOT IN table1old);
INSERT INTO table2 (customerId,product,cost)
VALUES(@customerId,'valueA','valueB');

只需使用 LAST_INSERT_ID()。

PS:有趣的是，给定表定义，理想情况下可以这样写:

INSERT INTO (
    SELECT CustomerName,Address,State,A,B
    FROM table1 JOIN table2
    USING (CustomerId))
VALUES('value1','value2','value3','valueA','valueB')

因为只有一对新的 table1 和 table2 值可以产生。 SQL 中有一些通过 View 进行的合法更新，但目前还没有涉及到 MySQL 中的多表