Блог

У меня возникает вопрос, когда я создаю таблицу, например: table1 со следующими столбцами:

• customerId
• CustomerName
• Address
• State

Где customerId находится PRIMARY KEY с. AUTO_INCREMENT

А затем table2 , например, со столбцами:

• purchaseId
• customerId
• product
• cost

Откуда PRIMARY KEY берется is purchaseId и внешний ключ customerId table1 .

Это должно означать, что я уже установил связь между table1 и table2 с помощью customerId .

Обе эти таблицы изначально пустые, поэтому я написал эту команду SQL:

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

Это работает нормально, но когда я пытаюсь вставить в дочернюю таблицу ( table2 ), она сообщает мне:

ОШИБКА ограничения внешнего ключа

Итак, в основном, что я хочу сделать, это вставить в родительскую таблицу, а затем в дочернюю таблицу, чтобы customerId она отображалась в table2 (дочерней таблице) как внешний ключ и соответствовала customerId in table1 (родительской таблице).

ДОЛЖЕН ЛИ Я СНАЧАЛА СОЗДАТЬ ДВЕ ТАБЛИЦЫ БЕЗ ВНЕШНЕГО КЛЮЧА, А ЗАТЕМ ПОПЫТАТЬСЯ УСТАНОВИТЬ СВЯЗЬ. Он продолжает говорить, что существует ограничение, пока существует связь.

Ограничение внешнего ключа table2 означает, что любое значение CustomerID table2 должно отображаться как CustomerID в table1. Вы получаете сообщение об ошибке, потому что вы вставляете CustomerID в table2, который не отображается в table1.

Поскольку СУБД генерирует идентификаторы клиентов table1 с помощью автоматического увеличения, если вы вставляете строку, вам нужно получить это значение, чтобы вставить строку с использованием этого идентификатора пользователя в table2.

Я предполагаю, что вы говорите «Я уже установил связь между таблицами1 и таблицами2», что означает «я объявил ограничение внешнего ключа». И я думаю, вы думаете, что это означает «после того, как я вставлю в table1, СУБД будет использовать автоматически сгенерированное значение ключа в качестве значения внешнего ключа при вставке в table2». Но это не значит, что. Вы должны сделать это самостоятельно. Ограничение внешнего ключа просто означает, что СУБД проверяет, что каждое значение идентификатора клиента table2 отображается как значение идентификатора клиента table1.

Вы можете и должны использовать любое ранее вставленное значение ключа в качестве соответствующего значения при вставке в таблицу с внешним ключом к этому ключу.

Чтобы вернуть автоматически увеличенное значение ключа, сгенерированное СУБД, используйте 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() . Потому что после вставки вашей таблицы1, но до вставки вашей таблицы2 другие могли добавлять строки и / или удалять строки, включая вашу новую строку и / или измененные строки, включая вашу новую строку. Таким образом, вы не можете полагаться на запрос table1 после того, как его вставка получит некоторое значение CustomerID, которое, как вы знаете, вы добавили.

Во-вторых, предположим, что вы находитесь в сериализованной транзакции и не используете LAST_INSERT_ID() .

Если (CustomerName, Address, State) также является суперключем table1, т.Е. Его значения уникальны, т.Е. 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, вы не можете этого сделать. Потому что другие строки, которые являются дубликатами для этого подстрока, могут быть в table1. Таким образом, вы можете получить несколько строк обратно. Таким образом, вы не знаете, какая из них самая новая. Вместо этого вам нужно запросить table1 перед вставкой, затем вставить, а затем найти разницу между старым и новым наборами идентификаторов клиентов:

 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 amp; table2. В SQL есть некоторые юридические обновления через views, хотя в настоящее время ни одно из них не включает несколько таблиц в MySQL