约束也叫完整性约束
完整性：是指数据库中存放的数据是有意义的、正确的
约束：为了保证数据的正确性和相容性，对关系模型提出的某些约束条件或者规则
注意：约束一般用于字段上
约束有哪些？
非空、唯一、默认值、主键、外键、自增
语法：字段名 数据类型[宽度|not null|unique|default 默认值|auto_increment]
1、默认值
MariaDB [test]> create table t1(name varchar(10),sex char(10)default 'male' );
MariaDB [test]> insert into t1 values();
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> select * from t1;
+------+------+
| name | sex |
+------+------+
| NULL | male |
+------+------+
1 row in set (0.00 sec)
默认值：当用户向表中插入数据时，指定了该字段的值，那么就插入该值，否则就插入默认值
修改默认值
两种方法
MariaDB [test]> alter table t1 modity sex varchar(10) default 'jim';
MariaDB [test]> alter table t1 alter sex set default 'tom';
2、非空 not null
MariaDB [test]> select * from t1 where name is null; //查询name字段为null的行
MariaDB [test]> select * from t1 where name is not null; //查询name字段为不为null的行
MariaDB [test]> create table t2 (id int not null ,name char(10));
MariaDB [test]> insert into t2 values();
MariaDB [test]> select * from t2; //会将不允许为空的id字段转换成0
+----+------+
| id | name |
+----+------+
| 0 | NULL |
+----+------+
字段类型为字符串型，非空约束会将空值转换成空字符串
MariaDB [test]> alter table t2 modify name char(10) not null;
MariaDB [test]> select * from t2;
+----+------+
| id | name |
+----+------+
| 0 | |
+----+------+
3、唯一
MariaDB [test]> create table t3 (id int unique,name char (10));
MariaDB [test]> insert into t3 values ();
MariaDB [test]> insert into t3 values (); //唯一性约束对空值无效
MariaDB [test]> select * from t3;
+------+------+
| id | name |
+------+------+
| 1 | jim |
| 2 | jim |
| NULL | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.00 sec)
MariaDB [test]> insert into t3 values (1,'tom');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
4、自增 auto_increment
要求：
1）该字段必须是数值型
2）字段上要有唯一性索引或者主键
MariaDB [test]> create table t5 (
-> id int primary key auto_increment,name char(10));
MariaDB [test]> desc t5;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
MariaDB [test]> insert into t5 values (); //id 为1
MariaDB [test]> insert into t5 values (); //id 为2
MariaDB [test]> insert into t5 values (6); //id 为6
MariaDB [test]> insert into t5 values (); //id 为7
总结：
1.当自增字段发生断档时，值会从最大值继续自增
2.当delete删除最大值时，下一个值任然从删除之前的最大值继续自增
3.当truncate表时，值从1开始重新计算
5、主键
主键是表中的特殊字段，这个字段能够唯一标识表中的每一条记录。
一张表只能有一个主键
主键的用途：快速定位数据
主键要满足的条件：非空且唯一
primary key == not null + unique
1）使用单个字段做主键
a、在字段后直接指定主键约束(列级约束，默认值为null)
MariaDB [test]> create table t6 (id int primary key,age int , name char(10));
MariaDB [test]> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [test]> insert into t6 values (1,15,'jim');
MariaDB [test]> insert into t6 values (1,16,'tom');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
MariaDB [test]> insert into t6 values();
MariaDB [test]> insert into t6 values();
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
b、整张表的所有字段都定义完成之后再来指定主键(表级约束，默认值是0)
MariaDB [test]> create table t8 ( id int,name char(10),primary key (id));
MariaDB [test]> desc t8;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
MariaDB [test]> insert into t8 values ();
MariaDB [test]> insert into t8 values ();
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
MariaDB [test]> insert into t8 values (1,'jim');
MariaDB [test]> insert into t8 values (1,'tom');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
2）多个字段做联合主键
注意：联合主键只能在所有字段都定义完成之后，才能定义主键。
MariaDB [test]> desc mysql.user\G; //host和user字段做联合主键
***************** 1. row ************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
****************** 2. row ************
Field: User
Type: char(16)
Null: NO
Key: PRI
Default:
Extra:
******************* 3. row ************
Field: Password
Type: char(41)
Null: NO
Key:
Default:
Extra:
HOST NAME PASSWORD
127.0.0.1 root 123
127.0.0.1 tom 123
127.0.0.1 root 456
192.168.1.2 root 456
MariaDB [test]> create table t8 ( HOST char(15),NAME char(10),PASSWORD char(50) ,primary key (HOST,NAME));
6、外键 foreign key
外键：一个表的数据依赖另一张表的主键列的数据，如果在主键列没有出现的值，是不能够出现在外键字段的。
创建外键的条件：
1）存储引擎必须是innodb
2）相关联字段数据类型要一致
3）最好在外键列上建索引(目的是为了减少扫描范围，不创建也可以，值是影响性能)
例：
class 班级表
student 学生表
class 父表 student 子表
CLASS_ID NAME AGE
ID CLASS 1 jim 18
1 yun 0215 1 tom 19
2 yun 0308 2 xiao 20
3 yun 0316 2 ming 21
4 yun 0411 5 hong 19
创建父表
MariaDB [test]> create table class (ID int primary key,class char(20));
创建子表
MariaDB [test]> create table student (CLASS_ID int,NAME char(20),AGE int,foreign key(CLASS_ID) references class(ID));
向父表内插入数据
MariaDB [test]> insert into class values(1,'yun0215'),
-> (2,'yun0308'),
-> (3,'yun0316'),
-> (4,'yun0411');
MariaDB [test]> select * from class;
+----+---------+
| ID | class |
+----+---------+
| 1 | yun0215 |
| 2 | yun0308 |
| 3 | yun0316 |
| 4 | yun0411 |
+----+---------+
向字表内插入数据
MariaDB [test]> insert into student values
-> (1,'jim',18),
-> (1,'tom',19),
-> (2,'xiao',20);
MariaDB [test]> insert into student values (5,'hong',19); //插入父表中不存在的班级号
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`))
MariaDB [test]> delete from class where ID=1; //删除父表中有外键依赖的数据
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`))
总结：
1）子表中的关联数据依赖于父表，不能向子表中插入父表中不存在的值
2）不能删除父表中被子表所依赖的记录
删除父表中被依赖的行的方法：
1）删除外键约束
2）指定级联操作的选项
on delete cascade 级联删除
on update cascade 级联更新
MariaDB [test]> drop table student;
MariaDB [test]> create table student (CLASS_ID int ,NAME char(20),AGE int,foreign key(CLASS_ID) references class (ID) on delete cascade on update cascade);
MariaDB [test]> insert into student values
-> (1,'jim',18),
-> (1,'tom',19),
-> (2,'xiao',20),
-> (2,'ming',21);
MariaDB [test]> select * from student;
+----------+------+------+
| CLASS_ID | NAME | AGE |
+----------+------+------+
| 1 | jim | 18 |
| 1 | tom | 19 |
| 2 | xiao | 20 |
| 2 | ming | 21 |
+----------+------+------+
MariaDB [test]> delete from class where ID=2;
MariaDB [test]> select * from class;
+----+---------+
| ID | class |
+----+---------+
| 1 | yun0215 |
| 3 | yun0316 |
| 4 | yun0411 |
+----+---------+
MariaDB [test]> select * from student;
+----------+------+------+
| CLASS_ID | NAME | AGE |
+----------+------+------+
| 1 | jim | 18 |
| 1 | tom | 19 |
+----------+------+------+
MariaDB [test]> update class set ID=2 where ID=1;
MariaDB [test]> select * from class;
+----+---------+
| ID | class |
+----+---------+
| 2 | yun0215 |
| 3 | yun0316 |
| 4 | yun0411 |
+----+---------+
MariaDB [test]> select * from student;
+----------+------+------+
| CLASS_ID | NAME | AGE |
+----------+------+------+
| 2 | jim | 18 |
| 2 | tom | 19 |
+----------+------+------+
总结：有了级联删除和级联更新选项，父表中的数据发生删除或者更新时，子表中相关数据也会发生相应的变化。
删除外键
语法：
alter table 表名 drop foreign key 外键的名字
查看外键的名字
MariaDB [test]> show create table student\G; //红色字体为外键的名字
**************** 1. row **************
Table: student
Create Table: CREATE TABLE `student` (
`CLASS_ID` int(11) DEFAULT NULL,
`NAME` char(20) DEFAULT NULL,
`AGE` int(11) DEFAULT NULL,
KEY `CLASS_ID` (`CLASS_ID`),
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
ERROR: No query specified
MariaDB [test]> alter table student drop foreign key student_ibfk_1;
MariaDB [test]> show create table student\G;
*************** 1. row ***************
Table: student
Create Table: CREATE TABLE `student` (
`CLASS_ID` int(11) DEFAULT NULL,
`NAME` char(20) DEFAULT NULL,
`AGE` int(11) DEFAULT NULL,
KEY `CLASS_ID` (`CLASS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
