如何为具有三个主表的同一个子表添加三个外键?
这里,我的数据库中有四个名为 test_center
的表。它们是:
test_user
--> PK =u_id
test_metadata
--> PK =test_id
student_detail
--> PK =Student_id
test_records
--> PK =test_record_id
(子表)
表4是子表,1、2、3是主表。
我正在尝试让 test_records
表具有来自每个主表的三个外键,但它显示一些错误,如下所示。 (我使用MySQL Workbench 5.0和MySQL服务器)
错误1005:无法创建表“test_center.#sql-aa4_12”(错误号:121)
SQL 语句:
ALTER TABLE `test_center`.`test_records`
ADD CONSTRAINT `Student_id`
FOREIGN KEY (`Student_id` )
REFERENCES `test_center`.`student_detail` (`Student_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
ADD CONSTRAINT `test_id`
FOREIGN KEY (`test_id` )
REFERENCES `test_center`.`test_metadata` (`test_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
ADD CONSTRAINT `u_id`
FOREIGN KEY (`u_id` )
REFERENCES `test_center`.`test_user` (`u_id` )
ON DELETE CASCADE
ON UPDATE CASCADE
错误:运行故障回复脚本时出错。详细信息如下。 错误 1046:未选择数据库
SQL 语句:
CREATE TABLE `test_records` (
`test_record_id` int(11) NOT NULL AUTO_INCREMENT,
`test_name` varchar(45) NOT NULL,
`Result` float NOT NULL,
`status` varchar(45) NOT NULL,
`Student_id` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
`test_id` int(11) NOT NULL,`enter code here`
PRIMARY KEY (`test_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Here I have four tables in my database named test_center
. Those are:
test_user
--> PK =u_id
test_metadata
--> PK =test_id
student_detail
--> PK =Student_id
test_records
--> PK =test_record_id
(Child table)
Table 4 is child table and 1, 2, 3 are masters.
I am trying for test_records
table to have three foreign keys from each of these master tables, but it is showing some errors as follows. (I am using MySQL Workbench 5.0 and MySQL server)
ERROR 1005: Can't create table 'test_center.#sql-aa4_12' (errno: 121)
SQL Statement:
ALTER TABLE `test_center`.`test_records`
ADD CONSTRAINT `Student_id`
FOREIGN KEY (`Student_id` )
REFERENCES `test_center`.`student_detail` (`Student_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
ADD CONSTRAINT `test_id`
FOREIGN KEY (`test_id` )
REFERENCES `test_center`.`test_metadata` (`test_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
ADD CONSTRAINT `u_id`
FOREIGN KEY (`u_id` )
REFERENCES `test_center`.`test_user` (`u_id` )
ON DELETE CASCADE
ON UPDATE CASCADE
ERROR: Error when running failback script. Details follow.
ERROR 1046: No database selected
SQL Statement:
CREATE TABLE `test_records` (
`test_record_id` int(11) NOT NULL AUTO_INCREMENT,
`test_name` varchar(45) NOT NULL,
`Result` float NOT NULL,
`status` varchar(45) NOT NULL,
`Student_id` varchar(45) NOT NULL,
`u_id` int(11) NOT NULL,
`test_id` int(11) NOT NULL,`enter code here`
PRIMARY KEY (`test_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您的查询无法识别数据库。 在创建表之前,您需要告诉MySQL要使用哪个数据库。
如果数据库不存在,您需要创建它:
然后是
Your query is unable to identify the database. You need to tell MySQL which database to use:
before you create a table.In case the database does not exist, you need to create it as:
followed by