约束是什么鬼?来自百科的解释:
约束是一个汉语词语,读音是yuē shù,意思是指有缠缚,束缚,限制,管束,处罚等意思。古有盟约之意,如《战国策·燕策》:“坚明约束。”中约束一词。
“额。。。好像跑题了,回到正题。
”
MySQL中的约束概述
约束是一种限制,它通过限制表中的数据,来确保数据的完整性和唯一性。
“例如,在某数据表中有一个存储金额的字段,如果随便输入字符串类型的数据,那这些无效的值就毫无意义了。
”
MySQL支持以下约束:
1. 主键约束
主键约束是使用最频繁的约束,在设计数据表时,一般情况下,都会要求表中设置一个主键,主键是表的一个特殊字段,该字段能唯一标识该表中的每条信息。例如,主机信息表中的IP地址是唯一的。
2. 外键约束
外键约束经常和主键约束一起使用,用来确保数据的一致性。例如,有一个汽车店,只有宝马、奔驰、本田、丰田4个品牌,那么,你来到这个汽车店买车,就只能选择这4个品牌,不能购买其它的品牌。
3. 唯一约束
唯一约束与主键约束有一个相似的地方,就是它们都能够确保列的唯一性。与主键约束不同的是,唯一约束在一个表中可以有多个,并且设置唯一约束的列是允许有空值的,虽然只能有一个空值。
例如,在主机信息表中,要避免表中的主机名重名,就可以把主机名列设置为唯一约束。
4. 检查约束
检查约束是用来检查数据表中,字段值是否有效的一个手段。
例如,主机信息表中的IPV4地址字段是合法的32位,并且位数也是有限制的。如果随便输入一些不合法的地址,那么它就不是一个可用的IP地址了。在设置字段的检查约束时要根据实际情况进行设置,这样能够减少无效数据的输入。
5. 非空约束
非空约束用来约束表中的字段不能为空。例如,在主机表中,如果不添加主机名,那么这条记录是没有用的。
6. 默认值约束
默认值约束用来约束当数据表中某个字段不输入值时,自动为其添加一个已经设置好的值。
例如,将主机录入CMDB信息时,如果不输入主机名叫啥,那么会默认设置一个主机名(比如host001)或者输入一个“localhost”。
默认值约束通常用在已经设置了非空约束的列,这样能够防止数据表在录入数据时出现错误。
“以上 6 种约束中,一个数据表中只能有一个主键约束,其它约束可以有多个。
”
主键约束
1. 设置单字段主键
# 创建表,将hostid设置为主键 create table hosts ( hostid BIGINT UNSIGNED PRIMARY KEY, name VARCHAR(64), status INT DEFAULT 0 ); # 查看表结构 mysql> desc hosts; +--------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------+------+-----+---------+-------+ | hostid | bigint unsigned | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | status | int | YES | | 0 | | +--------+-----------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
定义主键的另外一种方式,如下:
# 创建表,将hostid设置为主键 create table hosts_new ( hostid BIGINT UNSIGNED, name VARCHAR(64), status INT DEFAULT 0, PRIMARY KEY(hostid) # 此处定义主键 ); # 查看 mysql> desc hosts_new; +--------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------+------+-----+---------+-------+ | hostid | bigint unsigned | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | status | int | YES | | 0 | | +--------+-----------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
2. 在创建表时设置联合主键
所谓的联合主键,就是这个主键是由一张表中多个字段组成的。
比如,设置学生选课数据表时,使用学生编号做主键还是用课程编号做主键呢?如果用学生编号做主键,那么一个学生就只能选择一门课程。如果用课程编号做主键,那么一门课程只能有一个学生来选。显然,这两种情况都是不符合实际情况的。
实际上设计学生选课表,要限定的是一个学生只能选择同一课程一次。因此,学生编号和课程编号可以放在一起共同作为主键,这也就是联合主键了。
“注意:当主键是由多个字段组成时,不能直接在字段名后面声明主键约束。
”
创建数据表 hosts,假设表中没有主键 id,为了唯一确定一台主机,可以把 name、hostid联合起来作为主键
mysql> create table hosts( -> hostid BIGINT UNSIGNED, -> name VARCHAR(64), -> status INT DEFAULT 0, -> PRIMARY KEY(hostid,name) -> ); Query OK, 0 rows affected (0.02 sec) mysql> desc hosts; +--------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------+------+-----+---------+-------+ | hostid | bigint unsigned | NO | PRI | NULL | | | name | varchar(64) | NO | PRI | NULL | | | status | int | YES | | 0 | | +--------+-----------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
3. 在修改表时添加主键约束
主键约束不仅可以在创建表的同时创建,也可以在修改表时添加。但是需要注意的是,设置成主键约束的字段中不允许有空值。
语法:
ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);
修改hosts表,将hostid设置为主键
# 先查看一个表,此表没有主键 mysql> desc hosts; +--------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------+------+-----+---------+-------+ | hostid | bigint unsigned | YES | | NULL | | | name | varchar(64) | YES | | NULL | | | status | int | YES | | 0 | | +--------+-----------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) # 修改表,添加主键 mysql> alter table hosts add PRIMARY KEY(hostid); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 # 再次查看 mysql> desc hosts; +--------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------+------+-----+---------+-------+ | hostid | bigint unsigned | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | status | int | YES | | 0 | | +--------+-----------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
4. 删除主键约束
当一个表中不需要主键约束时,就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。
mysql> alter table hosts drop PRIMARY KEY; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc hosts; +--------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------+------+-----+---------+-------+ | hostid | bigint unsigned | NO | | NULL | | | name | varchar(64) | YES | | NULL | | | status | int | YES | | 0 | | +--------+-----------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
“由于主键约束在一个表中只能有一个,因此不需要指定主键名就可以删除一个表中的主键约束。
”
5. 主键自增长
在MySQL中,当主键定义为自增长后,这个主键的值就不再需要用户输入数据了,而由数据库系统根据定义自动赋值。每增加一条记录,主键会自动以相同的步长进行增长。
通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长
案例
mysql> create table hosts ( -> hostid BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(64), -> status INT DEFAULT 0 -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc hosts; +--------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------+------+-----+---------+----------------+ | hostid | bigint unsigned | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | | status | int | YES | | 0 | | +--------+-----------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> insert into hosts(name,status) values('pc01',0); Query OK, 1 row affected (0.00 sec) mysql> insert into hosts(name,status) values('pc02',0); Query OK, 1 row affected (0.01 sec) mysql> insert into hosts(name,status) values('pc03',1); Query OK, 1 row affected (0.00 sec) mysql> insert into hosts(name,status) values('pc10',1); Query OK, 1 row affected (0.00 sec) mysql> insert into hosts(name,status) values('pc12',0); Query OK, 1 row affected (0.00 sec) mysql> select * from hosts; +--------+------+--------+ | hostid | name | status | +--------+------+--------+ | 1 | pc01 | 0 | | 2 | pc02 | 0 | | 3 | pc03 | 1 | | 4 | pc10 | 1 | | 5 | pc12 | 0 | +--------+------+--------+ 5 rows in set (0.00 sec) mysql>
外键约束
1. 在创建表时设置外键约束
案例:创建interface表,并在表 interface 上创建外键约束,约束名称为c_interface_1,让它的键 hostid 作为外键关联到表hosts的主键hostid
# 创建hosts表 mysql> create table hosts ( -> hostid int(4) PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(64), -> status INT DEFAULT 0 -> ); Query OK, 0 rows affected, 1 warning (0.04 sec) # 创建interface表,并在表 interface 上创建外键约束,约束名称为c_interface_1,让它的键 hostid 作为外键关联到表 hosts 的主键hostid mysql> create table interface ( -> interfaceid BIGINT UNSIGNED, -> ip VARCHAR(64) DEFAULT '127.0.0.1', -> hostid int(4), -> CONSTRAINT c_interface_1 FOREIGN KEY(hostid) REFERENCES hosts(hostid) -> ); Query OK, 0 rows affected, 1 warning (0.03 sec) # 查看interface表的创建语句 mysql> show create table interface\G; *************************** 1. row *************************** Table: interface Create Table: CREATE TABLE `interface` ( `interfaceid` bigint unsigned DEFAULT NULL, `ip` varchar(64) DEFAULT '127.0.0.1', `hostid` int DEFAULT NULL, KEY `c_interface_1` (`hostid`), CONSTRAINT `c_interface_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified mysql> mysql>
2. 删除外键约束
ALTER TABLE interface DROP FOREIGN KEY c_interface_1;
3. 在修改表时添加外键约束
ALTER TABLE interface ADD CONSTRAINT c_interface_1 FOREIGN KEY(hostid) REFERENCES hosts(hostid);
唯一约束
MySQL唯一约束(Unique Key)是指所有记录中字段的值不能重复出现。例如,为id字段加上唯一性约束后,每条记录的id值都是唯一的,不能出现重复的情况。如果主机信息表中的hostid字段,其中一条记录的hostid值为‘76549’,那么该表中就不能出现另一条记录的hostid值也为‘76549’。
唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是,唯一约束在一个表中可有多个,并且设置唯一约束的列允许有空值,但是只能有一个空值。而主键约束在一个表中只能有一个,且不允许有空值。比如,在主机信息表中,为了避免表中主机名重名,可以把主机名设置为唯一约束。
- 唯一约束的关键字:UNIQUE
1. 在创建表时设置唯一约束
语法
<字段名> <数据类型> UNIQUE
案例:创建users表,userid字段设置主键约束,name字段设置唯一约束
create table users( userid int(4) PRIMARY KEY, name varchar(20) unique, alias varchar(20) );
2. 在修改表时添加唯一约束
语法:
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
案例:将现有的users表中的alias字段设置唯一约束
alter table users add constraint c_1 unique(alias);
3. 删除唯一约束
语法
ALTER TABLE <表名> DROP INDEX <唯一约束名>;
案例:删除名为c_1的唯一约束
# 先查看创建叫的语句,就可以知道约束名叫啥 mysql> show create table users\G; *************************** 1. row *************************** Table: users Create Table: CREATE TABLE `users` ( `userid` int NOT NULL, `name` varchar(20) DEFAULT NULL, `alias` varchar(20) DEFAULT NULL, PRIMARY KEY (`userid`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified mysql> # 删除名为c_1的唯一约束 alter table users drop index c_1;
检查约束
MySQL检查约束(CHECK)是用来检查数据表中字段值有效性的一种手段,可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置,这样能够减少无效数据的输入。
1. 在创建表时设置检查约束
语法
CHECK(<检查约束>)
案例,创建users表,age字段设置检查约束,约束条件是:值大于0和小于100
# 创建users表,age字段设置检查约束 create table users( userid int(4) primary key, name varchar(20) unique, age int, check(age>0 and age<100) ); # 查看创建表语句 mysql> show create table users\G; *************************** 1. row *************************** Table: users Create Table: CREATE TABLE `users` ( `userid` int NOT NULL, `name` varchar(20) DEFAULT NULL, `age` int DEFAULT NULL, PRIMARY KEY (`userid`), UNIQUE KEY `name` (`name`), CONSTRAINT `users_chk_1` CHECK (((`age` > 0) and (`age` < 100))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
2. 在修改表时添加检查约束
语法
ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
3. 删除检查约束
语法
ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;
非空约束
MySQL 非空约束(NOT NULL)指字段的值不能为空。对于使用了非空约束的字段,如果用户在添加数据时没有指定值,数据库系统就会报错。可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。在表中某个列的定义后加上关键字 NOT NULL 作为限定词,来约束该列的取值不能为空。
比如,在主机信息表中,如果不添加IP地址,那么这条主机信息就是无效的,这时就可以为IP地址字段设置非空约束。
1. 在创建表时设置非空约束
创建表时可以使用 NOT NULL 关键字设置非空约束,具体的语法格式如下:
<字段名> <数据类型> NOT NULL;
2. 在修改表时添加非空约束
如果在创建表时忘记了为字段设置非空约束,也可以通过修改表进行非空约束的添加。
修改表时设置非空约束的语法格式如下:
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;
3. 删除非空约束
修改表时删除非空约束的语法规则如下:
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
默认值约束
默认值(Default)的完整称呼是“默认值约束(Default Constraint)”,用来指定某列的默认值。在表中插入一条新记录时,如果没有为某个字段赋值,系统就会自动为这个字段插入默认值。
例如,主机信息表中,主机位于A机房的比较多,那么机房位置就可以默认为“A”,系统就会自动为这个字段赋值为“A”。
1. 在创建表时设置默认值约束
语法
<字段名> <数据类型> DEFAULT <默认值>;
2. 在修改表时添加默认值约束
修改表时添加默认值约束的语法格式如下:
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;
3. 删除默认值约束
当一个表中的列不需要设置默认值时,就需要从表中将其删除。
修改表时删除默认值约束的语法格式如下:
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;
“好了,关于mysql的约束,今晚就此搁笔,时间有限。笔者认为,除了要学好数据库的约束之外,还要学会自我约束,比如约束自己准时睡觉,这不,我要准备睡了,不能熬夜哦!