完整性约束

原文章:https://www.cnblogs.com/majj/p/9169241.html

  1. 基本介绍

    PRIMARY KEY (PK)    #标识该字段为该表的主键,可以唯一的标识记录
    FOREIGN KEY (FK)    #标识该字段为该表的外键
    NOT NULL    #标识该字段不能为空
    UNIQUE KEY (UK)    #标识该字段的值是唯一的
    AUTO_INCREMENT    #标识该字段的值自动增长(整数类型,而且为主键)
    DEFAULT    #为该字段设置默认值
    
    UNSIGNED #无符号
    ZEROFILL #使用0填充
    
    #===========说明===============
    #1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值
    #2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值
    sex enum('male','female') not null default 'male'
    
    #必须为正值(无符号) 不允许为空 默认是20
    age int unsigned NOT NULL default 20 
    # 3. 是否是key
    主键 primary key
    外键 foreign key
    索引 (index,unique...)

     

  2. not nul 与 default

    # 是否可空,null表示空,非字符串
    # not null - 不可空
    # null - 可空
    
    # 默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
    
    
    
    create table tb1(
        nid int not null defalut 2,
        num int not null
    
    );
    mysql> create table t11(id int);# id字段默认可以为空
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> desc t11;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | YES   |          | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    row in set (0.03 sec)
    mysql> insert into t11 values(); #给t11表插一个空的值
    Query OK, 1 row affected (0.00 sec)
    
    #查询结果如下
    mysql> select * from t11;
    +------+
    | id   |
    +------+
    | NULL |
    +------+
    row in set (0.00 sec)
    
    默认值可以为空
    验证1
    mysql> create table t12(id int not null);#设置字段id不为空
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> desc t12;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | NO   |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    row in set (0.01 sec)
    
    mysql> insert into t12 values();#不能插入空
    ERROR 1364 (HY000): Field 'id' doesn't have a default value
    
    设置not null,插入值时不能为空
    设置not null,插入值时不能为空
    # 第一种情况
    mysql> create table t13(id int default 1);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> desc t13;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | YES  |           | 1              |              |
    +-------+---------+------+-----+---------+-------+
    row in set (0.01 sec)
    
    mysql> insert into t13 values();
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from t13;
    +------+
    | id   |
    +------+
    |    1  |
    +------+
    row in set (0.00 sec)
    
    
    # 第二种情况
    mysql> create table t14(id int not null default 2);
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> desc t14;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | NO      |         | 2               |             |
    +-------+---------+------+-----+---------+-------+
    row in set (0.01 sec)
    
    mysql> select * from t14;
    +----+
    | id |
    +----+
    |  2 |
    +----+
    row in set (0.00 sec)
    
    设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值
    设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值

     

  3. unique

    1. 单列唯一
      #使用约束条件unique,来对公司部门的字段进行设置。
      #第一种创建unique的方式
      #例子1:
      create table department(
          id int,
          name char(10) unique
      );
      mysql> insert into department values(1,'it'),(2,'it');
      ERROR 1062 (23000): Duplicate entry 'it' for key 'name'
      
      #例子2:
      create table department(
          id int unique,
          name char(10) unique
      );
      insert into department values(1,'it'),(2,'sale');
      
      #第二种创建unique的方式
      create table department(
          id int,
          name char(10) ,
          unique(id),
          unique(name)
      );
      insert into department values(1,'it'),(2,'sale');

       

    2. 联合唯一
      # 创建services表
      mysql> create table services(
              id int,
              ip char(15),
              port int,
              unique(id),
              unique(ip,port)
             );
      Query OK, 0 rows affected (0.05 sec)
      
      mysql> desc services;
      +-------+----------+------+-----+---------+-------+
      | Field | Type      | Null | Key | Default | Extra |
      +-------+----------+------+-----+---------+-------+
      | id        | int(11)   | YES   | UNI  | NULL       |             |
      | ip        | char(15) | YES   | MUL  | NULL       |             |
      | port    | int(11) | YES   |          | NULL       |             |
      +-------+----------+------+-----+---------+-------+
      rows in set (0.01 sec)
      
      #联合唯一,只要两列记录,有一列不同,既符合联合唯一的约束
      mysql> insert into services values
             (1,'192,168,11,23',80),
             (2,'192,168,11,23',81),
             (3,'192,168,11,25',80);
      Query OK, 3 rows affected (0.01 sec)
      Records: 3  Duplicates: 0  Warnings: 0
      
      mysql> select * from services;
      +------+---------------+------+
      | id   | ip            | port |
      +------+---------------+------+
      |    1 | 192,168,11,23 |   80 |
      |    2 | 192,168,11,23 |   81 |
      |    3 | 192,168,11,25 |   80 |
      +------+---------------+------+
      rows in set (0.00 sec)
      
      mysql> insert into services values (4,'192,168,11,23',80);
      ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'

       

  4. primary key

    在MySQL的一个表中只有唯一的一个主键,不能有多列主键,但可以有复合主键
    
    一个表中可以:
    
    单列做主键
    多列做主键(复合主键)
    
    约束:等价于 not null unique,字段的值不为空且唯一
    
    存储引擎默认是(innodb):对于innodb存储引擎来说,一张表必须有一个主键。
    介绍

     

    1. 单列主键
      # 创建t14表,为id字段设置主键,唯一的不同的记录
      create table t14(
          id int primary key,
          name char(16)
      );
      
      insert into t14 values
      (1,'xiaoma'),
      (2,'xiaohong');
      
      mysql> insert into t14 values(2,'wxxx');
      ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
      
      
      #   not null + unique的化学反应,相当于给id设置primary key
      create table t15(
          id int not null unique,
          name char(16)
      );
      mysql> create table t15(
          -> id int not null unique,
          -> name char(16)
          -> );
      Query OK, 0 rows affected (0.01 sec)
      
      mysql> desc t15;
      +-------+----------+------+-----+---------+-------+
      | Field | Type         | Null | Key | Default | Extra |
      +-------+----------+------+-----+---------+-------+
      | id        | int(11)  | NO     | PRI | NULL       |             |
      | name   | char(16) | YES  |         | NULL       |             |
      +-------+----------+------+-----+---------+-------+
      rows in set (0.02 sec)

       

    2. 复合主键
      create table t16(
          ip char(15),
          port int,
          primary key(ip,port)
      );
      
      insert into t16 values
      ('1.1.1.2',80),
      ('1.1.1.2',81);
      
      验证复合主键的使用

       

  5. auto_increment

    #约束:约束的字段为自动增长,约束的字段必须同时被key约束
    # 创建student
    create table student(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') default 'male'
    );
    
    mysql>  desc student;
    +-------+-----------------------+------+-----+---------+----------------+
    | Field | Type                  | Null | Key | Default | Extra          |
    +-------+-----------------------+------+-----+---------+----------------+
    | id    | int(11)               | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(20)           | YES  |     | NULL    |                |
    | sex   | enum('male','female') | YES  |     | male    |                |
    +-------+-----------------------+------+-----+---------+----------------+
    rows in set (0.17 sec)
    
    #插入记录
    mysql>  insert into student(name) values ('老白'),('小白');
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from student;
    +----+--------+------+
    | id | name   | sex  |
    +----+--------+------+
    |  1 | 老白   | male |
    |  2 | 小白   | male |
    +----+--------+------+
    rows in set (0.00 sec)
    
    不指定id,则自动增长
    mysql> insert into student values(4,'asb','female');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into student values(7,'wsb','female');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from student;
    +----+--------+--------+
    | id | name   | sex    |
    +----+--------+--------+
    |  1 | 老白   | male   |
    |  2 | 小白   | male   |
    |  4 | asb    | female |
    |  7 | wsb    | female |
    +----+--------+--------+
    rows in set (0.00 sec)
    
    # 再次插入一条不指定id的记录,会在之前的最后一条记录继续增长
    mysql>  insert into student(name) values ('大白');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from student;
    +----+--------+--------+
    | id | name   | sex    |
    +----+--------+--------+
    |  1 | 老白   | male   |
    |  2 | 小白   | male   |
    |  4 | asb    | female |
    |  7 | wsb    | female |
    |  8 | 大白   | male   |
    +----+--------+--------+
    rows in set (0.00 sec)
    
    也可以指定id
    也可以指定id
    mysql> delete from student;
    Query OK, 5 rows affected (0.00 sec)
    
    mysql> select * from student;
    Empty set (0.00 sec)
    
    mysql> select * from student;
    Empty set (0.00 sec)
    
    mysql> insert into student(name) values('ysb');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from student;
    +----+------+------+
    | id | name | sex  |
    +----+------+------+
    |  9 | ysb  | male |
    +----+------+------+
    row in set (0.00 sec)
    
    #应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它
    mysql> truncate student;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql>  insert into student(name) values('xiaobai');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from student;
    +----+---------+------+
    | id | name    | sex  |
    +----+---------+------+
    |  1 | xiaobai | male |
    +----+---------+------+
    row in set (0.00 sec)
    
    mysql>
    
    对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
    对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长

    清空表区分delete和truncate的区别:

    delete from t1; #如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始。

    truncate table t1;数据量大,删除速度比上一条快,且直接从零开始。

  6. foreign key

    1. 快速理解foreign key

      之前创建表的时候都是在一张表中添加记录,比如如下表:公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费。

    2. 解决方法
      1. 我们完全可以定义一个部门表

        然后让员工信息表关联该表,如何关联,即foreign key

        我们可以将上表改为如下结构:

        此时有两张表,一张是employee表,简称emp表(关联表,也就从表)。一张是department表,简称dep表(被关联表,也叫主表)
      2. 创建两张表操作:
        #1.创建表时先创建被关联表,再创建关联表
        # 先创建被关联表(dep表)
        create table dep(
            id int primary key,
            name varchar(20) not null,
            descripe varchar(20) not null
        );
        
        #再创建关联表(emp表)
        create table emp(
            id int primary key,
            name varchar(20) not null,
            age int not null,
            dep_id int,
            constraint fk_dep foreign key(dep_id) references dep(id) 
        );
        
        #2.插入记录时,先往被关联表中插入记录,再往关联表中插入记录
        
        insert into dep values
        (1,'IT','IT技术有限部门'),
        (2,'销售部','销售部门'),
        (3,'财务部','花钱太多部门');
        
        insert into emp values
        (1,'zhangsan',18,1),
        (2,'lisi',19,1),
        (3,'egon',20,2),
        (4,'yuanhao',40,3),
        (5,'alex',18,2);
        
        3.删除表
        #按道理来说,删除了部门表中的某个部门,员工表的有关联的记录相继删除。
        mysql> delete from dep where id=3;
        ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db5`.`emp`, CONSTRAINT `fk_name` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
        
        #但是先删除员工表的记录之后,再删除当前部门就没有任何问题
        
        mysql> delete from emp where dep =3;
        Query OK, 1 row affected (0.00 sec)
        
        mysql> select * from emp;
        +----+----------+-----+--------+
        | id | name     | age | dep_id |
        +----+----------+-----+--------+
        |  1 | zhangsan |  18 |      1 |
        |  2 | lisi     |  18 |      1 |
        |  3 | egon     |  20 |      2 |
        |  5 | alex     |  18 |      2 |
        +----+----------+-----+--------+
        rows in set (0.00 sec)
        
        mysql> delete from dep where id=3;
        Query OK, 1 row affected (0.00 sec)
        
        mysql> select * from dep;
        +----+-----------+----------------------+
        | id | name      | descripe             |
        +----+-----------+----------------------+
        |  1 | IT        | IT技术有限部门       |
        |  2 | 销售部    | 销售部门             |
        +----+-----------+----------------------+
        rows in set (0.00 sec)

         

      3. 上面的删除表记录的操作比较繁琐,按道理讲,裁掉一个部门,该部门的员工也会被裁掉。其实呢,在建表的时候还有个很重要的内容,叫同步删除,同步更新

        接下来将刚建好的两张表全部删除,先删除关联表(emp),再删除被关联表(dep)

        接下来:
        重复上面的操作建表
        注意:在关联表中加入
        on delete cascade #同步删除
        on update cascade #同步更新

        create table emp(
            id int primary key,
            name varchar(20) not null,
            age int not null,
            dep_id int,
            constraint fk_dep foreign key(dep_id) references dep(id) 
            on delete cascade #同步删除
            on update cascade #同步更新
        );

         

      4. 接下来的操作,就复合我们正常的生活中的情况了。
        #再去删被关联表(dep)的记录,关联表(emp)中的记录也跟着删除
        mysql> delete from dep where id=3;
        Query OK, 1 row affected (0.00 sec)
        
        mysql> select * from dep;
        +----+-----------+----------------------+
        | id | name      | descripe             |
        +----+-----------+----------------------+
        |  1 | IT        | IT技术有限部门       |
        |  2 | 销售部    | 销售部门             |
        +----+-----------+----------------------+
        rows in set (0.00 sec)
        
        mysql> select * from emp;
        +----+----------+-----+--------+
        | id | name     | age | dep_id |
        +----+----------+-----+--------+
        |  1 | zhangsan |  18 |      1 |
        |  2 | lisi     |  19 |      1 |
        |  3 | egon     |  20 |      2 |
        |  5 | alex     |  18 |      2 |
        +----+----------+-----+--------+
        rows in set (0.00 sec)
        
        #再去更改被关联表(dep)的记录,关联表(emp)中的记录也跟着更改
        
        mysql> update dep set id=222 where id=2;
        Query OK, 1 row affected (0.02 sec)
        Rows matched: 1  Changed: 1  Warnings: 0
        
        # 赶紧去查看一下两张表是否都被删除了,是否都被更改了
        mysql> select * from dep;
        +-----+-----------+----------------------+
        | id  | name      | descripe             |
        +-----+-----------+----------------------+
        |   1 | IT        | IT技术有限部门       |
        | 222 | 销售部    | 销售部门             |
        +-----+-----------+----------------------+
        rows in set (0.00 sec)
        
        mysql> select * from emp;
        +----+----------+-----+--------+
        | id | name     | age | dep_id |
        +----+----------+-----+--------+
        |  1 | zhangsan |  18 |      1 |
        |  2 | lisi     |  19 |      1 |
        |  3 | egon     |  20 |    222 |
        |  5 | alex     |  18 |    222 |
        +----+----------+-----+--------+
        rows in set (0.00 sec)

         

 

 

 

 

 

 

 

 

 

 

 

 

 

posted on 2019-08-18 18:38  信奉上帝的小和尚  阅读(166)  评论(0编辑  收藏  举报

导航