CODEWITHSUNDEEP
phpmysqlforeign-keys
Sirko
Sirko

Reputation: 74076

Determine programatically which FOREIGN KEY constraint failed

So assume the following (simplified) setup:

CREATE TABLE Table1(
  `a` int PRIMARY KEY
) ENGINE=INNODB;

INSERT INTO Table1 (`a`) VALUES (1),(2),(3);

CREATE TABLE Table2(
  `a` int,
  FOREIGN KEY (`a`) REFERENCES Table1(`a`) ON DELETE CASCADE
) ENGINE=INNODB;

We have two tables, of which the second has a foreign key references the first.

If I execute something like

INSERT INTO `Table2` VALUES ( 1 );

everything is fine. But if I execute

INSERT INTO `Table2` VALUES ( 5 );

I get an error message like

#1452 - Cannot add or update a child row: a foreign key constraint fails (table2, CONSTRAINT table2_ibfk_1 FOREIGN KEY (a) REFERENCES table1 (a) ON DELETE CASCADE)

which is correct.

As long as I have just one foreign key, it is no problem to see, that this failed. But further assuming I have more than one foreign key constraint:

How can I determine programmatically in PHP, which of them failed?

The only way I can think of, is parsing the error string and trying to extract the respective columns, but this is highly unstable, if MySQL decides to change the syntax of its error messages.

Some background: There is an underlying datamodel like a star-schema, which is just represented in the database. I do not want to check all constraints in PHP as this would either require to drop a lot of data from the database (the list of possible values for the foreign keys is pretty long) or fire a lot of queries to see, if the values exist in the respective dimension tables. Both doesn't seem a good approach. Especially since MySQL is checking those constraints anyhow.

Upvotes: 0

Views: 231

Answers (2)

Rahul
Rahul

Reputation: 77876

Your sole concern here is to find out whether the INSERT will fail or not. Not sure how efficient you think it would be but you can define a BEFORE INSERT trigger on your table and have the trigger emit a specifc warning message or log that message in a error log table. Something like (a rough idea)

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Table2`
FOR EACH ROW
BEGIN
DECLARE @row_count INT;
SELECT COUNT(*) INTO @row_count FROM Table1 WHERE `a` = NEW.a;
    IF @row_count <= 0 THEN
      INSERT INTO Error_Log(`detail`, `value`)
      VALUES('Insert failed for value ', NEW.a);
    END IF;
END$$   
DELIMITER ;

Then, if at all for some value(s) of a insert operation fails then you are sure that the same information will be available in Error_Log table; which can directly fetch from your application code (PHP).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

The easiest way is to name your constraints:

CREATE TABLE Table2(
  `a` int,
  CONSTRAINT FK_table2_a FOREIGN KEY (`a`) REFERENCES Table1(`a`) ON DELETE CASCADE
) ENGINE=INNODB;

Then the error message will say something intelligible.

Upvotes: 3

Related Questions