第1步:设计
首先要设计一张用于我想要用途的表,例如如下用于描述个人的信息类型:
- 姓名;
- 性别;
- 出生日期;
- 地址;
- 最喜爱的食物。
下面为他来指定列和数据类型:
列 | 类型 | 允许值
- | - | -
name | varchar(40) |
gender | char(1) | M,F
birth_date | date |
address | varchar(100) |
favorite_foods | varchar(200) |
其中,name、address 和 favorite_foods 列的类型为varchar,容许不同形式的数据条目,而
gender列则只允许单个字母M或F。birth_date为日期类型,因为此处并不需要精确到具体的时间。
第2步:精化
进一步观察person表,会发现如下问题:
- name列实际上是包含了姓氏和名字的复合对象。
- 可能存在多个人具有相同的名字、性别、生日等,而person表中并没有列来保证唯一性。
- address列也是包含了街道、省名、县市名和邮政编码的符合对象。
- favorite_foods列可以是包含0、1或更多条目的列表,因此最好为此数据创建一个独立的表,其中包含一个指向person表的外键,以便为每一种食物知名所归属的人员。
考虑这些问题之后,列出person规范化后的结果:
person表
列 | 类型 | 允许值
- | - | -
person_id | smallint(unsigned) |
first_name | varchar(20) |
last_name | varchar(20) |
gender | char(1) | M,F
birth_date | date |
street | varchar(30) |
city | varchar(20) |
state | varchar(20) |
county | varchar(20) |
postal_code | varchar(20) |
现在person表已经具有了主键(person_id)来保证唯一性,下一步便是建立favorite_food表,其中包含一个执行person表的外键:
favorite_food表
列 | 类型
- | -
person_id | smallint(unsigned)
food | varchar(20)
person_id 和 food 列构成了 favorite_food表的主键,并且person_id列也是person表的外键。
第3步:构建SQL方案语句
创建 person 表的语句如下:
CREATE TABLE person
(
person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender CHAR(1),
birth_date DATE,
street VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(20),
postal_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);
最下面的一个语句(CONSTRAINT pk_person PRIMARY KEY (person_id)
)用于指定一个约束,他创建了一个名为pk_person
的约束(constraint),其创建在 person_id 之上。
对于性别(gender)这一列来说,可以为其添加约束,使其只能是'M'或者'F'。可以使用 检查约束 或者 enum 。
检查约束
为该列添加一个检查约束,以限制该列只存放被允许的值。MySQL允许在定义列时关联一个检查约束,如下所示:
gender CHAR(1) CHECK (gender IN ('M','F')),
enum
MySQL提供了另一种名为 enum 的字符数据类型,它可以将检查约束与数据类型定义融合在一起,此方法的定义如下:
gender ENUM('M','F'),
下面是重新定义了的person表,其中使用enum作为gender列的数据类型:
CREATE TABLE person
(
person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender ENUM('M','F'),
birth_date DATE,
street VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(20),
postal_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);
可以通过DESC person
命令(或者DESCRIBE person
命令)来查看person表的详细信息。结果如下:
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| fname | varchar(20) | YES | | NULL | |
| lname | varchar(20) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| street | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| country | varchar(20) | YES | | NULL | |
| postal_code | varchar(20) | YES | | NULL | |
+-------------+----------------------+------+-----+---------+-------+
其中第1列和第2列的结果是显而易见的。
第3列显示该列是否允许在插入数据时被省略。
第4列显示该列是否作为键值(主键或外键),本例中 person_id 列被标记为主键。
第5列显示如果在插入数据时忽略该列,是否向其提供默认值。
第6列("Extrra")显示该列附加的说明信息。
接下来创建 favorite_foods 表:
favorite_foods表
CREATE TABLE favorite_food
(
person_id SMALLINT UNSIGNED,
food VARCHAR(20),
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
REFERENCES person (person_id)
);
除了有以下不同之外,它与前面 person 表的 create table 语句十分类似:
- 由于一个人可能有多种喜爱的食物(当然这也是创建此表的原因),仅靠 person_id 列不能保证数据的唯一性,因此本表的逐渐包含两列:person_id 和 food。
- favorite_food 包含了另一种类型的约束,及外键约束,它限制了favorite表中person_id列的值只能够来自person表。通过这种约束,使得当person表中没有person_id为33的记录时,向favorite_food表中增加person_id为33、喜爱食物为披萨的数据行是不可能的。
- 同理,如果要删除person表中一个id为3的人,则需要先删除favorite_food表中person_id为3的所有信息,再去删除person表中的数据。(不过好像有一个去除关联的方法)
在执行完create table
语句后,使用describe命令可以显示下面的结果:
+-----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO | PRI | NULL | |
| food | varchar(20) | NO | PRI | NULL | |
+-----------+----------------------+------+-----+---------+-------+