MySQL Study之--MySQL约束

傷城~ 2022-08-03 03:06 187阅读 0赞

**MySQL Study之--MySQL约束**

**约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除。  **

**常用五类约束：  
not null：非空约束，指定某列不为空   
unique： 唯一约束，指定某列和几列组合的数据不能重复   
primary key：主键约束，指定某列的数据不能重复、唯一   
foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据   
check：检查，指定一个表达式，用于检验指定数据 **  
注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；     
**根据约束数据列限制，约束可分为：**  
单列约束：每个约束只约束一列   
多列约束：每个约束约束多列数据

### 1、not null ###

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。   
Null类型特征： 所有的类型的值都可以是null，包括int、float等数据类型 空字符串””是不等于null，0也不等于null   
create table temp(           
id int not null,           
name varchar(255) not null default ‘abc’,           
sex char null   
)  
上面的table加上了非空约束，也可以用alter来修改或增加非空约束   
增加非空约束   
alter table temp modify sex varchar(2) not null;     
取消非空约束   
alter table temp modify sex varchar(2) null;     
取消非空约束，增加默认值   
alter table temp modify sex varchar(2) default ‘abc’;

**案例：**

### 2、unique ###

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。 MySQL会给唯一约束的列上默认创建一个唯一索引；  
create table temp (           
id int not null,           
name varchar(25),           
password varchar(16),   
constraint uk\_name\_pwd unique(name, password)   
);   
表示用户名和密码组合不能重复   
添加唯一约束   
alter table temp add unique(name, password);   
修改唯一性约束  
alter table temp modify name varchar(25) unique;   
删除约束   
alter table temp drop index name;

**案例：**

mysql> alter table t4 add constraint uq_id unique(id);
    Query OK, 0 rows affected (0.20 sec)
    Records: 0  Duplicates: 0  Warnings: 0

### 3、primary key ###

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束， 那么这些列都不允许为空值，并且组合的值不允许重复。 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。  
MySQL的主键名总是PRIMARY， 当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。   
列模式：   
create table temp(       
id int primary key,       
name varchar(25)   
);     
create table temp2(       
id int not null,       
name varchar(25),       
pwd varchar(15),       
constraint pk\_temp\_id primary key(id)   
);     
组合模式：   
create table temp2(       
id int not null,       
name varchar(25),       
pwd varchar(15),       
constraint pk\_temp\_id primary key(name, pwd)   
);  
alter删除主键约束   
alter table temp drop primary key;     
alter添加主键   
alter table temp add primary key(name, pwd);     
alter修改列为主键   
alter table temp modify id int primary key;     
设置主键自增   
create table temp(           
id int auto\_increment primary key,           
name varchar(20),           
pwd varchar(16)   
);   
auto\_increment自增模式，设置自增后在插入数据的时候就不需要给该列插入值了。

**案例：**

mysql> create table t3 (id int primary key,
        -> name varchar(10));
    Query OK, 0 rows affected (0.08 sec)
    mysql> drop table t3;
    Query OK, 0 rows affected (0.04 sec)
    mysql> create table t3 (id int ,
        -> name varchar(10),
        -> constraint pk_t3 primary key(id));
    Query OK, 0 rows affected (0.11 sec)

mysql> alter table t3 drop primary key;
    Query OK, 0 rows affected (0.14 sec)
    Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t3 add constraint pk_t3 primary key(id);
    Query OK, 0 rows affected (0.19 sec)
    Records: 0  Duplicates: 0  Warnings: 0

mysql> create table emp1 as select * from emp;
    Query OK, 14 rows affected (0.08 sec)
    Records: 14  Duplicates: 0  Warnings: 0

### 4、foreign key ###

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。 也就是说从表的外键值必须在主表中能找到或者为空。 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据， 然后才可以删除主表的数据。还有一种就是级联删除子表数据。  
注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录， 那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。  
创建外键约束：   
主表   
create table classes(           
id int auto\_increment primary key,           
name varchar(20)   
);   
从表   
create table student(           
id int auto\_increment,           
name varchar(22),           
constraint pk\_id primary key(id),           
classes\_id int references classes(id)   
);     
通常先建主表，然后再建从表，这样从表的参照引用的表才存在。  
表级别创建外键约束：   
create table student(           
id int auto\_increment primary key,           
name varchar(25),                          
classes\_id int,           
foreign key(classes\_id) references classes(id)   
);   
上面的创建外键的方法没有指定约束名称，系统会默认给外键约束分配外键约束名称，命名为student\_ibfk\_n， 其中student是表名，n是当前约束从1开始的整数。    
指定约束名称：   
create table student(           
id int auto\_increment primary key,           
name varchar(25),           
classes\_id int,                  
constraint fk\_classes\_id foreign key(classes\_id) references classes(id)   
);     
多列外键组合，必须用表级别约束语法：   
create table classes(           
id int,           
name varchar(20),           
number int,           
primary key(name, number)   
);   
create table student(           
id int auto\_increment primary key,           
name varchar(20),           
classes\_name varchar(20),           
classes\_number int,                  
foreign key(classes\_name, classes\_number) references classes(name, number)   
);     
删除外键约束：   
alter table student drop foreign key student\_ibfk\_1;   
alter table student drop foreign key fk\_student\_id;     
增加外键约束：  
alter table student add foreign key(classes\_name, classes\_number) references classes(name, number);    
自引用、自关联（递归表、树状表）   
create table tree(           
id int auto\_increment primary key,           
name varchar(50),           
parent\_id int,           
foreign key(parent\_id) references tree(id)   
);     
级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加on delete cascade 或on delete set null，前者是级联删除，后者是将从表的关联列的值设置为null。  
create table student(           
id int auto\_increment primary key,           
name varchar(20),           
classes\_name varchar(20),           
classes\_number int,         /\*表级别联合外键\*/           
foreign key(classes\_name, classes\_number) references classes(name, number) on delete cascade  
);

**案例：**

mysql> create table dept1 as select * from dept;
    Query OK, 4 rows affected (0.07 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    mysql> desc emp1;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | empNO    | int(4)      | NO   |     | 0       |       |
    | ENAME    | varchar(10) | YES  |     | NULL    |       |
    | JOB      | varchar(9)  | YES  |     | NULL    |       |
    | MGR      | int(4)      | YES  |     | NULL    |       |
    | HIREDATE | date        | YES  |     | NULL    |       |
    | SAL      | int(7)      | YES  |     | NULL    |       |
    | COMM     | int(7)      | YES  |     | NULL    |       |
    | deptNO   | int(2)      | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    8 rows in set (0.00 sec)
    mysql> desc dept1;
    +--------+-------------+------+-----+---------+-------+
    | Field  | Type        | Null | Key | Default | Extra |
    +--------+-------------+------+-----+---------+-------+
    | deptNO | int(2)      | NO   |     | 0       |       |
    | DNAME  | varchar(14) | YES  |     | NULL    |       |
    | LOC    | varchar(13) | YES  |     | NULL    |       |
    +--------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    mysql> select * from emp1;
    +-------+--------+-----------+------+------------+------+------+--------+
    | empNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | deptNO |
    +-------+--------+-----------+------+------------+------+------+--------+
    |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
    |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
    |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
    |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
    |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
    |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
    |  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
    |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
    |  7876 | ADAMS  | CLERK     | 7788 | 1987-06-13 | 1100 | NULL |     20 |
    |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
    |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
    +-------+--------+-----------+------+------------+------+------+--------+
    14 rows in set (0.00 sec)
    mysql> select * from dept1;
    +--------+------------+----------+
    | deptNO | DNAME      | LOC      |
    +--------+------------+----------+
    |     10 | ACCOUNTING | NEW YORK |
    |     20 | RESEARCH   | DALLAS   |
    |     30 | SALES      | CHICAGO  |
    |     40 | OPERATIONS | BOSTON   |
    +--------+------------+----------+
    4 rows in set (0.00 sec)
    mysql> alter table emp1 add constraint pk_emp1 primary key(empno);
    Query OK, 0 rows affected (0.39 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> alter table dept1 add constraint pk_dept1 primary key(deptno);
    Query OK, 0 rows affected (0.12 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> alter table emp1 add constraint fk_emp1 foreign key (deptno) references dept1(deptno);
    Query OK, 14 rows affected (0.22 sec)
    Records: 14  Duplicates: 0  Warnings: 0
    mysql> delete from dept1 where deptno=10;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`prod`.`emp1`, CONSTRAINT `fk_emp1` FOREIGN KEY (`deptNO`) REFERENCES `dept1` (`deptNO`))

### 5、check ###

MySQL可以使用check约束，但check约束对数据验证没有任何作用。   
create table temp(           
id int auto\_increment,           
name varchar(20),           
age int,           
primary key(id),   
check(age > 20)   
);   
上面check约束要求age必须大于20，但没有任何作用。但是创建table的时候没有任何错误或警告。

**案例：**

mysql> create table t4 as select * from t1 where 1=2;
    Query OK, 0 rows affected (0.15 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> select * from t4;
    Empty set (0.00 sec)
    
    mysql> alter table t4 add constraint chk_id check (id <90);
    Query OK, 0 rows affected (0.07 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc t4;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(10)     | NO   |     | 0       |       |
    | name  | varchar(10) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.01 sec)
    
    mysql> insert into t4 (id) values (99);
    Query OK, 1 row affected (0.05 sec)
    
    mysql> select * from t4;             //check约束没有起作用
    +----+------+
    | id | name |
    +----+------+
    | 99 | NULL |
    +----+------+
    1 row in set (0.00 sec)

\---对于check约束，可以添加（不能删除），添加后是不起作用的，MySQL还不支持check约束，但添加不会有语法错误！