Trying to delete records in TestStand MySQL DataBase gives a "foreign key constraint fails" -error

[Roger_C]

Hi,

Using the standard TestStand 2010 database schema we filled up a MySQL database. The database contains some test runs I want to remove to clean up the database. I'm trying to delete these "test records" using the SQL DELETE statement. For example: I'm deleting from the UUT_RESULT table using the SQL command: DELETE from UUT_RESULT where UUT_SERIAL_NUMBER = 'TEST123' and BATCH_SERIAL_NUMBER = 'test';

This gives the following error-message: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`teststandtestresults`.`step_result`, CONSTRAINT `UUT_RESULT_FK` FOREIGN KEY (`UUT_RESULT`) REFERENCES `uut_result` (`ID`))

Diving into this "foreign key constraint"-error gives the logical answer that it's caused by the constrains set during the CREATE TABLE statements in the default TestStand MySQL Create dataBase script.It's a good thing ... It keeps the data in the database consistent and prevents for "ghost-data".

The strange thing is there is no information on the NI website or no questions about this topic (deleting Teststand DB records) on the forum. I know I can delete records by making a Query searching for the elements in the deepest child tables and from there start deleting the "tree" until I reach the "parent" UUT_RESULT-table. But I can't imagine that I'm the first one who's trying this. Did I overlook something? Maybe somebody has a (My-)SQL script to start from?

MySQL documentation also mentions an ON DELETE CASCADE option to add to the FOREIGN KEY Constraints using the ALTER TABLE statement.(innodb-foreign-key-constraints)

When deleting the row from the parent table it automatically deletes the matching rows in the child table. Somebody has experience with adding this option to the FOREIGN KEY Constraints of the standard TestStand schema? Are there any pitfalls ?

Thanks,

Roger

[Hossein128]

Hello,

I'm not familier with MySQL and teststand but I looked for you to find something which will be useful. I found some info that you can look at them in the link below.

http://msdn.microsoft.com/en-us/library/ms233823(v=vs.80).aspx

I hope it would help you.

Hear from you.

Regards,

[Roger_C · Accepted solution]

Hi Hossein,

Thanks for the reply and the suggestion. Good thing you remind me of updating this thread so other people can make use of my experience.

Because I use InnoDB I solved this problem by adding the ON DELETE CASCADE option as CONSTRAINT on the foreign Key UUT_RESULT_FK in the definitions of the `step_result` table as follows:

CONSTRAINT `UUT_RESULT_FK` FOREIGN KEY (`UUT_RESULT`) REFERENCES `uut_result` (`ID`) ON DELETE CASCADE

So only adding ON DELETE CASCADE after the standard in the TestStand schema defined line:

CONSTRAINT `UUT_RESULT_FK` FOREIGN KEY (`UUT_RESULT`) REFERENCES `uut_result` (`ID`)

Do the same for all the foreign keys in the other tables. For example for the step_seqcall -table change:

CONSTRAINT `STEP_SEQCALL_FK` FOREIGN KEY (`STEP_RESULT`) REFERENCES `step_result` (`ID`)

into:

CONSTRAINT `STEP_SEQCALL_FK` FOREIGN KEY (`STEP_RESULT`) REFERENCES `step_result` (`ID`) ON DELETE CASCADE

Do this for all the "Foreign Key Constraints" (all the tables accept the UUT-RESULT table have one) and it works!

Now if you delete a record in the UUT_RESULT table the MySQL engine (better: The InnoDB engine) deletes the records who are linked.

For MySQL it's easy done by using the MySQL workbench. Just rightclick on a tables in overview on the left side (Obejct browser)  and select ALTER table in the right click menu. Now in the Tab "Foreign Key" select the To edit "foreign key"on the left side and than on the right select Foreign Key option On Delete: CASCADE and press apply.

Thanks

-Roger