day07-1MySQL约束

MySQL约束

• 基本介绍
  

约束用于确保数据库的数据满足特定的商业规则
在mysql中，约束包括：not null，unique，primary key，foreign key 和check 5种
1.primary key(主键)

1. 字段名 字段类型 primary key

复制代码

用于唯一地标识表行的数据，当定义主键约束之后，该列不能重复

• 细节说明
  

• primary key不能重复而且不能为null
  
• 一张表最多只能有一个主键，但是可以是复合主键
  
• 主键的指定方式有两种
  
  
  
  • 直接在字段名后面指定：字段名 primary key
    
  • 在表定义最后写 primary key（列名）
    
  
  
• 使用desc 表名，可以看到primary key的情况
  
• 在实际开发中，每一张表往往都会设计一个主键
  

例子

1. -- 主键的使用
3. -- id        name         email
4. CREATE TABLE t17(
5.         id INT PRIMARY KEY, -- 表示id列是主键
6.         `name` VARCHAR(32),
7.         email VARCHAR(32)
8. );
9. INSERT INTO t17 VALUES(1,'jack','jack@sohu.com');
10. INSERT INTO t17 VALUES(2,'tom','tom@sohu.com');
13. -- 1. primary key不能重复而且不能为null
14. INSERT INTO t17 VALUES(NULL,'jack','jack@sohu.com');-- 插入失败，Column 'id' cannot be null
16. -- 2. 一张表最多只能有一个主键，但是可以是复合主键（比如id+name）
17. -- 演示复合主键
18. CREATE TABLE t18(
19.         id INT,
20.         `name` VARCHAR(32),
21.         email VARCHAR(32),
22.         PRIMARY KEY(id,`name`) -- 这里就是复合主键
23. );
25. INSERT INTO t18 VALUES(1,'tom','tom@sohu.com'); -- ok
26. INSERT INTO t18 VALUES(1,'jack','jack@sohu.com');-- OK
27. INSERT INTO t18 VALUES(1,'tom','12345@sohu.com'); -- false
28. SELECT * FROM t18;
30. -- 3. 主键的指定方式有两种
31. --  3.1直接在字段名后面指定：字段名 primary key
32. CREATE TABLE t19(
33.         id INT PRIMARY KEY,
34.         `name` VARCHAR(32),
35.         email VARCHAR(32)
36. );
38. --  3.2在表定义最后写 primary key（列名）
39. CREATE TABLE t20(
40.         id INT,
41.         `name` VARCHAR(32),
42.         email VARCHAR(32),
43.         PRIMARY KEY(id)
44. );
46. -- 4. 使用desc 表名，可以看到primary key的情况
47. DESC t20; -- 查看t20的情况，显示约束的情况

复制代码

2.not null(非空)

如果在列上定义了not null，那么当插入数据时，必须为列提供数据

1. 字段名 字段类型 not null

复制代码

3.unique(唯一)

当定义了唯一约束后，该列值是不能重复的

1. 字段名 字段类型 unique

复制代码

• unique细节
  

• 如果没有指定not null，则unique字段可以有多个null
  
• 一张表可以有多个unique字段
  

1. -- unique的使用
3. CREATE TABLE t21(
4.         id INT UNIQUE, -- 表示id列不可重复
5.         `name` VARCHAR(32),
6.         email VARCHAR(32)
7. )
8. INSERT INTO t21 VALUES(1,'jack','jack@sohu.com'); -- ok
9. INSERT INTO t21 VALUES(1,'tom','tom@sohu.com');  -- false，Duplicate entry '1' for key 'id'
11. -- unique细节
13. -- 1. 如果没有指定not null，则unique字段可以有多个null
14. -- 如果一个列（字段），是 unique not null 则使用效果类似 primary key
15. INSERT INTO t21 VALUES(NULL,'tom','tom@sohu.com');-- ok
16. SELECT * FROM t21;
18. -- 2. 一张表可以有多个unique字段
19. CREATE TABLE t22(
20.         id INT UNIQUE, -- 表示id列不可重复
21.         `name` VARCHAR(32) UNIQUE, -- 表示name也不可以重复
22.         email VARCHAR(32)
23. )
24. DESC t22;

复制代码

4.foreign key(外键)

• foreign key(外键)
  

用于定义主表和从表之间的关系：
外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。
当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

• 语法
  

1. foreign key(本表字段名) references 主表名(主键名或unique字段名)

复制代码

例子

1. -- 外键演示
2. -- 创建 主表 my_class
3. CREATE TABLE my_class(
4.         id INT PRIMARY KEY, -- 班级编号
5.         `name` VARCHAR(32) NOT NULL DEFAULT ''
6. );
8. -- 创建从表 my_stu
9. CREATE TABLE my_stu(
10.         id INT PRIMARY KEY,-- 学生编号
11.         `name` VARCHAR(32) NOT NULL DEFAULT '',
12.         class_id INT, -- 学生所在的班级的编号
13.         -- 下面指定外键关系
14.         FOREIGN KEY(class_id) REFERENCES my_class(id)
15. );
17. -- 测试数据
18. INSERT INTO my_class VALUES(100,'java'),(200,'web');
19. INSERT INTO my_class VALUES(300,'php');
20. SELECT * FROM my_class;
22. INSERT INTO my_stu VALUES(1,'tom',100);
23. INSERT INTO my_stu VALUES(2,'jack',200);
24. INSERT INTO my_stu VALUES(3,'hsp',300);
25. INSERT INTO my_stu VALUES(4,'mary',400); -- 失败，因为400号班级不存在
27. SELECT * FROM my_stu;

复制代码

• 外键细节说明
  

• 外键指向的主表的字段，要求是primary key 或是unique
  
• 表的类型是innodb，这样的表才支持外键
  
• 外键字段的类型要和主键字段的类型一致（长度可以不同）
  
• 外键字段的值，必须在主键字段中出现过，或者为null  [前提是外键字段允许为null]
  
• 一旦建立主外键的关系，数据就不能随意删除了
  

5.check

• check
  

用于强制行数据必须满足的条件。假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在此范围，就会提示出错
提示：oracle和sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

• 语法
  

1. 列名 类型 check(check条件)

复制代码

在mysql中实现check的功能，一般是在程序中控制或者通过触发器完成

例子

[code]-- 演示check的使用-- 目前mysql5.7还不支持check，只做语法校验但不会生效-- 测试CREATE TABLE t23(        id INT PRIMARY KEY,        `name` VARCHAR(32),        sex VARCHAR(6) CHECK(sex IN('man','woman')),        sal DOUBLE CHECK(sal >1000 AND sal