数据库约束详解-优快云博客

约束

为了防止数据的准确性和可靠性

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

约束条件和数据类型的宽度一样都是可选参数，主要分为：

NOT NULL # 非空约束，指定某列（字段）不能为空。比如学生姓名
UNIQUE # 唯一约束，指定某列或者几列的组合不能重复，唯一性，可以为空。比如学生ID不可重复
primary key # 主键，用于保证该列的值可以唯一的标识该列记录，并且非空。比如学生ID
foreign key # 外键，用于限制两张表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于主表某列的值。比如学生表的class_id字段，引用于教室表的主键id
default # 用于保证该字段有默认值 比如性别

NOT NULL

NULL 表示空，非字符串

NOT NULL 表示非空

# 创建score表 字段为id int类型  并且插入数据时该字段不能为空
create table score(id int NOT NULL);

DEFAULT

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

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

# 创建score表 字段id int数据类型 默认不能为空
# 字段 id2 int数据类型 并且不能为空 如果没有指定默认值则为222
create table score(id int NOT NULL ,id2 int NOT NULL default 222);

设置严格模式：

不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值

# 直接在mysql中生效(重启失效):
set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

# 配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

UNIQUE

唯一约束，指定某列或者几列组合不能重复

示例：

# 方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);

# 方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
unique(name)
);

not null 和 uniqce联合使用

这样使用如果是表中的第一个字段，也就相当于主键效果

主键： 第一个字段不能为空并且不重复

create table t1(id int not null unique);

联合唯一

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

PRIMARY KEY

主键为了保证表中的每一条数据都是该字段的唯一值。
他是用来独一无二的确认表中的每一行数据

主键可以包含一个字段或者多个字段，但是一张表只能由一个主键
当主键包含多个栏位时，称为组合key（Composite key）,也可以称为联合主键

主键可以在创建新表时(create table 表时) 添加某个字段位primary key
或者在修改的时候使用alter table来修改表的字段为主键

表中的第一个字段 + not null + unique = primary key

单字段主键

# 方法一
# not null + unique

create table test_table(
id int NOT NULL unique,  # 相当于主键
name char(20) NOT NULL 
)

# 方法二
# 在字段后面加上 primary key

create table test_table1(
id int primary key,
name char(20) NOT NULL 
);

# 方法三
# 在所有字段后单独定义primary key

create table test_table3(
id int,
name char(20) NOT NULL,
primary key(id)
)

# 方法四
# 给已经建成的表添加约束 

create table test_table4(
id int,
name char(20)
);

alter table test_table4 modify id int primary key;

多字段主键

create table test(
id int,
name char(20),
primary key(id,name)
);

AUTO_INCREMENT

约束字段为自动增长，被约束的字段必须同时被key约束

使用方法

create table student(
id int primary key auto_increment,
name char(10) not null,
sex enum('男','女') default '男'
);

insert into student values('马海燕');
insert into student values('马海阳');
insert into student values(4,'asb','男');


# 对于自增的字段，在用delete删除后，再插入值，该字段仍然按照删除前的位置继续增长
delete from student where id = 2;
insert into student(name) values('赵振');

# 显示

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 马   | 男   |
|  3 | 赵   | 男   |
+----+------+------+
2 rows in set (0.00 sec)

# 可以通过show create table student 查看下一次自增的数字
# AUTO_INCREMENT=4

mysql> show create table student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                    |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1) NOT NULL,
  `sex` enum('男','女') DEFAULT '男',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8    |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


# 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它

mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
row in set (0.00 sec)

offset偏移量

# 在创建完表后，修改自增字段的起始值

alter table student auto_increment=3;

mysql> insert into student(name) values('alex');
Query OK, 1 row affected, 1 warning (0.35 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 马   | 男   |
|  3 | 赵   | 男   |
|  5 | a    | 男   |
+----+------+------+
3 rows in set (0.00 sec)


#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;




#设置步长
sqlserver：自增步长
    基于表级别
    create table t1（
        id int。。。
    ）engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长：
    show session variables like 'auto_inc%';
    
    #基于会话级别
    set session auth_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）


#！！！注意了注意了注意了！！！
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋
比如：设置auto_increment_offset=3，auto_increment_increment=2




mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+



create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  3 | egon1 | male |
|  8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+

步长:auto_increment_increment,起始偏移量:auto_increment_offset

FOREIKEY 外键

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

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

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

mysql> create table departments (dep_id int(4),dep_name varchar(11));
Query OK, 0 rows affected (0.02 sec)

mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 创建外键不成功
mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key 

# 设置dep_id非空，仍然不能成功创建外键
mysql> alter table departments modify dep_id int(4) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | NO   |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key constraint

# 当设置字段为unique唯一字段时，设置该字段为外键成功
mysql> alter table departments modify dep_id int(4) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc departments;                                                                                                       +----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  | UNI | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
Query OK, 0 rows affected (0.02 sec

操作实例

# 表类型必须是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 |
+----+-----------+--------+
6 rows in set (0.00 sec)

on delete

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

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

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

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

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

转载于:https://www.cnblogs.com/Hybb/p/11580468.html