06.约束

约束

保证数据的完整性和一致性 表级约束和列级约束 约束类型： NOT NULL（非空约束） PRIMARY KEY（主键约束） UNIQUE KEY（唯一约束） DEFAULT（默认约束） FOREIGN KEY（外键约束）

非空约束(NOT NULL)

创建表 其中 not_null字段不许为空

mysql> CREATE TABLE null_test( -> not_null VARCHAR(20) NOT NULL, -> can_null VARCHAR(20) NULL, -> null_ VARCHAR(20) -> ); Query OK, 0 rows affected (0.39 sec)

查看表结构

mysql> DESC null_test; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | not_null | varchar(20) | NO | | NULL | | | can_null | varchar(20) | YES | | NULL | | | null_ | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

后两个为NULL，插入正常

mysql> INSERT null_test VALUES('tom',NULL,NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT*FROM null_test; +----------+----------+-------+ | not_null | can_null | null_ | +----------+----------+-------+ | tom | NULL | NULL | +----------+----------+-------+ 1 row in set (0.00 sec)

第一个为NULL，插入异常

mysql> INSERT null_test VALUES(NULL,'jojo',NULL); ERROR 1048 (23000): Column 'not_null' cannot be null

主键约束（PRIMARY KEY）

主键（PRIMARY KEY）

主键约束 每张表只能有一个主键 主键字段值不能相同 主键自动NOT NULL 自增长依附于主键，但主键不一定需要自增长

自增长错误

mysql> CREATE TABLE unique_id( -> id SMALLINT UNSIGNED AUTO_INCREMENT, -> username VARCHAR(20) -> ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

自增长的字段必须为主键

mysql> CREATE TABLE unique_id( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) -> ); Query OK, 0 rows affected (0.06 sec) mysql> DESC unique_id; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | +----------+----------------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)

插入数据，主键字段值自增长

mysql> INSERT unique_id(username) VALUES(one); ERROR 1054 (42S22): Unknown column 'one' in 'field list' mysql> INSERT unique_id(username) VALUES('one'); Query OK, 1 row affected (0.00 sec) mysql> INSERT unique_id(username) VALUES('two'); Query OK, 1 row affected (0.00 sec) mysql> INSERT unique_id(username) VALUES('three'); Query OK, 1 row affected (0.00 sec) mysql> SELECT*FROM unique_id; +----+----------+ | id | username | +----+----------+ | 1 | one | | 2 | two | | 3 | three | +----+----------+ 3 rows in set (0.00 sec)

id设为了主键，当001存在，再插入001，就会报错

mysql> CREATE TABLE key_test( -> id SMALLINT UNSIGNED PRIMARY KEY, -> username VARCHAR(20) -> ); Query OK, 0 rows affected (0.39 sec) mysql> INSERT key_test VALUES(001,'dog'); Query OK, 1 row affected (0.00 sec) mysql> INSERT key_test VALUES(002,'dog'); Query OK, 1 row affected (0.01 sec) mysql> INSERT key_test VALUES(001,'cat'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

唯一约束（UNIQUE KEY）

可为NULL 一表可存在多个

username 设置了唯一约束，当重复时，插入异常

mysql> CREATE TABLE unique_test( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) UNIQUE KEY, -> age TINYINT UNSIGNED -> ); Query OK, 0 rows affected (0.06 sec) mysql> DESC unique_test; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | UNI | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> INSERT unique_test(username,age) VALUES('dog',22); Query OK, 1 row affected (0.00 sec) mysql> INSERT unique_test(username,age) VALUES('cat',22); Query OK, 1 row affected (0.00 sec) mysql> INSERT unique_test(username,age) VALUES('dog',12); ERROR 1062 (23000): Duplicate entry 'dog' for key 'username'

外键约束(FOREIGN KEY)

父表和子表必须使用相同储存引擎 数据表的储存引擎只能是InnoDB 外键列和参照列必须由相似的数据类型 外键列和参照列必须创建索引 创建省份表 mysql> CREATE TABLE province( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> p_name VARCHAR(20) NOT NULL -> ); Query OK, 0 rows affected (0.03 sec) mysql> DESC province; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | p_name | varchar(20) | NO | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)

查询建表语句，可见使用了InnoDB

mysql> SHOW CREATE TABLE province; +----------+------------------------------------------- | Table | Create Table +----------+------------------------------------------- | province | CREATE TABLE `province` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `p_name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+------------------------------------------- 1 row in set (0.00 sec)

类型不统一，不能创建外键约束表

mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL, -> p_id BIGINT, -> FOREIGN KEY(p_id)REFERENCES province(id) -> ); ERROR 1215 (HY000): Cannot add foreign key constraint

统一后，创建成功

mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL, -> p_id SMALLINT UNSIGNED, -> FOREIGN KEY(p_id)REFERENCES province(id) -> ); Query OK, 0 rows affected (0.39 sec)

查看province表的索引（\G以网格形式，不加则以表的形式，太长）

mysql> SHOW INDEXES FROM province \G; *************************** 1. row *************************** Table: province Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec)

查看users表的索引

*************************** 1. row *************************** Table: users Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: users Non_unique: 1 Key_name: p_id Seq_in_index: 1 Column_name: p_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.01 sec)

外键约束参照操作

CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行 SET NULL:从父表删除或更新行，并设置子表中外键列为NULL。（需子表列没有指定NOT NULL） RESTRICT：拒绝对父表的删除或更新操作 NO ACTION：标准SQL关键字

CASCADE测试：

建表user1

mysql> CREATE TABLE user1( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL, -> p_id SMALLINT UNSIGNED, -> FOREIGN KEY(p_id)REFERENCES province(id) ON DELETE CASCADE -> ); Query OK, 0 rows affected (0.06 sec)

插入记录：必须在父表插入数据，插入三个省份

mysql> INSERT province(p_name) VALUES('福建省'); Query OK, 1 row affected (0.00 sec) mysql> INSERT province(p_name) VALUES('山东省'); Query OK, 1 row affected (0.00 sec) mysql> SELECT*FROM province; +----+-----------+ | id | p_name | +----+-----------+ | 1 | 安徽省 | | 2 | 福建省 | | 3 | 山东省 | +----+-----------+ 3 rows in set (0.00 sec)

在子表中插入记录：（若超出父表范围，会插入失败（也会占一个Id））

mysql> INSERT user1(username,p_id) VALUES('张风',1); Query OK, 1 row affected (0.00 sec) mysql> INSERT user1(username,p_id) VALUES('toly',10); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zoom`.`user1`, CONSTRAINT `user1_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `province` (`id`) ON DELETE CASCADE) mysql> INSERT user1(username,p_id) VALUES('捷特',2); Query OK, 1 row affected (0.00 sec) mysql> INSERT user1(username,p_id) VALUES('龙少',1); Query OK, 1 row affected (0.00 sec) mysql> SELECT*FROM user1; +----+----------+------+ | id | username | p_id | +----+----------+------+ | 1 | 张风 | 1 | | 3 | 捷特 | 2 | | 4 | 龙少 | 1 | +----+----------+------+ 3 rows in set (0.00 sec)

删除province 表中id是2和3的行，结果子表相应行也被删除

mysql> DELETE FROM province WHERE id=2; Query OK, 1 row affected (0.01 sec) mysql> DELETE FROM province WHERE id=3; Query OK, 1 row affected (0.00 sec) mysql> SELECT*FROM province; +----+-----------+ | id | p_name | +----+-----------+ | 1 | 安徽省 | +----+-----------+ 1 row in set (0.00 sec) mysql> SELECT*FROM user1; +----+----------+------+ | id | username | p_id | +----+----------+------+ | 1 | 张风 | 1 | | 4 | 龙少 | 1 | +----+----------+------+ 2 rows in set (0.00 sec)

转载于:https://www.cnblogs.com/toly-top/p/9782028.html