由于外键约束而延迟插入 [英] Delayed insert due to foreign key constraints

问题描述

我试图运行一个查询：

$ p $ 插入
到`ProductState`（`ProductId`，`` ChangedOn`，`State`）
SELECT t.`ProductId`，t.`ProcessedOn`，\'Activated\'
FROM`tmpImport` t
LEFT JOIN`Product` p
ON t.`ProductId` = p.`Id`
WHERE p.`I``为NULL
ON DUPLICATE KEY UPDATE
`ChangedOn` = VALUES（`ChangedOn`）


（我不太确定查询是否正确，但似乎有效）我遇到了以下问题。在创建产品表中的条目之前，我正在运行此查询，并且由于条目尚不在产品表中，因此正在获取外键约束问题。

我的问题是，有没有办法运行这个查询，而是等到下一个查询（更新Product表）执行上面的查询的插入部分之前？另外需要注意的是，如果查询是在产品条目创建后运行的，它将不会再看到p。 Id 为空，因此失败之前必须先执行创建产品条目。

--->编辑< ---
我试图实现的概念如下：
对于初学者，我将一组数据导入临时表， Product 表是过去（或已经过去）通过集合添加的所有产品的列表来自临时表的数据。我需要的是一个单独的表，它提供了产品的状态变化，因为有时产品将变得不可用（不再在供应商提供的数据集中）。

ProductState表如下所示：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' ）NOT NULL，
`ChangedOn` DATE NOT NULL，
`State` ENUM（'Activated'，'Deactivated'）NULL，
PRIMARY KEY（`ProductId`，`ChangedOn`）
INDEX``fk_ProductState_Product`（`ProductId` ASC），
CONSTRAINT`fk_ProductState_Product`
FOREIGN KEY（`ProductId`）
REFERENCES`Product`（`Id`）
ON DELETE NO ACTION
ON UPDATE NO ACTION）
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

外键与产品表（ Product 。 Id ）



基本上我要完成的是：
1.任何时候，在供应商数据集中显示新产品（或以前停用的产品）时，该记录将作为已激活在ProductState表中创建。
2.任何时候，一个产品（被激活）不会显示在供应商数据集中，该记录在ProductState表中被创建为Deactivated。

ProductState表的目的是跟踪产品的激活和关闭状态。此外，ProductState与Product Table是多对一关系，产品的状态每天只会更改一次，因此我的PKEY将是ProductId和ChangedDate。

所以您应该做的伪代码：

1. 
   
2. 将它们与产品表中的现有列表进行比较


3. 如果找到新产品：3.1将产品插入到产品表中，它到ProductState表


4. 如果从供应商列表中缺失：4.1将其插入到ProductState表中
   

所有这些都应该在1笔交易中完成。请注意，除非您确实要删除与之关联的所有信息，否则不应删除产品表中的内容。也删除所有你已经存储的状态。

与其试图在1个查询中完成这一切 - 最好的办法是创建一个存储过程来完成工作就像上面的一步一步一样。我认为它过于复杂（或在这种情况下，可能是不可能的）做所有在一个查询中。

编辑：是这样的：

pre code $ CREATE PROCEDURE`some_procedure_name`（）
BEGIN

- 将tmpImport表细分为2个表：new和removed
SELECT * INTO _temp_new_products
FROM`tmpImport` t
LEFT JOIN`Product` p
ON t.`ProductId` = p.`Id`
WHERE p。 `Id` IS NULL

SELECT * INTO _temp_removed_products
FROM`Product` p
LEFT JOIN`tmpImport` t
ON t.`ProductId` = p.` Id`
WHERE t.`ProductId`为NULL

- 对于_temp_new_products中的每个条目：
- 1.插入产品表
- 2。插入到ProductState表中'activated'

- 对于_temp_removed_products中的每个条目：
- 1.插入到ProductState表中'deactivated'

- drop the临时表
DROP TABLE _temp_new_products
DROP TABLE _temp_removed_products
END


I am trying to run a query:

INSERT
  INTO `ProductState` (`ProductId`, `ChangedOn`, `State`)
SELECT t.`ProductId`, t.`ProcessedOn`, \'Activated\'
  FROM `tmpImport` t
  LEFT JOIN `Product` p
    ON t.`ProductId` = p.`Id`
 WHERE p.`Id` IS NULL
    ON DUPLICATE KEY UPDATE
       `ChangedOn` = VALUES(`ChangedOn`)

(I am not quite sure the query is correct, but it appears to be working), however I am running into the following issue. I am running this query before creating the entry into the 'Products' table and am getting a foreign key constraint problem due to the fact that the entry is not in the Products table yet.

My question is, is there a way to run this query, but wait until the next query (which updates the Product table) before performing the insert portion of the query above? Also to note, if the query is run after the Product entry is created it will no longer see the p.Id as being null and therefore failing so it has to be performed before the Product entry is created.

---> Edit <--- The concept I am trying to achieve is as follows: For starters I am importing a set of data into a temp table, the Product table is a list of all products that are (or have been in the past) added through the set of data from the temp table. What I need is a separate table that provides a state change to the product as sometimes the product will become unavailable (no longer in the data set provided by the vendor).

The ProductState table is as follows:

CREATE  TABLE IF NOT EXISTS `ProductState` (
  `ProductId` VARCHAR(32) NOT NULL ,
  `ChangedOn` DATE NOT NULL ,
  `State` ENUM('Activated','Deactivated') NULL ,
  PRIMARY KEY (`ProductId`, `ChangedOn`) ,
  INDEX `fk_ProductState_Product` (`ProductId` ASC) ,
  CONSTRAINT `fk_ProductState_Product`
    FOREIGN KEY (`ProductId` )
    REFERENCES `Product` (`Id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

The foreign key is an identifying relationship with the Product table (Product.Id)

Essentially what I am trying to accomplish is this: 1. Anytime a new product (or previously deactivated product) shows up in the vendor data set, the record is created in the ProductState table as 'Activated'. 2. Anytime a product (that is activated), does not show up in the vendor data set, the record is created as 'Deactivated' in the ProductState table.

The purpose of the ProductState table is to track activation and deactivation states of a product. Also the ProductState is a Multi-To-One relationship with the Product Table, and the state of the product will only change once daily, therefore my PKEY would be ProductId and ChangedDate.

解决方案

With foreign keys, you definitely need to have the data on the Product table first, before entering the state, think about it with this logic: "How can something that dont exist have a state" ?

So pseudocode of what you should do:

1. Read in the vendor's product list
2. Compare them to the existing list in your Product table
3. If new ones found: 3.1 Insert it to Product table, 3.2 Insert it to ProductState table
4. If missing from vendor's list: 4.1 Insert it to ProductState table

All these should be done in 1 transaction. Note that you should NOT delete things from Product table, unless you really want to delete every information associated with it, ie. also delete all the "states" that you have stored.

Rather than trying to do this all in 1 query - best bet is to create a stored procedure that does the work as step-by-step above. I think it gets overly complicated (or in this case, probably impossible) to do all in 1 query.

Edit: Something like this:

CREATE PROCEDURE `some_procedure_name` ()
BEGIN

-- Breakdown the tmpImport table to 2 tables: new and removed
SELECT * INTO _temp_new_products
FROM`tmpImport` t
LEFT JOIN `Product` p
ON t.`ProductId` = p.`Id`
WHERE p.`Id` IS NULL

SELECT * INTO _temp_removed_products
FROM `Product` p
LEFT JOIN `tmpImport` t 
ON t.`ProductId` = p.`Id`
WHERE t.`ProductId` IS NULL

-- For each entry in _temp_new_products:
-- 1. Insert into Product table
-- 2. Insert into ProductState table 'activated'

-- For each entry in _temp_removed_products:
-- 1. Insert into ProductState table 'deactivated'

-- drop the temporary tables
DROP TABLE _temp_new_products
DROP TABLE _temp_removed_products
END

这篇关于由于外键约束而延迟插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！