MySQL Inserting into table with 2 foreign keys

Question

I'm working on a C# application that uses a MySQL Database. I'm trying to insert into one table with 2 foreign keys, but am getting an error:

Cannot add or update a child row: a foreign key constraint fails (thedatabase.tblC, CONSTRAINT tblC_ibfk_1 FOREIGN KEY (ID) REFERENCES tblA (ID) ON DELETE NO ACTION ON UPDATE CASCADE)

The table structure is

tblA - ID, col2, col3, col4...

tblB - ID, col2, col3, col4...

tblC - tblA.ID, tblB.ID, col3, col4, col5...

I've been looking over the forums, and for the life of me I can't find the syntax to make it work.

Here is what I'm using to connect. I've confirmed that the ID's to exist in the parent tables.

//Connect to the database
MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand cmd;
connection.Open(); 

//Insert new record into database
cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO tblC(tblA_ID, tblB_ID, col3, col4, col5) VALUES(@tblA_ID, @tblB_ID, @col3, @col4, @col5);";
cmd.Parameters.AddWithValue("@tblA_ID", tblA_ID);
cmd.Parameters.AddWithValue("@tblB_ID", tblB_ID);
cmd.Parameters.AddWithValue("@col3", col3);
cmd.Parameters.AddWithValue("@col4", col4);
cmd.Parameters.AddWithValue("@col5", col5);

cmd.ExecuteNonQuery();
connection.Close();

Try posting some code as well as the error message. Are you using Entity Framework? Is it a web app? — ste-fu, Apr 03 '15 at 22:29
Not a web app, just a regular C# application. The database is meant to be later shared with a local web app (the reason for mysql). — Beaker, Apr 03 '15 at 23:06

score 1 · Answer 1 · answered Apr 03 '15 at 22:30

Your syntax is fine but a foreign key error indicates a data issue.

The error is happening because the ID being referenced does not exist in tblA.

So if you are doing something like:

INSERT INTO tblC SET tblA_id = 100, tblB_id = 101

tblA MUST have a record with an ID = 100 and tblB MUST have a record with ID = 101 or a foreign key error will occur.

score 1 · Answer 2 · edited May 23 '17 at 12:14

The foreign records must be added first; use a transaction over all the inserts to ensure consistency.

This is because MySQL does not support deferred FK constraints, but then neither does SQL Server..

Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. ..

(Besides, for surrogate/auto-increment PKs, how can one access IDs that do not exist yet?)

Inserting such a relation can be illustrated with LAST_INSERT_ID and the following SQL:

 BEGIN TRANSACTION;

 DECLARE a_id INT DEFAULT 0,
         b_id INT DEFAULT 0;

 INSERT INTO tblA (..) VALUES (..)
 SET a_id = LAST_INSERT_ID();

 INSERT INTO tblA (..) VALUES (..)
 SET b_id = LAST_INSERT_ID();

 INSERT INTO tblC (..) VALUES (@a_id, @b_id, ..)

 COMMIT;

The exact same approach must be followed when doing the inserts from the client. Create the foreign records; get the IDs, use the IDs in the dependent record.

For a "generic" ADO.NET solution one can use the result of ExecuteScalar for the insert along with a trailing select (vary for database). If using MySqlCommand, from MySqlConnector, this is natively supported via LastInsertedId.

When using another Framework and/or ORM, follow the rules used therein for building object graphs and/or determining inserted IDs.

MySQL Inserting into table with 2 foreign keys

2 Answers2