【2】约束
一、Mysql的建表约束
1.主键约束：primary key
目的：唯一确定一条记录。通过给某个字段添加约束，使得该字段不能重复且不能为空。
mysql> create table user( -> id int primary key, -> name varchar(20) -> );
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | pet | | testtype | | user | +----------------+
mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
mysql> INSERT INTO user VALUES(1,'Lily');
mysql> INSERT INTO user VALUES(1,'Lily'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
只能有一个。重复则报错。
mysql> INSERT INTO user VALUES(2,'Lily'); Query OK, 1 row affected (0.08 sec)
mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | Lily | | 2 | Lily | +----+------+
且不能为空。为空则报错。
mysql> INSERT INTO user VALUES(NULL,'Lily'); ERROR 1048 (23000): Column 'id' cannot be null
联合主键：多个字段构成主键。
上个例子中，如果要id和name同为主键怎么整？
mysql> create table user2( -> id int, -> name varchar(20), -> password varchar(20), -> primary key(id,name) -> ); Query OK, 0 rows affected (0.41 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | pet | | testtype | | user | | user2 | +----------------+ 4 rows in set (0.01 sec)
只要联合的主键值加起来不重复就可以：
mysql> INSERT INTO user2 VALUES(1,'Zhangsan','1234'); Query OK, 1 row affected (0.14 sec) mysql> INSERT INTO user2 VALUES(1,'Lisi','4321'); Query OK, 1 row affected (0.12 sec) mysql> INSERT INTO user2 VALUES(2,'Zhangsan','1234'); Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO user2 VALUES(1,'Zhangsan','1234'); ERROR 1062 (23000): Duplicate entry '1-Zhangsan' for key 'PRIMARY'
一模一样的就不行，就会报错！当然，联合主键任何一个仍不可以为空！
mysql> INSERT INTO user2 VALUES(NULL,'Zhangsan','1234'); ERROR 1048 (23000): Column 'id' cannot be null
2.自增约束AUTO_INCREMENT
mysql> create table user3( -> id int primary key auto_increment, -> name varchar(20) -> );
id是主键且为自增约束。
mysql> INSERT INTO user3(name) VALUES('Lily'); Query OK, 1 row affected (0.11 sec) mysql> select * from user3; +----+------+ | id | name | +----+------+ | 1 | Lily | +----+------+ 1 row in set (0.02 sec) mysql> INSERT INTO user3(name) VALUES('Lily'); Query OK, 1 row affected (0.11 sec) mysql> select * from user3; +----+------+ | id | name | +----+------+ | 1 | Lily | | 2 | Lily | +----+------+ 2 rows in set (0.00 sec)
第一次插入数据没有设置id的值，但自动管控id为1。第二次插入数据，自动管控id的值自增为2。
如果在创建表的时候，忘记创建主键约束了怎么办？
创建表之后，主键约束的添加、删除与修改：
mysql> create table user4( -> id int, -> name varchar(20) -> );
mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
通过ALTER table 表名 add primary key(字段) 添加主键约束：
mysql> alter table user4 add primary key(id);
mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
那又该如何删除那？
ALTER table 表名 drop primary key:
mysql> alter table user4 drop primary key;
mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
使用modify修改字段，添加主键约束：
mysql> alter table user4 modify id int primary key;
mysql> desc user4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
3.唯一约束UNIQUE
约束修饰的字段的值不可以重复：
alter table 表名 add unique(字段)
mysql> create table user5( -> id int, -> name varchar(20) -> );
mysql> alter table user5 add unique(name);
mysql> desc user5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+
像表中添加数据：
mysql> INSERT INTO user5 VALUES(1,'zhangsan'); Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO user5 VALUES(2,'zhangsan'); ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'
mysql> INSERT INTO user5 VALUES(1,'lili'); Query OK, 1 row affected (0.06 sec)
再次添加name=zhangsan不行，因为name的值不可以重复。
除了向上述建表之后通过ALTER添加约束之外，还可以在建表时就添加唯一约束：
mysql> create table user6( -> id int, -> name varchar(20), -> unique(name) -> );
或：
mysql> create table user7( -> id int, -> name varchar(20) unique -> );
添加2个唯一约束：
mysql> create table user8( -> id int, -> name varchar(20), -> unique(id,name) -> );
mysql> desc user8; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
此时，id和name值都不能重复？
mysql> INSERT INTO user8 VALUES(1,'zhang'); Query OK, 1 row affected (0.06 sec) mysql> INSERT INTO user8 VALUES(2,'zhang'); Query OK, 1 row affected (0.06 sec) mysql> INSERT INTO user8 VALUES(1,'li'); Query OK, 1 row affected (0.04 sec) mysql> INSERT INTO user8 VALUES(1,'zhang'); ERROR 1062 (23000): Duplicate entry '1-zhang' for key 'id' mysql> select * from user8; +------+-------+ | id | name | +------+-------+ | 1 | li | | 1 | zhang | | 2 | zhang | +------+-------+ 3 rows in set (0.00 sec)
由此可见，只要加起来不重复就行。
如何删除唯一约束？
alter table 表名 drop index 字段;
alter table 表名 drop 字段;
没有index，字段name会被删除：
mysql> desc user7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table user7 drop index name; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table user7 drop name; Query OK, 0 rows affected (0.57 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user7; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+
再添加回去：
mysql> alter table user7 modify name varchar(20) unique; ERROR 1054 (42S22): Unknown column 'name' in 'user7'
mysql> alter table user7 add name varchar(20) unique; Query OK, 0 rows affected (0.85 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table user7 modify id int unique; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+
总结：
（1）建表的同时添加约束；
（2）建表之后，添加： alter...add...；修改：alter...modify...；删除：alter...drop...
4.非空约束 NOT NULL
修饰的字段不能为空。
mysql> create table user9( -> id int, -> name varchar(20) NOT NULL -> );
mysql> desc user9; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+
mysql> INSERT INTO user9 (id) VALUES(1); ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> INSERT INTO user9 VALUES(1,'Zhang'); Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO user9 (name) VALUES('lisa'); Query OK, 1 row affected (0.05 sec) mysql> select * from user9; +------+-------+ | id | name | +------+-------+ | 1 | Zhang | | NULL | lisa | +------+-------+
5.默认约束default
当我们插入字段值的时候，没有传入值的字段将会使用默认值。
mysql> create table user10( -> id int, -> name varchar(20), -> age int default 22 -> ); mysql> desc user10; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | 22 | | +-------+-------------+------+-----+---------+-------+
插入数据：
mysql> INSERT INTO user10 VALUES(2,'Leo'); ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO user10(id,name) VALUES(2,'Leo'); Query OK, 1 row affected (0.06 sec) mysql> select * from user10; +------+------+------+ | id | name | age | +------+------+------+ | 2 | Leo | 22 | +------+------+------+
mysql> INSERT INTO user10 VALUES(2,'Leo',18); Query OK, 1 row affected (0.10 sec) mysql> select * from user10; +------+------+------+ | id | name | age | +------+------+------+ | 2 | Leo | 22 | | 2 | Leo | 18 | +------+------+------+
同样，也可使用add、drop、modify进行添加、删除、修改。
6.外键约束FOREIGN_KEY
此时涉及到两个表：父表与子表。主表与附表。
创建班级表，主键为id：
mysql> create table classes( -> id int, -> name varchar(20), -> primary key(id) -> );
创建学生表，主键为id：
mysql> create table students( -> id int primary key, -> name varchar(20), -> class_id int, -> foreign key(class_id) references classes(id) -> );
mysql> desc students; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | class_id | int(11) | YES | MUL | NULL | | +----------+-------------+------+-----+---------+-------+
向班级表中插入数据：
mysql> INSERT INTO classes VALUES(1,'Math'); mysql> INSERT INTO classes VALUES(2,'Bio'); mysql> INSERT INTO classes VALUES(3,'Eng'); mysql> INSERT INTO classes VALUES(4,'Gym'); mysql> select * from classes; +----+------+ | id | name | +----+------+ | 1 | Math | | 2 | Bio | | 3 | Eng | | 4 | Gym | +----+------+
向学生表中插入数据：
mysql> INSERT INTO students VALUES(1001,'Mary',1); mysql> INSERT INTO students VALUES(1002,'Lily',2); mysql> INSERT INTO students VALUES(1003,'Simo',3); mysql> INSERT INTO students VALUES(1004,'Max',4); mysql> select * from students; +------+------+----------+ | id | name | class_id | +------+------+----------+ | 1001 | Mary | 1 | | 1002 | Lily | 2 | | 1003 | Simo | 3 | | 1004 | Max | 4 | +------+------+----------+
注意的是：
1.主表（父表）classes中没有的数据值（例如班级id值没有5），在附表（子表）students中是不可以使用的；
mysql> INSERT INTO students VALUES(1005,'Lose',5); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
2.主表中的记录被附表引用，是不可以被删除的；
mysql> delete from classes where id=4; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
