MySQL 创建新表时如何添加外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/239443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I add a foreign key when creating a new table?
提问by user17451
I have these two CREATE TABLEstatements:
我有这两个CREATE TABLE声明：
CREATE TABLE GUEST (
id int(15) not null auto_increment PRIMARY KEY,
GuestName char(25) not null
);
CREATE TABLE PAYMENT (
id int(15) not null auto_increment
Foreign Key(id) references GUEST(id),
BillNr int(15) not null
);
What is the problem in the second statement? It did not create a new table.
第二个陈述有什么问题？它没有创建新表。
回答by DOK
The answer to your question is almost the same as the answer to this one.
你的问题的答案与这个问题的答案几乎相同。
You need to specify in the table containing the foreign key the name of the table containing the primary key, and the name of the primary key field (using "references").
您需要在包含外键的表中指定包含主键的表的名称和主键字段的名称（使用“引用”）。
Thishas some code showing how to create foreign keys by themselves, and in CREATE TABLE.
这有一些代码显示如何自己创建外键，并在 CREATE TABLE 中。
Here's one of the simpler examples from that:
这是其中一个更简单的例子：
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
回答by Hapkido
I will suggest having a unique key for the payment table. On it's side, the foreign key should not be auto_increment as it refer to an already existing key.
我建议为付款表设置一个唯一键。另一方面，外键不应该是 auto_increment，因为它指的是一个已经存在的键。
CREATE TABLE GUEST(
id int(15) not null auto_increment PRIMARY KEY,
GuestName char(25) not null
) ENGINE=INNODB;
CREATE TABLE PAYMENT(
id int(15)not null auto_increment,
Guest_id int(15) not null,
INDEX G_id (Guest_id),
Foreign Key(Guest_id) references GUEST(id),
BillNr int(15) not null
) ENGINE=INNODB;
回答by Thirumurugan K
create table course(ccode int(2) primary key,course varchar(10));
create table student1(rollno int(5) primary key,name varchar(10),coursecode int(2) not
null,mark1 int(3),mark2 int(3),foreign key(coursecode) references course(ccode));
回答by mattoc
Make sure you're using the InnoDB engine for either the database, or for both tables. From the MySQL Reference:
确保对数据库或两个表都使用 InnoDB 引擎。从 MySQL 参考：
For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.
对于 InnoDB 以外的存储引擎，MySQL Server 会解析 CREATE TABLE 语句中的 FOREIGN KEY 语法，但不使用或存储它。
回答by Piyush Sharma
There should be space between int(15)and not null
int(15)和之间应该有空格not null
