MySql Define foreign key with multiple columns throws error

Question

我有两个表。查询如下。

CREATE TABLE `queue_service_settings` (
`app_id` int(11) NOT NULL,
`app_id_type` int(11) NOT NULL,
`channel_id` int(11) NOT NULL,
`setting_type` varchar(255) NOT NULL,
`setting_key` varchar(255) NOT NULL,
`setting_value` varchar(255) NOT NULL,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (app_id,app_id_type),
KEY `index_qss_app_id` (`app_id`) USING BTREE,
KEY `index_qss_setting_type` (`setting_type`) USING BTREE,
KEY `index_qss_setting_key` (`setting_key`) USING BTREE,
KEY `index_qss_channel_id` (`channel_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `queue_applications` (
`app_id` int(11) NOT NULL,
`app_id_type` int(11) NOT NULL,
`channel_id` int(11) NOT NULL,
`app_name` varchar(255) NOT NULL,
`callback_url` varchar(255) NOT NULL,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`app_id`,`app_id_type`,`channel_id`),
KEY `fk_qa_channel_id` (`channel_id`),
CONSTRAINT `fk_qa_app_id_app_id_type_channel_id` FOREIGN KEY (app_id,app_id_type,channel_id) REFERENCES queue_service_settings(app_id,app_id_type,channel_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我收到这个错误

无法添加外键约束创建表具有外键约束的'event_processer_test/queue_applications'失败。引用的表中没有索引列显示为第一列。

当我试图将这3列(app_id，app_id_type，channel_id(定义为主键时，它是可以的，但主键应该只是(app_id，app_id_type(

Answer 1

外键中的列必须与其他表中引用的主键的列匹配。在queue_service_settings的主键中有两列，所以在引用它的外键中应该有两列

CREATE TABLE `queue_applications` (
`app_id` int(11) NOT NULL,
`app_id_type` int(11) NOT NULL,
`channel_id` int(11) NOT NULL,
`app_name` varchar(255) NOT NULL,
`callback_url` varchar(255) NOT NULL,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`app_id`,`app_id_type`,`channel_id`),
KEY `fk_qa_channel_id` (`channel_id`),
CONSTRAINT `fk_qa_app_id_app_id_type_channel_id` 
FOREIGN KEY (app_id,app_id_type) 
REFERENCES queue_service_settings(app_id,app_id_type)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;