[大数据]206 - 211.MySQL中的完整性约束

完整性约束

1.MySQL支持的完整性约束

1.1 主键约束

特点：
唯一且为空
主键可以由一个字段组成，也可以由多个字段组成
如果主键可以由一个字段组成，既可以添加到列级，也可以添加到表级
如果由多个字段组成只能添加到表级

1.1.1 在字段的列级添加约束

create table 表名( 字段名 字段类型 primary key )

CREATE TABLE student1(id int(8) PRIMARY KEY,name VARCHAR(20),age int(2),sex varchar(1))

1.1.2 表级上添加主键约束

create table 表名( 字段名1 字段类型1,
字段名2 字段类型2 , …
字段名n 字段类型n ,
[ constraint 主键约束名 ] primary key(字段名)
)

CREATE TABLE student2(id int(8),name VARCHAR(20),age int(2),sex VARCHAR(1),CONSTRAINT pk_student2 PRIMARY KEY(id))

1.1.3 多个字段作为组件，只能添加到表级

create table 表名( 字段名1 字段类型1,
字段名2 字段类型2 , …
字段名n 字段类型n ,
[ constraint 主键约束名 ] primary key(字段名1,字段名2)
)

CREATE TABLE student3(school VARCHAR(20),id INT(8),name VARCHAR(20),age int(2),sex VARCHAR(1),CONSTRAINT pk_student3 PRIMARY KEY(school,id))

1.2 给已有表添加主键

语法 ：alter table 表名 add [ constraint 主键约束名] primary key(主键名)

ALTER TABLE student4 add CONSTRAINT pk_student4 PRIMARY KEY(id)

1.3删除主键约束

语法：alter table 表名 drop primary key

ALTER TABLE student4 DROP PRIMARY KEY

2唯一约束

指表中字段的值不能重复

2.1列级添加唯一约束

create table 表名( 字段名1 字段类型1 unique,
字段名2 字段类型2 , …
字段名n 字段类型n ,
)

CREATE TABLE student5 (id int(8) UNIQUE,name VARCHAR(20),age INT(2),sex VARCHAR(1));
DESC student5;

2.2 表级添加唯一约束

create table 表名( 字段名1 字段类型1 unique,
字段名2 字段类型2 , …
字段名n 字段类型n ,
[ constraint 唯一约束名] unique(字段1，字段2…)
)

CREATE TABLE student6(id int(8),name VARCHAR(20),age int(2),sex VARCHAR(1),CONSTRAINT uk_student6_id_name UNIQUE (id,name));
DESC student6;

2.3 给已有表添加唯一约束

alter table 表名 add [ constraint 唯一约束名] unique(字段1，字段2…)

CREATE TABLE student7(id int(8),name VARCHAR(20),age int(2),sex VARCHAR(1));
ALTER TABLE student7 ADD CONSTRAINT uk_student7_name UNIQUE (name);
DESC student7;

2.4 删除唯一约束

alter table 表名 drop index 唯一约束名
注意：如果单个字段没有指定唯一约束名，则默认的唯一约束名为字段名
如果是多个字段组合为唯一约束的时候，默认的唯一约束名为第一个字段的名称
如果指定了约束名则删除的时候写约束名

ALTER TABLE student7 DROP INDEX uk_student7_name;
DESC student7;

3.非空约束

not null
某张表中某字段的值不能为空

注意
1.只能使用列级添加
2.空字符串" "或者0都不是null

3.1 列级添加非空约束

create table 表名(
字段名 字段类型 not null
)

CREATE TABLE student8(id INT(8),name varchar(20) NOT NULL,age int(2),sex VARCHAR(1));
DESC student8;

3.2 给已有表添加非空约束

alter table 表名 modify 字段名 字段类型 not null

ALTER TABLE student9 MODIFY name VARCHAR(20) NOT NULL;

3.3 删除非空约束

alter table 表名 modify 字段名 字段类型 [null]

ALTER TABLE student9 MODIFY name VARCHAR(20);

4.默认值约束

default
指在没有对某字段插入具体值时候会取默认值

注意
1.只能使用列级约束
2.对于使用默认值约束，如果插入的数据为"null"，则不会使用默认值，只有没有数据插入的时候，才会使用默认值

4.1 列级添加默认值约束

create table 表名(
字段名 字段类型 default value
)

CREATE TABLE student10(id int(8),name VARCHAR(20),sex VARCHAR(2) DEFAULT '男',age int(2) );
DESC student10; 

4.2 给已有表添加默认值约束

alter table 表名 modify 字段名 字段类型 default value;

ALTER TABLE student11 MODIFY sex VARCHAR(2) DEFAULT '男';

4.3 删除默认值约束

alter table 表名 modify 字段名 字段类型 ;

ALTER TABLE student11 MODIFY sex VARCHAR(2);

5.自动增长约束

auto_increment
指表中某字段的值会自动增加

注意
1.一张表中只能有一个自动增长的字段
2.配合主键一起使用，并且只适用于整数类型
3.自动增长默认的初始值1，每增加一条记录，该字段的只会增加1

5.1 创建表的时候创建自动增长约束

create table 表名(
字段名 字段类型 auto_increment
)

CREATE TABLE student12(id int(8) PRIMARY KEY auto_increment,name VARCHAR(20),sex VARCHAR(1),age int(2))

5.2 给已有表添加自动增长约束

alter table modify 字段名 字段类型 auto_increment

ALTER TABLE student13 MODIFY id int(8) auto_increment;

5.3 删除自动增长约束

alter table 表名 modify 字段名 字段类型

ALTER TABLE student13 MODIFY id INT(8);

6.外键约束

foreign key
某一张表中某字段的值依赖于另一张表中某字段的值
主要实现了数据库中的参照完整性
将两张表紧密结合，对某张表修改或者删除时，要保证数据的完整
例如：班级(t_class) 学生(t_student) 关系 1:n

6.1 创建外键约束

虽然MySQL提供了列级添加外键约束，但添加后不会生效，所以使用表级添加外键约束

create table 表名(
字段名 字段类型
[constraint 外键约束名] foreign key(字段名) references 表(字段名)
)

CREATE TABLE t_class(
	cno INT(8) PRIMARY KEY auto_increment,
	cname VARCHAR(20)
);
CREATE TABLE t_student(
	cno int(8) PRIMARY KEY auto_increment,
	cname VARCHAR(20),
	age INT(2),
	sex VARCHAR(2),
	con INT(8),
	CONSTRAINT fk_t_student_cno FOREIGN KEY(cno) REFERENCES t_class(cno)
);

6.2 在已有表中添加外键约束

alter table 表名 add [constraint 外键约束名] foreign key(字段名) references 表(字段名)

CREATE TABLE tt_class(
	cno int(8) PRIMARY KEY auto_increment,
	cname VARCHAR(20)
);
CREATE TABLE tt_student(
	sno int(8) PRIMARY KEY auto_increment,
	sname VARCHAR(20),
	stu_cno int(8)
);
ALTER TABLE tt_student ADD CONSTRAINT fk_tt_student_stu_cno FOREIGN KEY(stu_cno) REFERENCES tt_class(cno);

6.3 删除外键约束

alter table 表名 drop foreign key 外键约束名

ALTER TABLE tt_student DROP FOREIGN KEY fk_tt_student_stu_cno;

6.4 有关系的表进行删除

a.先删除有外键约束的表，再删除主表
先删除t_student表，再删除t_class

b.先删除外键约束再删除表