使用复合主键在Junction表上设置外键约束

I have a junction table that stores the relationship between tests and the questions in the tests, with testFK and questionFK set as a compound primary key to prevent duplicate entries.

我有一个联结表，用于存储测试与测试中的问题之间的关系，testFK和questionFK设置为​​复合主键以防止重复输入。

Junction Table:

连接表：

    +--------+------------+
    | testFK | questionFK |
    +--------+------------+
    |    7   |      1     |
    +--------+------------+
    |    7   |      2     |
    +--------+------------+
    |    7   |      3     |
    +--------+------------+
    |    8   |      4     |
    +--------+------------+
    |    8   |      5     |
    +--------+------------+
    |    8   |      6     |
    +--------+------------+

What I want to do:

我想做的事：

Upon deleting testID from the test table, I would like all the rows with testFK=8 from the Junction Table to be deleted as well.

从测试表中删除testID后，我希望删除连接表中testFK = 8的所有行。

The Problem:

问题：

phpmyadmin won't let me set testFK as a foreign key for testID from the test table.

phpmyadmin不允许我从测试表中将testFK设置为​​testID的外键。

What is the best way to handle this situation?

处理这种情况的最佳方法是什么？

Thanks for any input!

谢谢你的任何输入！

UPDATE: Create Code:

更新：创建代码：

CREATE TABLE IF NOT EXISTS `junc_test_question` (
  `testFK` int(11) NOT NULL,
  `questionFK` int(11) NOT NULL,
  `ordinal` int(3) NOT NULL DEFAULT '1',
  PRIMARY KEY (`testFK`,`questionFK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

1 个解决方案

#1

DROP TRIGGER remove_test_table
GO
CREATE TRIGGER remove_test_table
FOR DELETE
AS 
DELETE FROM test_table WHERE testFK IN (SELECT testFK FROM deleted);
GO