mysql - SQL 一对多关系

Question

我是 SQL 新手，我想在两个表之间创建一对多关系。我使用以下查询创建了这两个表:

CREATE TABLE Customers
(
    CustomerId INT NOT NULL AUTO_INCREMENT,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    Address VARCHAR(255) NOT NULL,
    PRIMARY KEY(CustomerId)
);

CREATE TABLE Orders
(
    OrderId INT NOT NULL AUTO_INCREMENT,
    Date DATE NOT NULL,
    Quantity INT NOT NULL,
    TotalDue FLOAT NOT NULL,
    CustomerId INT NOT NULL,
    PRIMARY KEY(OrderId),
    FOREIGN KEY(CustomerId) REFERENCES Customers(CustomerId)
);

但是，即使我将 CustomerId 设置为 Orders 表的外键，我仍然可以在 Orders 表中添加行CustomerId 不存在于 Customers 表中。这是为什么？如何在表之间创建真正的链接？

这就是表格的样子(从我的 mysql 客户端复制/粘贴):

mysql> select * from Customers;
+------------+-----------+----------+-------------------+------------------------+
| CustomerId | FirstName | LastName | Email             | Address                |
+------------+-----------+----------+-------------------+------------------------+
|          1 | Jacks     | James    | james98@yahoo.com | Str. Moony, No. 9      |
|          2 | Mock      | Grad     | rrfuX@yahoo.com   | Str. Mars, No. 91      |
|          3 | James     | Geremy   | gv@yahoo.com      | Str. Monday, No. 12    |
|          4 | Joana     | Joan     | iiogn@yahoo.com   | Str. Comete, No. 19    |
|          5 | Granicer  | James    | gtuawr@yahoo.com  | Str. Sydney, No. 651   |
+------------+-----------+----------+-------------------+------------------------+
5 rows in set (0.00 sec)

mysql> select * from Orders;
+---------+------------+----------+----------+------------+
| OrderId | Date       | Quantity | TotalDue | CustomerId |
+---------+------------+----------+----------+------------+
|       1 | 2014-01-09 |       10 |      340 |          3 |
|       2 | 2014-01-09 |        1 |       50 |          3 |
|       3 | 2014-01-09 |       11 |       55 |          5 |
|       4 | 2014-01-09 |       11 |       55 |         51 |
+---------+------------+----------+----------+------------+
4 rows in set (0.00 sec)

如您所见，OrderId 4 包含一个 CustomerId 51 的客户，该客户不在 Customers 表中。

Answer 1

您需要设置存储引擎...将查询更改为...

CREATE TABLE Orders
(
    OrderId INT NOT NULL AUTO_INCREMENT,
    Date DATE NOT NULL,
    Quantity INT NOT NULL,
    TotalDue FLOAT NOT NULL,
    CustomerId INT NOT NULL,
    PRIMARY KEY(OrderId),
    FOREIGN KEY(CustomerId) REFERENCES Customers(CustomerId)
)ENGINE=INNODB;