麻烦定义外键(MySQL) [英] Trouble defining foreign keys (MySQL)
问题描述
这个例子是一个大学数据库,我有一个小节
描述部分;
+ -------------- + ------------- + ------ + ----- + --- ------ + ------- +
|字段|类型|空|密钥|默认|额外|
+ -------------- + ------------- + ------ + ----- + --- ------ + ------- +
| course_id | varchar(7)| NO | PRI | | |
| sec_id | varchar(10)| NO | PRI | | |
|学期| varchar(10)| NO | PRI | | |
|年| | int(11)| NO | PRI | 0 | |
|建筑| varchar(20)|是| MUL | NULL | |
| room_no | varchar(5)|是| | NULL | |
| time_slot_id | varchar(5)|是| MUL | NULL | |
+ -------------- + ------------- + ------ + ----- + --- ------ + ------- +
我正在努力一个需要数据库,并能够使外键为 ID
和 course_id
,但任何其他我试图做' (
ID int,
course_id varchar(7),$ b $(
$ b
b sec_id varchar(10),
semester varchar(10),
year int,
grade int,
主键(course_id,sec_id,学期,年),
外键(ID)引用学生(ID)
级联更新级联上的级联,
外键(course_id)引用部分(course_id)
级联上级联级联
)engine = innodb;
直到这一点起作用。
如果我尝试添加这个,它会给我 errno:150
外键(sec_id)
引用段(sec_id)
删除级联更新级联
任何帮助表示赞赏。我想我可能会漏掉一些关于外键的基本知识,因为它对于 course_id
而不是 sec_id
是完全一样的吗?
可能性:
- 数据类型(包括未签名)不匹配。
- 父字段未编入索引。
- Data types, including unsigned, are not matching.
- Parent field is not indexed.
如果数据类型匹配,也许你还没有在你想要定义外键关系的父列上定义一个索引。请检查表部分
中的列 sec_id
是否已定义任何索引。
请参阅 :
$ b
Working on an introduction to databases class assignment and running into trouble on a few foreign key. I've done a few foreign keys but every now and then I get stumped and can't figure out why it doesn't work.
This example is a university database and I have section
describe section;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| course_id | varchar(7) | NO | PRI | | |
| sec_id | varchar(10) | NO | PRI | | |
| semester | varchar(10) | NO | PRI | | |
| year | int(11) | NO | PRI | 0 | |
| building | varchar(20) | YES | MUL | NULL | |
| room_no | varchar(5) | YES | | NULL | |
| time_slot_id | varchar(5) | YES | MUL | NULL | |
+--------------+-------------+------+-----+---------+-------+
I'm trying to make a takes database and am able to make the foreign key for ID
and course_id
, but any other one I try doesn't work
create table takes(
ID int,
course_id varchar(7),
sec_id varchar(10),
semester varchar(10),
year int,
grade int,
primary key (course_id, sec_id, semester, year),
foreign key (ID) references student(ID)
on delete cascade on update cascade,
foreign key (course_id) references section(course_id)
on delete cascade on update cascade
) engine = innodb;
Up until this point it works.
If I try and add this, it give me errno: 150
foreign key (sec_id)
references section(sec_id)
on delete cascade on update cascade
Any help is appreciated. I think I might be missing something fundamental about foreign keys since it worked for course_id
but not for sec_id
, which seem to be completely the same?
Possibilities:
If the datatypes are matching, perhaps you have not defined an index on the parent column on which you wanted to define a foreign key relation. Please check if the column sec_id
in table section
has any indexes defined.
Refer to:
这篇关于麻烦定义外键(MySQL)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!