约束也叫完整性约束
完整性:是指数据库中存放的数据是有意义的、正确的
约束:为了保证数据的正确性和相容性,对关系模型提出的某些约束条件或者规则

注意:约束一般用于字段上

约束有哪些?
非空、唯一、默认值、主键、外键、自增
语法:字段名 数据类型[宽度|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)