非空约束(not null)
mysql> /*创建表时指定id列为非空约束,name列不加约束*/ mysql> create table t_test1 (id int not null,name varchar(20)) tablespace sqltestspace; Query OK, 0 rows affected (0.01 sec) mysql> desc t_test1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> /*插入测试数据(id列插入数值，name列不插入数值)*/ mysql> insert into t_test1(id) values (1); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test1; +----+------+ | id | name | +----+------+ | 1 | NULL | +----+------+ 1 row in set (0.00 sec) mysql> /*插入测试数据(id列不插入值，name列插入数值)，出现报错*/ mysql> insert into t_test1(name) values ('Tina'); ERROR 1364 (HY000): Field 'id' doesn't have a default value mysql> /*修改插入的测试数据指定id列的值*/ mysql> insert into t_test1(id,name) values (1,'Tina'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test1; +----+------+ | id | name | +----+------+ | 1 | NULL | | 1 | Tina | +----+------+ 2 rows in set (0.00 sec) mysql> /*为表t_test1的name列添加非空约束,出现报错(含有空值的列是无法添加非空约束的)*/ mysql> alter table t_test1 modify name varchar(20) not null; ERROR 1138 (22004): Invalid use of NULL value /*修复数据name列的空值再次添加约束*/ mysql> update t_test1 set name='Jack' where name is null; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_test1; +----+------+ | id | name | +----+------+ | 1 | Jack | | 1 | Tina | +----+------+ 2 rows in set (0.00 sec) mysql> alter table t_test1 modify name varchar(20) not null; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_test1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) 由上面的例子可以看出，not null 约束的列不接受 null 值 有null值的列不能添加 not null 约束 not null 约束接受重复值唯一约束(unique)
mysql> /*创建表时指定id列为唯一约束，name列不加约束*/ mysql> create table t_test2 (id int unique,name varchar(20)) tablespace sqltestspace; Query OK, 0 rows affected (0.00 sec) mysql> desc t_test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | UNI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> /*插入测试数据*/ mysql> insert into t_test2(id) values (1); Query OK, 1 row affected (0.01 sec) mysql> insert into t_test2(name) values('Tina'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test2; +------+------+ | id | name | +------+------+ | 1 | NULL | | NULL | Tina | +------+------+ 2 rows in set (0.00 sec) mysql> /*插入已存在的id数据到表中(出现报错信息)*/ mysql> insert into t_test2(id,name) values (1,'Tina'); ERROR 1062 (23000): Duplicate entry '1' for key 't_test2.id' mysql> /*修改数据重新插入测试数据*/ mysql> insert into t_test2(id,name) values (2,'Tina'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test2; +------+------+ | id | name | +------+------+ | 1 | NULL | | NULL | Tina | | 2 | Tina | +------+------+ 3 rows in set (0.00 sec) mysql> /*为name列添加唯一约束(添加约束失败，运维该列中存在重复值)*/ mysql> alter table t_test2 add unique(name); ERROR 1062 (23000): Duplicate entry 'Tina' for key 't_test2.name' mysql> /*修复表中数据重新添加约束*/ mysql> update t_test2 set name='Jack' where id is null; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> alter table t_test2 add unique(name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | UNI | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> /*为组合列创建唯一约束*/ mysql> alter table t_test2 add constraint uk_t_test2 unique(id,name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t_test2\G *************************** 1. row *************************** Table: t_test2 Create Table: CREATE TABLE `t_test2` ( `id` int DEFAULT NULL, `name` varchar(20) DEFAULT NULL, UNIQUE KEY `id` (`id`), UNIQUE KEY `name` (`name`), UNIQUE KEY `uk_t_test2` (`id`,`name`) ) /*!50100 TABLESPACE `sqltestspace` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> /*创建表时为组合列创建唯一约束*/ mysql> create table t_test3 ( -> id int, -> name varchar(20), -> constraint uk_t_test3 unique(id,name) -> ) tablespace sqltestspace; Query OK, 0 rows affected (0.01 sec) mysql> desc t_test3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | MUL | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> /*插入测试数据(单列数值重复，组合列数值不重复时可正常插入)*/ mysql> insert into t_test3 (id,name) values (1,'Tina'); Query OK, 1 row affected (0.01 sec) mysql> insert into t_test3 (id,name) values (1,'Jack'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_test3 (id,name) values (2,'Tina'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test3; +------+------+ | id | name | +------+------+ | 1 | Jack | | 1 | Tina | | 2 | Tina | +------+------+ 3 rows in set (0.00 sec) mysql> /*插入组合数据重复值会出现报错，组合键不唯一*/ mysql> insert into t_test3 (id,name) values (2,'Tina'); ERROR 1062 (23000): Duplicate entry '2-Tina' for key 't_test3.uk_t_test3' 由上面的例子我们可以看出，unique 不接受重复值 有重复值的列不能添加 unique 约束 unique 接受 null 值 组合列添加 unique 约束时，组合值唯一即可默认值(default)
mysql> /*创建测试表，设置sex列默认值(comment用来添加注释信息)*/ mysql> create table t_test4 ( -> id int, -> name varchar(20), -> sex int default 0 comment '性别(0-男,1-女,默认值0)' -> ) tablespace sqltestspace; Query OK, 0 rows affected (0.02 sec) mysql> desc t_test4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | int | YES | | 0 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> /*插入测试数据*/ mysql> insert into t_test4(id,name,sex) values (1,'Tina',1); Query OK, 1 row affected (0.00 sec) mysql> insert into t_test4(id,name) values (2,'Jack'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test4; +------+------+------+ | id | name | sex | +------+------+------+ | 1 | Tina | 1 | | 2 | Jack | 0 | +------+------+------+ 2 rows in set (0.00 sec) 由上面的例子可以看出 当插入数据时，如果不为default约束的字段赋值 那么会自动添加设置的default值至数据中主键约束(primary key)
mysql> /*创建测试表，指定id列为主键*/ mysql> create table t_test5 (id int primary key,name varchar(20)) tablespace sqltestspace; Query OK, 0 rows affected (0.01 sec) mysql> desc t_test5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> /*插入测试数据*/ mysql> insert into t_test5(id,name) values (1,'Tina'); Query OK, 1 row affected (0.00 sec) mysql> /*主键约束插入重复值会出现报错*/ mysql> insert into t_test5(id,name) values (1,'Jack'); ERROR 1062 (23000): Duplicate entry '1' for key 't_test5.PRIMARY' mysql> insert into t_test5(id,name) values (2,'Jack'); Query OK, 1 row affected (0.00 sec) mysql> /*主键约束插入null值时会出现报错*/ mysql> insert into t_test5(name) values ('Tom'); ERROR 1364 (HY000): Field 'id' doesn't have a default value mysql> insert into t_test5(id,name) values (3,'Tom'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test5; +----+------+ | id | name | +----+------+ | 1 | Tina | | 2 | Jack | | 3 | Tom | +----+------+ 3 rows in set (0.00 sec) 由上面的例子可以看出 主键约束不接受 null 值和重复值外键约束(foreign key)
mysql> /*创建测试表，指定emp_no列使用外键，参照t_test5的主键id(外键的references列必须是主键)*/ mysql> create table t_test6 ( -> dept_no int, -> emp_no int, -> dept_name varchar(20), -> constraint fk_t_test6 foreign key(emp_no) references t_test5(id) -> ) tablespace sqltestspace; Query OK, 0 rows affected (0.00 sec) mysql> desc t_test6; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | dept_no | int | YES | | NULL | | | emp_no | int | YES | MUL | NULL | | | dept_name | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> /*插入测试数据(当插入的外键数值不是参照表的主键值时会出现报错)*/ mysql> insert into t_test6(dept_no,emp_no,dept_name) values (101,10,'dev'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sqltest`.`t_test6`, CONSTRAINT `fk_t_test6` FOREIGN KEY (`emp_no`) REFERENCES `t_test5` (`id`)) mysql> /*修改外键数值为参照表中包含的值*/ mysql> insert into t_test6(dept_no,emp_no,dept_name) values (101,1,'dev'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test6; +---------+--------+-----------+ | dept_no | emp_no | dept_name | +---------+--------+-----------+ | 101 | 1 | dev | +---------+--------+-----------+ 1 row in set (0.00 sec) 由上面的例子可以看出 外键约束必须建立在主键之上 外键约束列的数值必须是参照主键中已经存在的数值自定义约束(check 检查约束)
mysql> /*创建测试表，在id列添加自定义的检查约束(id>10)*/ mysql> create table t_test7 ( -> id int, -> name varchar(20), -> check (id>10) -> ) tablespace sqltestspace; Query OK, 0 rows affected (0.01 sec) mysql> show create table t_test7\G *************************** 1. row *************************** Table: t_test7 Create Table: CREATE TABLE `t_test7` ( `id` int DEFAULT NULL, `name` varchar(20) DEFAULT NULL, CONSTRAINT `t_test7_chk_1` CHECK ((`id` > 10)) ) /*!50100 TABLESPACE `sqltestspace` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> /*插入测试数据，当id值不满足检查条件时会出现报错*/ mysql> insert into t_test7(id,name) values (5,'Tina'); ERROR 3819 (HY000): Check constraint 't_test7_chk_1' is violated. mysql> insert into t_test7(id,name) values (12,'Tina'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_test7; +------+------+ | id | name | +------+------+ | 12 | Tina | +------+------+ 1 row in set (0.00 sec)自动增长(auto_increment)
mysql> /*创建测试表，主键、唯一键自动增长*/ mysql> create table t_test8 ( -> id int primary key auto_increment, -> name varchar(20) -> ) tablespace sqltestspace; Query OK, 0 rows affected (0.00 sec) mysql> desc t_test8; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> create table t_test9 ( -> id int primary key, -> emp_no int auto_increment unique, -> name varchar(20) -> ) tablespace sqltestspace; Query OK, 0 rows affected (0.01 sec) mysql> desc t_test9; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | | | emp_no | int | NO | UNI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> /*插入测试数据(自动增长列也可以插入数值，下一个自动增长值基于当前最大数值)*/ mysql> insert into t_test8(id,name) values (4,'Tina'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_test8(name) values ('Jack'); Query OK, 1 row affected (0.01 sec) mysql> select * from t_test8; +----+------+ | id | name | +----+------+ | 4 | Tina | | 5 | Jack | +----+------+ 2 rows in set (0.00 sec)