如何在创建新表时添加外键？

I have these two CREATE TABLE statements:

我有这两个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.

第二个陈述中的问题是什么?它没有创建新表。

4 个解决方案

#1

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").

您需要在包含外键的表中指定包含主键的表的名称,以及主键字段的名称(使用“references”)。

This has 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;

#2

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;

#3

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语法,但不使用或存储它。

#4

-1

There should be space between int(15) and not null

int(15)和非null之间应该有空格

#1