第21课:安全的约束

本节要点

约束简介
主键约束
外键约束
唯一约束
检查约束
非空约束

约束简介

对于下面这张学生表:

CREATE TABLE student
(
    student_id varchar(50) not null,
    student_name varchar(100) not null default '',
    gender varchar(10) not null default '',
    age integer not null default 0 ,
    class_id varchar(50) not null default ''
);

对于表中的性别字段(gender),我们知道,性别只可能有男、女、未知三种值。如果想插入一条为“人妖”的值,是不允许的。

或者,对于表中的班级编号(class_id)字段,只允许插入班级表中已经存在的班级编号字段。

那么怎么才能保证肯定不会插入其他的垃圾数据呢?

我们可以使用本节课讲解的约束,来保证插入表中的数据都是有效的。

约束的英文是constraint,代表对表中数据的限制条件。不满足限制条件的数据,是不允许插入表中的。

它主要是用来保证表中数据的完整性以及有效性。

在SQL语言中,约束的种类还是蛮多的,下面我们一一介绍。

主键约束

主键,是用来唯一标识表中每一行的一列或者多列。那么,我们在创建表时,或者创建表后,是可以为表添加主键约束的。

方法一:在创建表时添加约束

CREATE TABLE student (
    student_id varchar(50) NOT NULL COMMENT '学生编号',
    student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
    gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号',
    PRIMARY KEY(student_id)
)COMMENT='学生';

方法二:创建表后,使用ALTER语句为表添加主键约束。

CREATE TABLE student (
    student_id varchar(50) NOT NULL COMMENT '学生编号',
    student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
    gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生’;

ALTER TABLE student ADD PRIMARY KEY(student_id);

外键约束

外键,是用来约定某一列的值必须出现在另一个表的主键中。也是有两种方法:

方法一:在创建表时,添加外键约束

CREATE TABLE student (
    student_id varchar(50) NOT NULL COMMENT '学生编号',
    student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
    gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号',
    FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id)
)COMMENT='学生';

方法二:创建表后,使用ALTER语句为表添加外键约束

CREATE TABLE student (
    student_id varchar(50) NOT NULL COMMENT '学生编号',
    student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
    gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生’;

ALTER TABLE student ADD FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id);

唯一约束

唯一约束,是用来约定某一列或某几列的值在表中不重复。也有两种方法:

方法一:在字段的后面,使用UNIQUE关键字,为表中字段添加唯一约束。这种方法只适合于对单个字段添加唯一约束。

CREATE TABLE student (
    student_id varchar(50) NOT NULL COMMENT '学生编号',
    student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名' UNIQUE,
    gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生';

方法二:创建表时,在字段定义的最后使用UNIQUE关键字,为表中字段添加唯一约束。这种方法适合于多个字段的情况。

CREATE TABLE student (
    student_id varchar(50) NOT NULL COMMENT '学生编号',
    student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
    gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号',
    UNIQUE (calss_id,student_name)
)COMMENT='学生';

检查约束

检查约束,是指约定某一列的值必须满足指定的条件。

比如,约定student表中的age字段的值,在10岁~20岁之间。那么可以使用CHECK关键字设置约束:

CREATE TABLE student (
    student_id varchar(50) NOT NULL COMMENT '学生编号',
    student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
    gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int(11) NOT NULL DEFAULT '0' COMMENT '年龄' CHECK ( age >= 10 AND age <= 20 ),
    class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生';

需要特别说明的是:与其他约束不同,MySQL虽然支持CHECK约束,但并没有强制限制。也就是说,违反约束的数据也可以插入表中。

非空约束

非空约束,用来约定某一列的值不可为NULL值。这个在前面的课程中,已经使用过很多次了。

CREATE TABLE student (
    student_id varchar(50) NOT NULL COMMENT '学生编号',
    student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',
    gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生';

课后习题:

1、对于第6课课后习题中的论坛注册用户表、第15课课后习题中的文章表、用户访问的文章记录表,请编写SQL完成以下查询。

(1)、为注册用户表中的用户级别字段设置约束,要求用户级别字段的取值必须为下面几个中的一个:

新手入门、铜牌用户、银牌用户、金牌用户、钻石用户、皇冠用户

(2)、为文章表中的访问次数、评论次数字段设置约束,要求这两个字段的取值必须大于等于0。

【版权说明:仅允许非商业转载且请注明出处:Mac私塾 网址:http://macsishu.com】

发表评论

邮箱地址不会被公开。

Captcha Code