mysql - 如何在一个表中使用外键在另一个表中使用主键

Question

我有 3 个表。类(class)、类(class)详情、类(class)预订。

类(class)

CREATE TABLE Courses (
    courseId int NOT NULL,
    courseName varchar(255),
    level varchar(255),
    description varchar(255),  
    PRIMARY KEY (courseId)
);

类(class)详情

CREATE TABLE CourseDetails (
    CourseStartDate int NOT NULL,
    Location varchar(255) NOT NULL,
    MaxNoPlaces int,
    Length int,
    Instructor varchar(255),
    TotalPlacesBooked int,
    NoPlacesCancelled int,
    AdultPrice int,
    ChildPrice int, 
    courseId int,
    CONSTRAINT PK_CourseDetails PRIMARY KEY (CourseStartDate,Location,courseId),
    FOREIGN KEY (courseId) REFERENCES Courses(courseId)
);

类(class)表中的 courseId 用作 CourseDetails 表中的外键。

现在我想在另一个名为 CourseBooking 的表中使用 CourseStartDate Location 和 CourseDetails 表中的 courseId。我知道如何将 CourseStartDate Location 添加为外键。但是我很困惑如何将 courseDetails 表中的 courseId 添加为新 CourseBookings 表中的外键。

我试过以下方法

CREATE TABLE CourseBookings (
    CourseStartDate int NOT NULL,
    Location varchar(255) NOT NULL,
    GuestNo int, 
    courseId int,
    CONSTRAINT PK_CourseDetails PRIMARY KEY (CourseStartDate,Location,GuestNo,courseId),
    FOREIGN KEY (courseId) REFERENCES CourseDetails.courseId(courseId),
    FOREIGN KEY (CourseStartDate) REFERENCES CourseDetails(CourseStartDate),
    FOREIGN KEY (Location) REFERENCES CourseDetails(Location),
    FOREIGN KEY (GuestNo) REFERENCES Guest(GuestNo)
 );

但是有一个错误说

Can't create table 'b8040777_db1.CourseBookings'

支持事务、行级锁定和外键

Answer 1

CourseBookings 表的声明有几个问题。

---

问题 1

这个:

FOREIGN KEY (courseId) REFERENCES CourseDetails.courseId(courseId),

应该写成:

FOREIGN KEY (courseId) REFERENCES CourseDetails(courseId),

---

问题 2

CourseDetails 的列之一，由表 CourseBookings 的外键引用，没有索引。

来自 the documentation :

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

在表 CourseDetails 中:

• courseId 有一个自动创建的索引，因为它引用了 Courses(courseId)
• CourseStartDate没有索引但是是主键约束声明自动生成的索引中的第一列
• Location 没有索引，它只是主键索引中的第二列 --> 无法创建引用它的外键，尝试时会引发错误<

---

解决方案 1

始终可以将缺少的索引添加到表中，方法是将其添加到 CREATE TABLE CourseDetails 语句中:

INDEX idx_Location (Location)

---

解决方案 2

在您的用例中，我怀疑您实际需要的是一个多列外键(仅在 InnoDB 中支持)，它引用 CourseDetails 的主键(这些列上已经存在索引) .这将允许您将 CourseBookings 的每条记录与 CourseDetails 中的唯一父记录相关联。

DDL的建议:

CREATE TABLE CourseBookings (
    CourseStartDate int NOT NULL,
    Location varchar(255) NOT NULL,
    GuestNo int, 
    courseId int,
    CONSTRAINT PK_CourseDetails PRIMARY KEY (CourseStartDate,Location,courseId,GuestNo),
    FOREIGN KEY (CourseStartDate,Location,courseId) 
        REFERENCES CourseDetails(CourseStartDate,Location,courseId),
    FOREIGN KEY (GuestNo) REFERENCES Guest(GuestNo)
 );

---

解决方案 3

如果解决方案 2 适合您的用例，则意味着可以通过在表 CourseDetails 上创建一个自动递增整数主键 来优化您的架构。现有的主键可以变成一个 UNIQUE 约束。然后，在表 CourseBookings 中，您可以只存储该列的外键。

这将为您提供一种将一个表与另一个表相关联的简单有效的方法，同时避免跨表重复信息(这实际上使您在 CourseBookins 中只有 3 列)。

在关系数据库设计中，在大多数表上创建这样的主键通常是一种很好的做法。您也可以考虑将一个添加到其他表。

示例:

CREATE TABLE CourseDetails (
    id int PRIMARY KEY AUTO_INCREMENT,
    CourseStartDate int NOT NULL,
    ...
    CONSTRAINT PK_CourseDetails UNIQUE (CourseStartDate,Location,courseId),
    FOREIGN KEY (courseId) REFERENCES Courses(courseId)
);

CREATE TABLE CourseBookings (
    id int PRIMARY KEY AUTO_INCREMENT,
    courseDetailId INT NOT NULL,
    GuestNo int, 
    CONSTRAINT PK_CourseBookings UNIQUE (courseDetailId,GuestNo),
    FOREIGN KEY (courseDetailId) REFERENCES CourseDetails(id),
    FOREIGN KEY (GuestNo) REFERENCES Guest(GuestNo)
);

---

PS，以防万一:

FOREIGN KEY (GuestNo) REFERENCES Guest(GuestNo)

表 Guest 是否存在于您的架构中(您没有为其显示 CREATE TABLE)？