如何在phpMyAdmin中设置表之间的关系 [英] How to setup relationship between tables In phpMyAdmin

问题描述

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

The question I have is, when I create a table for example: table1 with the following columns:

• customerId
• CustomerName
• Address
• State

• customerId
• CustomerName
• Address
• State

其中customerId是带有AUTO_INCREMENT的PRIMARY KEY.

然后是table2例如带有列:

• purchaseId
• customerId
• product
• cost

• purchaseId
• customerId
• product
• cost

其中PRIMARY KEY是purchaseId，外键是customerId中的customerId.

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

It should mean that I already established a relationship between table1 and table2 using the customerId.

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

Both these tables are initially empty so I wrote this SQL command:

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

这很好，但是当我尝试插入子表(table2)时，它告诉我:

This works fine, but when I try to insert into the child table (table2) it tells me:

错误外键约束

ERROR a foreign key constraint

所以基本上我想做的是先插入父表，然后插入子表，以使customerId作为外键显示在table2(子表)中，并与table1中的customerId相对应>(父表).

So basically what I want to do is insert into the parent table then into the child table so that the customerId shows up in table2 (child Table) as a foreign key and corresponds with customerId in table1 (parent Table).

我必须先创建两个表，而无需使用外键，然后再尝试建立关系.它一直说只要关系存在就存在约束.

DO I HAVE TO CREATE THE TWO TABLES FIRST WITHOUT THE FOREIGN KEY THEN TRY TO ESTABLISH A RELATIONSHIP. It Keeps saying there is a constraint as long as the relationship was there.

推荐答案

table2外键约束意味着任何table2 customerId值都必须在table1中显示为customerId.之所以会出现错误，是因为您正在将未在table1中出现的customerID插入到table2中.

The table2 foreign key constraint means that any table2 customerId value must appear as a customerId in table1. You are getting the error because you are inserting a customerID into table2 that doesn't appear in table1.

由于DBMS通过自动递增生成table1客户ID，所以如果您插入一行，则必须获取该值才能将使用该customerID的行插入到table2中.

Since the DBMS is generating table1 customerIDs by auto increment, if you insert a row you have to get that value in order to insert a row using that customerID into table2.

我猜您说我已经在table1和table2之间建立关系"的意思是我声明了外键约束".我猜您认为这意味着插入表1后，DBMS将在插入表2时将自动生成的键值用作外键值".但这并不意味着那样.你必须自己做.外键约束仅表示DBMS检查每个table2 customerId值是否都显示为table1 customerId值.

I guess you say "I already established a relationship between table1 and table2" to mean "I declared a foreign key constraint". And I guess you think that means "after I insert into table1 the DBMS will use the auto-generated key value as the foreign key value when I insert into table2". But it doesn't mean that. You have to do that yourself. The foreign key constraint just means that the DBMS checks that every table2 customerId value appears as a table1 customerId value.

当您插入带有外键的表时，您可以并且必须使用以前插入的任何键值作为相应值.

You can and must use any previously inserted key value as the corresponding value when you insert into a table with a foreign key to that key.

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

To get back the auto incremented key value generated by the DBMS use 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');

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

This is what it is for. But here are the problems if you don't use it.

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

First, if you are not in a serialized transaction then you must use LAST_INSERT_ID(). Because after your table1 insert but before your table2 insert others could have added rows and/or deleted rows including your new row and/or changed rows including your new row. So you cannot rely on querying table1 after its insert get some customerId value that you know you added.

第二，假设您正在进行序列化事务，并且不使用LAST_INSERT_ID().

Second, suppose you are in a serialized transaction and you don't use LAST_INSERT_ID().

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

If (CustomerName,Address,State) is also a superkey of table1, ie its values are unique, ie SQL UNIQUE/KEY/PK is declared on all or some of its columns, then you can use it to query for the associated new 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的超键，那么您将无法执行此操作.因为该子行的其他重复行可能在table1中.这样您可以返回多行.因此，您将不知道哪个是最新的.取而代之的是，您必须在插入之前查询table1，然后插入，然后找到新旧客户ID集之间的区别:

But if (CustomerName,Address,State) is not a superkey of table1 then you cannot do this. Because other rows that are duplicates for that subrow could be in table1. So you could get multiple rows back. So you would not know which is the newest one. Instead you have to query table1 before the insert, then insert, then find the difference between the old and new sets of customerIds:

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().

Just use LAST_INSERT_ID().

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

PS: Interestingly, given the table definitions, ideally one could write:

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

因为只有一对新表1&可能产生的table2值.通过SQL中的视图进行了一些法律更新，尽管目前没有涉及MySQL中的多个表

since there is just one pair of new table1 & table2 values that can result. There are some legal updates through views in SQL, although none involving multiple tables in MySQL currently

这篇关于如何在phpMyAdmin中设置表之间的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！