mysql表的完整性约束

阿亮 2020-01-28

mysql表的完整性约束

 为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。

约束条件与数据类型的宽度一样,都是可选参数,主要分为以下几种:

# NOT NULL :非空约束,指定某列不能为空; 
# UNIQUE : 唯一约束,指定某列或者几列组合不能重复
# PRIMARY KEY :主键,指定该列的值可以唯一地标识该列记录
# FOREIGN KEY :外键,指定该行记录从属于主表中的一条记录,主要用于参照完整性

NOT NULL

是否可空,null表示空,非字符串
not null - 不可空 表示此字段必须有内容
null - 可空

# not null 非空约束
    # create table user(
    #   username char(18) not null,
    #   password char(32) not null,
    #   age tinyint unsigned
    # )

DEFAULT

我们约束某一列不为空,如果这一列中经常有重复的内容,就需要我们频繁的插入,这样会给我们的操作带来新的负担,于是就出现了默认值的概念。

默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值

default 设置默认值
    # create table user2(
    #   username char(18) not null default 'xxxx',
    #   password char(32) not null,
    #   age tinyint unsigned default 18
    # )

UNIQUE

唯一约束,指定某列或者几列组合不能重复(一般非空且唯一当作主键)

unique 唯一约束
#create table department1(
#id int,
#name varchar(20) unique,
#comment varchar(100)
#);
mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name' #重复报错

PRIMARY KEY 主键

主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之,它是用来独一无二地确认一个表格中的每一行数据。

# primary key 主键
    # 1.一张表只能有一个主键
    # 2.主键约束了一个字段的非空且唯一
    # create table user5(
    #   id int unique not null,
    #   username char(18) primary key,
    #   password char(32),
    #   age tinyint unsigned
    # )

AUTO_INCREMENT 自增

auto_increment 自增
    # 依赖unique约束并自带not null约束
    # create table user6(
    #   id int unique auto_increment,
    #   username char(18),
    #   password char(32),
    #   age tinyint unsigned
    # )

foreign key 外键

假设我们要描述所有公司的员工,需要描述的属性有这些 : 工号 姓名 部门

公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费

解决方法: 我们完全可以定义一个部门表 然后让员工信息表关联该表,如何关联,即foreign key

#表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键,关联父表(department主键id),同步更新,同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
foreign key(dpt_id)
references department(id)
on delete cascade  # 级连删除
on update cascade # 级连更新
)engine=innodb;


#先往父表department中插入记录
insert into department values
(1,'教质部'),
(2,'技术部'),
(3,'人力资源部');


#再往子表employee中插入记录
insert into employee values
(1,'yuan',1),
(2,'nezha',2),
(3,'egon',2),
(4,'alex',2),
(5,'wusir',3),
(6,'李沁洋',3),
(7,'皮卡丘',3),
(8,'程咬金',3),
(9,'程咬银',3)
;


#删父表department,子表employee中对应的记录跟着删
mysql> delete from department where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      3 |
|  6 | 李沁洋    |      3 |
|  7 | 皮卡丘    |      3 |
|  8 | 程咬金    |      3 |
|  9 | 程咬银    |      3 |
+----+-----------+--------+
6 rows in set (0.00 sec)


#更新父表department,子表employee中对应的记录跟着改
mysql> update department set id=2 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      2 |
|  6 | 李沁洋    |      2 |
|  7 | 皮卡丘    |      2 |
|  8 | 程咬金    |      2 |
|  9 | 程咬银    |      2 |
+----+-----------+--------+

相关推荐