本文详细介绍了数据库中的四种重要约束：NOTNULL、UNIQUE、PRIMARYKEY和FOREIGNKEY。NOTNULL确保字段不允许为空，UNIQUE保证字段或字段组合的唯一性，PRIMARYKEY作为表的唯一标识，FOREIGNKEY实现表间的数据关联。通过示例演示了如何创建和使用这些约束，并讨论了它们在实际操作中的应用和限制。
一、概览
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：
| 约束条件 | 解释 |
|---|---|
| NOT NULL | 非空约束，指定某列不能为空； |
| UNIQUE | 唯一约束，指定某列或者几列组合不能重复 |
| PRIMARY KEY | 主键，指定该列的值可以唯一地标识该列记录 |
| FOREIGN KEY | 外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性 |
| UNSIGNED | 仅针对数字类型，无符号形式 |
二、NOT NULL
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
2.1 not null实例
create table t12 (id int not null);
# Query OK, 0 rows affected (0.02 sec)
select * from t12;
# Empty set (0.00 sec)
desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
row in set (0.00 sec)
#不能向id列插入空元素。
insert into t12 values (null);
# ERROR 1048 (23000): Column 'id' cannot be null
insert into t12 values (1);
# Query OK, 1 row affected (0.01 sec)
2.2 DEFAULT
我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。
2.3 not null + default 示例
create table t13 (id1 int not null,id2 int not null default 222);
# Query OK, 0 rows affected (0.01 sec)
desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | NO | | NULL | |
| id2 | int(11) | NO | | 222 | |
+-------+---------+------+-----+---------+-------+
# 只向id1字段添加值，会发现id2字段会使用默认值填充
insert into t13 (id1) values (111);
# Query OK, 1 row affected (0.00 sec)
select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
+-----+-----+
# id1字段不能为空，所以不能单独向id2字段填充值；
insert into t13 (id2) values (223);
# ERROR 1364 (HY000): Field 'id1' doesn't have a default value
# 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
insert into t13 (id1,id2) values (112,223);
# Query OK, 1 row affected (0.00 sec)
select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
| 112 | 223 |
+-----+-----+
2.4 not null不生效
设置严格模式：
不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值
直接在mysql中生效(重启失效):
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
唯一约束，指定某列或者几列组合不能重复。
3.1 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)
);
insert into department1 values(1,'IT','技术');
# Query OK, 1 row affected (0.00 sec)
insert into department1 values(1,'IT','技术');
# ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
3.2 not null 和 unique 的结合
create table t1(id int not null unique);
# Query OK, 0 rows affected (0.02 sec)
desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
# row in set (0.00 sec)
3.3 联合唯一（多个字段）
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);
insert into service values
(1,'nginx','192.168.0.10',80),
(2,'haproxy','192.168.0.20',80),
(3,'mysql','192.168.0.30',3306);
# Query OK, 3 rows affected (0.01 sec)
# Records: 3 Duplicates: 0 Warnings: 0
insert into service(name,host,port) values('nginx','192.168.0.10',80);
# ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
四、PRIMARY KEY
主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。
注意：在 InnoDB 存储引擎-----创建表的时候，如果没有指定主键，但是有 'not null unique’的键，会自动变成主键；如果都没的时候，会采用内部隐藏的字段，但是我们无法用到
4.1 单字段主键
4.1.1 在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);
desc department2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
# rows in set (0.00 sec)
4.1.2 在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
primary key(id)
); #创建主键并为其命名pk_name
desc department3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
# rows in set (0.01 sec)
4.1.3 给已经建成的表添加主键约束
create table department4(
id int,
name varchar(20),
comment varchar(100));
# Query OK, 0 rows affected (0.01 sec)
desc department4;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
# rows in set (0.01 sec)
alter table department4 modify id int primary key;
# Query OK, 0 rows affected (0.02 sec)
# Records: 0 Duplicates: 0 Warnings: 0
desc department4;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
# rows in set (0.01 sec)
4.2 多字段主键
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);
desc service;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | NULL | |
| port | char(5) | NO | PRI | NULL | |
| service_name | varchar(10) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
# rows in set (0.00 sec)
insert into service values
('172.16.45.10','3306','mysqld'),
('172.16.45.11','3306','mariadb');
# Query OK, 2 rows affected (0.00 sec)
# Records: 2 Duplicates: 0 Warnings: 0
insert into service values ('172.16.45.10','3306','nginx');
# ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
4.3 AUTO_INCREMENT
约束字段为自动增长，一般专门给主键使用的
主键的自增，是不受已经存在的数据的删除的影响，只会一直增加
4.3.1 不指定自增的数字
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
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 | |
+-------+-----------------------+------+-----+---------+----------------+
insert into student(name) values('nick'),('tank');
select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | nick | male |
| 2 | tank | male |
+----+------+------+
4.3.1 指定自增的数字
insert into student values(4,'asb','female');
# Query OK, 1 row affected (0.00 sec)
insert into student values(7,'wsb','female');
# Query OK, 1 row affected (0.00 sec)
select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | nick | male |
| 2 | tank | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+------+--------+
4.3.1 删除自增的字段
字段仍按照删除 (delete from )前的位置继续增长 ，想要完全清空—(truncate清空表)
delete from student;
# Query OK, 4 rows affected (0.00 sec)
select * from student;
# Empty set (0.00 sec)
insert into student(name) values('ysb');
select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 8 | ysb | male |
+----+------+------+
#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
truncate student;
# Query OK, 0 rows affected (0.01 sec)
insert into student(name) values('nick');
# Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | nick | male |
+----+------+------+
row in set (0.00 sec)
4.4 offset偏移量（了解）
#在创建完表后，修改自增字段的起始值
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
alter table student auto_increment=3;
show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
insert into student(name) values('nick');
Query OK, 1 row affected (0.01 sec)
select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 3 | nick | male |
+----+------+------+
row in set (0.00 sec)
mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
#也可以创建表时指定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('nick1'),('nick2'),('nick3');
mysql> select * from student;
+----+-------+------+
| id | name | sex |
+----+-------+------+
| 3 | nick1 | male |
| 8 | nick2 | male |
| 13 | nick3 | male |
+----+-------+------+
步长:auto_increment_increment,起始偏移量:auto_increment_offset
五、FOREIGN KEY
假设我们要描述所有公司的员工，需要描述的属性有这些 ： 工号 姓名 部门
公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费
解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key
5.1 创造外键的条件
语法：create table 表名(多的一方) ( ...... ,foreign key (字段名) references 关联的表名( 被关联的表中的字段名 ) );
- 要分析表与表之间是什么关系：一对一，一对多，多对多
- 在"多"的一方中，添加外键字段(一对一的关系下则需要加上 unique)`
- 外键字段只能添加已经存在的值，不能随意添加
- 在创建表的时候一定要先创建被关联的表，不然外键无法关联
- 所关联的外键在被关联的表中一般是主键—不可重复且非空的
- 表类型必须是innodb存储引擎
先创建部门表–被关联的表
mysql> create table departments (dep_id int(4),dep_name varchar(11));
mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id | int(4) | YES | | NULL | |
| dep_name | varchar(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
再创建需要关联外键的表
所关联的外键在被关联的表中一般是主键—唯一，不可重复
# 直接创建---创建失败
create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
# 设置dep_id非空，仍然不能成功创建外键
alter table departments modify dep_id int(4) not null;
desc departments;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id | int(4) | NO | | NULL | |
| dep_name | varchar(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
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唯一字段时，设置该字段为外键成功
alter table departments modify dep_id int(4) unique;
desc departments; +----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id | int(4) | YES | UNI | NULL | |
| dep_name | varchar(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
5.2 级连更新，级连删除
如果我想要修改 被关联的外键字段，例如：删除被关联表中的主键，而这个主键被别的表当做外键，那么被关联的表中的数据就会出现问题；
如何做到，修改被关联的主键的时候，别的表中关联的外键的信息也跟着修改呢？
级联删除加上后，若外键字段的表中所关联的主键被删除之后，关联的表的中的数据也会被删除
create table staff_info (
s_id int,
name varchar(20),
dep_id int,
foreign key(dep_id) references departments(dep_id) on delete cascade on update cascade
);
# 在后面加上 on delete cascade on update cascade
扫一扫
977
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
