MYSQL 表格父级ID是什么意思 access父子表

文章目录

• 1. The SurgeTech Conference

• 1.1 Attendee
• 1.2 Company
• 1.3 Presentation
• 1.4 Room
• 1.5 Presentation Attendance

• 2. 主键与外键

• 2.1 主键
• 2.2 外键

• 3. The Schema
• 4. 创建数据库

• 4.1 创建表格
• 4.2 设置外键约束

• 参考资料

我们在上一篇中介绍了

Join，接下来我们将介绍数据库设计。

1. The SurgeTech Conference

假如你是一个名为 SurgeTech 会议的管理人员，今天老大叫你创建一个数据库来管理参会人员（attendees），公司（companies），演讲（presentations），会议室（rooms）以及演讲者（oresentation attendance），你该怎么来设计呢？

首先，想一下这些不同的对象，应该如何转换为结构化的数据，这看起来很麻烦，但再麻烦的问题都可以化成许许多多的小问题。

1.1 Attendee

参会人员指的就是那些需要来签到的人员（包含了部分的 VIPs），需要登记到每个参会人员的 ID、姓名、手机号、邮箱以及是否 VIP：

图1 Attendee 表

1.2 Company

参会的公司也需要记录，包含公司的 ID、公司名、公司简介以及联系人（联系人也应该包含在上方的参会人员）：

图2 Company 表

1.3 Presentation

一些公司会在特定的时间进行演讲（包含了演讲开始时间和结束时间），因此需要记录每场演讲的公司 ID 以及会议室 ID：

图3 Presentation 表

1.4 Room

会议室的编号、楼层以及座位数需要记录：

图4 Room 表

1.5 Presentation Attendance

如果参会者对某个公司的演讲感兴趣，他们可以申请一张入场券。为了实现这个功能，就需要制作一张听众表，来记录入场券编号、演讲编号以及听众编号：

图5 Presentation Attendance 表

2. 主键与外键

2.1 主键

对于一个表，主键（Primary Key） 是必不可少的。主键能够一一对应到每一行数据，通常是某一列不重复的数据（或者某几列）。比如 ATTENDEE_ID 就是在表 ATTENDEE 的主键，而 COMPANY_ID 是表 COMPANY 的主键。

由于主键中的不允许有重复，因此也起到着确保数据完整性的约束作用。不可重复意味着在表 ATTENDEE 中不能出现两个 ATTEND_ID 为 2 的数据，否则将会导致错误。

如果某几列数据作为一个整体充当表的主键，那么就要求这几列数据的组合不能够重复。举个例子，如果你将 REPORT_ID 和 APPROVER_ID 设为主键，那么就不能出现有相同 REPORT_ID 和 APPROVER_ID 的记录

2.2 外键

主键（Primary Key）出现在父表（Parent Table）中，而外键（Foreign Key）则出现在子表（Child Table）中。子表中的外键指向父表中的主键，举个例子。ATTENDEE_ID 是 ATTENDEE 表的主键，但在 PRESENTATION_ATTENDANCE 表中 ATTENDEE_ID 则是一个外键。这两个联合在一起就成为了一个一对多的关系，而不像主键，外键并不要求唯一性，因为它就是一对多关系中的多。

主键和外键并不需要有相同的列名，如子表 PRESENTATION 中的外键 BOOKED_COMPANY_ID 指向父表 COMPANY 中的主键 COMPANY_ID。

3. The Schema

在了解了主键和外键后，我们可以将这五个表画成一个 Database Schema，如下图所示。一个 Database Schema 展示了所有的表、列以及以及它们的关系。所有的主键和外键都用箭头连接起来了，这些箭头展示了父表与子表之间的联系。

在数据库中，schema（模式）是数据库的组织和结构^[1]

图6 The SurgeTech Conference Database Schema

如果你直接看这个图的话可能会有点晕，但再复杂的结构都可以分成几个简单的部分。当你用 SELECT 写查询时，最多也就用到两三个表，所以你可以先看一部分，这样会容易一点：

图7 父表 ATTENDEE 与子表 COMPANY 的关系图

4. 创建数据库

• 使用Jupyter Notebook 运行 SQL 语句需安装 ipython-sql
• %sql 以及 %%sql 为在 Notebook 中运行 SQL 语句，在 SQLite 命令行或 SQLite Stiduo 中不需要 %sql 或 %%sql

载入 SQL 以及连接 SQLite：

%load_ext sql

我们已经设计好整个数据库的结构了，现在就可以看是创建数据库了，运行下面的代码，你就可以在你的当前路径的 DataBase 文件夹中产生一个名为 surgetech_conference2.db 的数据库文件：

%sql sqlite:///DataBase/surgetech_conference2.db

'Connected: @DataBase/surgetech_conference2.db'

4.1 创建表格

图8 Attendee 表

以刚才的 Company 表为例，我们希望：

• ATTENDEE_ID 是一个整形（INTERGER）数据，并且将其设为主键（PRIMARY KEY），同时能够自动标号（AUTOINCREMENT），即第一个 ATTENDEE_ID 为 1 ，第二个 ATTENDEE_ID 为 2，以此类推；
• FIRST_NAME 是长度不超过 30 的字符数据（VARCHAR）并且不能为缺失值（NOT NULL）；
• LAST_NAME 是长度不超过 30 的字符数据（VARCHAR）并且不能为缺失值（NOT NULL）；
• PHONE 为整形（INTERGER）数据
• EMAIL 是长度不超过 30 的字符数据（VARCHAR）；
• VIP 为布尔型数据（BOOLEAN）并且默认值为 0

%%sql
CREATE TABLE attendee 
(
    attendee_id   INTEGER PRIMARY KEY AUTOINCREMENT, 
    first_name    VARCHAR (30) NOT NULL, 
    last_name     VARCHAR (30) NOT NULL,
    phone         INTEGER,
    email         VARCHAR (30),
    vip           BOOLEAN DEFAULT (0)
);

* sqlite:///DataBase/surgetech_conference2.db
Done.

4.2 设置外键约束

我们刚刚创建的表格其实还是不够安全的，因为并没有设置外键约束，要记得子表的外键与父表的主键连接在一起。因此逻辑上，对于主键中没有出现过的数值，就不应该出现在子键中。

举个例子，如果父表 COMPANY 的主键 COMPANY_ID 中没有 5 这个数值，则子表 PRESENTATION 的外键 BOOKED_COMPANY_ID 中就不应该出现 5，否则就是一个孤儿数据（Orphaned record），我们可以通过设置外间约束来避免这种情况。

从 SQLite 3.6.19 开始支持 外键约束

SQLite 默认没有打开外键约束，若需要使用外键约束，需要设置 'PRAGMA foreign_keys = ON;

%%sql
PRAGMA foreign_keys = ON;

* sqlite:///DataBase/surgetech_conference2.db
Done.

现在我们可以利用图 6 来创建其他的表并设置外键约束了：

%%sql
CREATE TABLE company 
(
    company_id         INTEGER      PRIMARY KEY AUTOINCREMENT, 
    name               VARCHAR(30)  NOT NULL,
    description        VARCHAR(60),
    primary_contact_id INTEGER      NOT NULL REFERENCES attendee(attendee_id)
);


CREATE TABLE room 
(
    room_id       INTEGER PRIMARY KEY AUTOINCREMENT, 
    floor_number  INTEGER NOT NULL,
    seat_capacity INTEGER NOT NULL
);

CREATE TABLE presentation 
(
    presentation_id   INTEGER PRIMARY KEY AUTOINCREMENT, 
    booked_company_id INTEGER NOT NULL REFERENCES company(compangy_id),
    booked_room_id    INTEGER NOT NULL REFERENCES room(room_id),
    start_time        TIME,
    end_time          TIME
);


    
CREATE TABLE presentation_attendance 
(
    ticket_id       INTEGER PRIMARY KEY AUTOINCREMENT, 
    presentation_id INTEGER REFERENCES presentation(presentation_id),
    attendee_id     INTEGER REFERENCES attendee(attendee_id)
);

* sqlite:///DataBase/surgetech_conference2.db
Done.
Done.
Done.
Done.

你可以查看下是否创建成功：

%%sql
select * from sqlite_master where type = "table";

* sqlite:///DataBase/surgetech_conference2.db
Done.

type	name	tbl_name	rootpage	sql
table	attendee	attendee	2	CREATE TABLE attendee ( attendee_id INTEGER PRIMARY KEY AUTOINCREMENT, first_name VARCHAR (30) NOT NULL, last_name VARCHAR (30) NOT NULL, phone INTEGER, email VARCHAR (30), vip BOOLEAN DEFAULT (0) )
table	sqlite_sequence	sqlite_sequence	3	CREATE TABLE sqlite_sequence(name,seq)
table	company	company	4	CREATE TABLE company ( company_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(30) NOT NULL, description VARCHAR(60), primary_contact_id INTEGER NOT NULL REFERENCES attendee(attendee_id) )
table	room	room	5	CREATE TABLE room ( room_id INTEGER PRIMARY KEY AUTOINCREMENT, floor_number INTEGER NOT NULL, seat_capacity INTEGER NOT NULL )
table	presentation	presentation	6	CREATE TABLE presentation ( presentation_id INTEGER PRIMARY KEY AUTOINCREMENT, booked_company_id INTEGER NOT NULL REFERENCES company(compangy_id), booked_room_id INTEGER NOT NULL REFERENCES room(room_id), start_time TIME, end_time TIME )
table	presentation_attendance	presentation_attendance	7	CREATE TABLE presentation_attendance ( ticket_id INTEGER PRIMARY KEY AUTOINCREMENT, presentation_id INTEGER REFERENCES presentation(presentation_id), attendee_id INTEGER REFERENCES attendee(attendee_id) )

参考资料

[1] 我是康小小.数据库中的Schema是什么？[EB/OL]., 2018-01-10.

[2] Thomas Nield.Getting Started with SQL[M].US: O’Reilly, 2016: 67-89