数据库约束是对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。约束用于限制加入表的数据的类型，可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）
1、主键约束PRIMARY KEY
- 每张数据表只能存在一个主键
- 主键保证记录的唯一性，主键的值不重复
- 主键自动为 NOT NULL
- 可以使用一列作为主键，也可以使用两列作为主键，称之为符合主键，必须通过两个列的数据才能进行整体定位的表可以使用。
# 创建表的时候增加主键约束
mysql> create table user_key (
-> id int(11),# id int(11) primary key,
-> age int(3),
-> name varchar(30),
-> primary key (id));
Query OK, 0 rows affected (0.03 sec)
mysql> desc user_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| age | int(3) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 给已存在列添加主键约束
mysql> alter table user_key modify id int(11) primary key; # alter table user_key add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| age | int(3) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 删除主键约束
mysql> alter table user_key drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| age | int(3) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 删除NOT NULL约束
mysql> alter table user_key modify id int(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user_key;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2、唯一约束UNIQUE KEY
- 唯一约束可以保证记录的唯一性
- 唯一约束的字段可以为空值（NULL）
- 每张数据表可以存在多个唯一约束
# 创建表的时候添加唯一约束
mysql> create table user_un(
-> id int unique,
-> name char(10));
Query OK, 0 rows affected (0.02 sec)
mysql> desc user_un;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 已存在的表添加唯一约束
# alter table user_un modify phone int unique;修改
mysql> alter table user_un add unique(name); # 多个字段用逗号隔开
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user_un;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(10) | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 删除唯一约束
mysql> alter table user_un drop index phone;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user_un;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(10) | YES | UNI | NULL | |
| phone | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
3、默认约束DEFAULT
- 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
# 创建表的时候添加约束
mysql> create table user_de(
-> id int primary key,
-> name char(10) not null,
-> age int not null default '0'); # age默认为0
Query OK, 0 rows affected (0.03 sec)
mysql> desc user_de;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | NO | | 0 | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 给已存在的表添加默认值约束
mysql> alter table user_de alter column address set default 'abc';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user_de;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| age | int(11) | YES | | 1 | |
| address | varchar(100) | YES | | abc | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 修改默认值
mysql> desc user_de;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| age | int(11) | NO | | 3 | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table user_de modify age int default '1';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user_de;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| age | int(11) | YES | | 1 | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 移除默认值约束
# alter table user_de age int drop default;
mysql> alter table user_de modify name char(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user_de;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| age | int(11) | NO | | 0 | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#添加一条数据不给age值，age默认存储0
mysql> insert into user_de (id,name) values (1,"张三");
Query OK, 1 row affected (0.00 sec)
mysql> select * from user_de;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 张三 | 0 |
+----+--------+-----+
1 row in set (0.00 sec)
4、非空约束NOT NULL
- 强制列不能为 NULL 值，约束强制字段始终包含值
- 这意味着，如果不向字段添加值，就无法插入新记录或者更新记录
# 创建表时增加非空约束
mysql> create table user_no(
-> id int not null,
-> name varchar(255),
-> age int);
Query OK, 0 rows affected (0.02 sec)、
# 已存在的表增加非空约束
mysql> alter table user_no modify name varchar(255) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 取消非空约束
mysql> alter table user_no modify name varchar(255) null;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
5、外键约束FOREIGN KEY
- 保持数据的一致性，完整性
- 实现1对1或1对n关系
# 班级表
mysql> desc class;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 创建表时添加外键约束
mysql> create table student(
-> id int,
-> name varchar(50),
-> c_id int,
-> c_name varchar(50),
-> constraint fk_cid foreign key(c_id) references class(id));
Query OK, 0 rows affected (0.03 sec)
# 已存在的表添加外键约束
mysql> ALTER TABLE stu ADD CONSTRAINT fk_cid FOREIGN KEY(c_id) REFERENCES class(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 删除外键约束
# 先查看外键名
mysql> show create table stu;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu | CREATE TABLE `stu` (
`id` int(11) DEFAULT NULL,
`name` char(50) DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
KEY `fk_cid` (`c_id`),
CONSTRAINT `fk_cid` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
# fk_cid为外键名称
mysql> alter table student drop foreign key fk_cid;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
6、自增约束AUTO_INCREMENT
- 可以约束任何一个字段
- 不一定是PRIMARY KEY字段，也就是说自增的字段并不等于主键字段
- 但是PRIMARY_KEY约束的主键字段，一定是自增字段，即PRIMARY_KEY 要与AUTO_INCREMENT一起作用于同一个字段
# 创建表示添加自增约束
mysql> create table zz(
-> id int auto_increment primary key,
-> name char(50));
Query OK, 0 rows affected (0.02 sec)
# 已存在表添加自增约束
mysql> alter table zz modify id int(11) auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 删除自增约束
mysql> desc zz;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> alter table zz modify id int;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc zz;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(50) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
