Mysql 表的完整性约束

约束(constraint)

为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。保证表中的记录完整和有效
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

1，not null 和 default 非空和默认值

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
所有的类型的值都可以是 null，包括 int、float 等数据类型

# 非空 和 默认值
# -- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table t1(
           id int not null,
           name varchar(255) not null default  'abc',
           sex enum('male','female') default 'male'
);

#-- 修改表--增加非空约束
alter table t1 modify sex enum('male','female') not null default 'male';

# -- 取消非空约束
alter table t1 modify sex enum('male','female') null;

2，unique 唯一
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
唯一约束不允许出现重复的值，但是可以为多个null。
同一个表可以有多个唯一约束，多个列组合的约束。
在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

# -- 创建表时设置
create table t2 (id int unique);
create table t3 (id int not null unique);    # 设置了唯一+非空,就相当于设置了一个主键primary key
create table t4 (id int, name char(12), unique(id), unique(name));

# 联合唯一 ： 两个或者多个字段的内容
# 利用联合唯一 记录每一个程序的信息 ip+端口号 不能有重复
# id    程序名     ip地址          端口号
# 234   mysqld    192.168.11.44    3306
# 235   mysqld    192.168.11.22    3306
# 236   kugou     192.168.11.44    8000
create table t5 (
    id int not null unique, 
    name char(20), 
    ip char(15) not null, 
    port int not null, 
    unique(ip,port)
);

# 修改表时 添加
create table t6(
    id int not null,
    name varchar(20),
    password varchar(10)
);

# 表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)
create table t_user(
    id int(10),
    name varchar(32) not null,
    email varchar(128),
    constraint t_user_email_unique unique(email)
);
# constraint 是约束关键字，t_user_email_unique 自己取的名字

#  -- 添加唯一约束
alter table t6 add unique (name, password);

#  -- 修改唯一约束
alter table t6 modify name varchar(25) unique;

# -- 删除约束
alter table t6 drop index name;

View Code

3，primary key 和 auto_increment 主键，指定该列的值可以唯一地标识该列记录

主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
auto_increment 专门用来自动生成主键值，主键值不用用户维护，自动生成，自增数从1开始，以1递增

# -- 单一主键--列级定义
create table t7(
    id int primary key,
    name varchar(20)
);

create table t7(
    id int(10) primary key auto_increment,
    name varchar(32) not null
);

# -- 单一主键--表级定义
create table t7(
    id int(10),
    name varchar(20) not null,
    constraint t_user_id_pk primary key(id)
);

# -- (复合)联合主键-表级定义--
create table t8(
    id int,
    name varchar(20),
    pwd varchar(20),
    primary key(id, name)
);

# -- 删除主键约束
alter table t8 drop primary key;

# -- 添加主键约束
alter table t8 add primary key(id,name);

# -- 修改主键约束
alter table t8 modify id int primary key;

View Code

4，foreign key references 外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
外键值可以为 null
外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束
有了外键引用之后，表分为父表和子表
删除先删除子表数据
插入先插入父表数据

# 单列外键
# 创建一个部门表字段：部门id  部门名称 部门办公室号
create table department(
    id int unique,
    dep_name char(20)
);

# 创建一个员工表字段：员工id  name  年龄  部门id(外键)
create table employee(
    id int,
    name char(12),
    age int,
    dep_id int,
    foreign key(dep_id) references department(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,
    constraint student_id foreign key(classes_name, classes_number) references classes(name, number) 
);

# -- 删除外键约束
alter table student drop foreign key student_id;

# -- 增加外键约束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);

View Code

列级约束：对一个数据列建立的约束既可以在列定义是声明，也可以在列定义之后声明
表级约束：对多个数据列建立的约束而且只能在列定义之后声明。
SET FOREIGN_KEY_CHECKS=0 禁用外键约束
SET FOREIGN_KEY_CHECKS=1 启用外键约束

-->4.1，外键约束属性
当设置外键后，如果在没有删除子表相应记录的情况了。删除了主表中的关联记录，就会报错。解决这个情况。我们可以在设置外键的同时。设置连级删除与更新的操作
语法：on delete 【cascade|set null|no action|Restrict|Set default】

create table employee(
    id int,
    name char(12),
    age int,
    dep_id int,
    foreign key(dep_id) references department(id)
    on delete cascade 
    on update cascade
);

1. cascade 在父表上update/delete记录时，同步update/delete掉子表的匹配记录

2. set null 在父表上update/delete记录时，将子表上匹配记录的列设为null 注意子表的外键列不能为not null

3. no action 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

4. Restrict 同no action, 都是立即检查外键约束

5. Set default 父表有变更时,子表将外键列设置成一个默认的值但Innodb不能识别

posted @ 2019-04-25 17:29 __Invoker 阅读(229) 评论(0) 编辑收藏举报

刷新页面返回顶部

__Invoker

Mysql 表的完整性约束

公告