Delayed insert due to foreign key constraints

Question

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.

Answer 1

I think you should:

• start a transaction
• do your insert into the Products table
• do your insert into the ProductState table
• commit the transaction

This will avoid any foreign key errors, but will also make sure your data is always accurate. You do not want to 'avoid' the foreign key constraint in any way, and InnoDB (which I'm sure you are using) never defers these constraints unless you turn them off completely.

Also no you cannot insert into multiple tables in one INSERT ... SELECT statement.

Answer 2

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