约束:constraint
在创建表的时候,可以给表中的字段加上约束来保证表中数据的完整性,有效性。
非空约束not null约束的字段不能为NULL。
只有列级约束。
mysql> create table t_vip(id int,name varchar(255) not null); Query OK, 0 rows affected (0.25 sec) mysql> desc t_vip; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(255) | NO | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.04 sec) mysql> insert into t_vip(id,name) values(1,'kd'); Query OK, 1 row affected (0.04 sec) mysql> insert into t_vip(id,name) values(2,'hl'); Query OK, 1 row affected (0.04 sec) mysql> insert into t_vip(id) values(2); 1364 - Field 'name' doesn't have a default value
可以看到name没有默认值,表示不能为空。
唯一性约束unique约束的字段不能重复,但是可以为NULL。
mysql> create table t_vip(id int,name varchar(255) unique,email varchar(255)); Query OK, 0 rows affected (0.65 sec) mysql> insert into t_vip(id,name,email) values(1,'kd','kd@123.com'); Query OK, 1 row affected (0.08 sec) mysql> select * from t_vip; +----+------+------------+ | id | name | email | +----+------+------------+ | 1 | kd | kd@123.com | +----+------+------------+ 1 row in set (0.03 sec) mysql> insert into t_vip(id,name,email) values(2,'kd','kd@123.com'); 1062 - Duplicate entry 'kd' for key 't_vip.name' mysql> insert into t_vip(id) values(2); Query OK, 1 row affected (0.07 sec) mysql> select * from t_vip; +----+------+------------+ | id | name | email | +----+------+------------+ | 1 | kd | kd@123.com | | 2 | NULL | NULL | +----+------+------------+ 2 rows in set (0.03 sec)
可以看出,name字段被unique约束,不能重复,一旦重复后会出现如下报错:
1062 - Duplicate entry 'kd' for key 't_vip.name'
但是,name字段可以为NULL,而NULL可以重复。
需求:name和email两个字段联合起来具有唯一性
mysql> create table t_vip( id int, name varchar(255), email varchar(255), unique(name,email)); Query OK, 0 rows affected (0.32 sec) mysql> desc t_vip; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(255) | YES | MUL | NULL | | | email | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.03 sec) mysql> insert into t_vip(id,name,email) values (1,'kd','kd@123.com'); Query OK, 1 row affected (0.05 sec) mysql> insert into t_vip(id,name,email) values (2,'kd','kd1@123.com'); Query OK, 1 row affected (0.04 sec) mysql> insert into t_vip(id,name,email) values (3,'kd1','kd@123.com'); Query OK, 1 row affected (0.03 sec) mysql> insert into t_vip(id,name,email) values(4,'kd','kd@123.com'); 1062 - Duplicate entry 'kd-kd@123.com' for key 't_vip.name'
从上面的示例中,可以看出两个字段联合的唯一性约束中,两个字段可以分别不同,只有两个字段完全一样的时候才会报错。
primary key(pk)
foreign key(fk)
check(mysql没有,Oracle有)
mysql> create table t_vip( id int, name varchar(255) not null unique); Query OK, 0 rows affected (0.26 sec) mysql> desc t_vip; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(255) | NO | PRI | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.03 sec)
可以看出,name是not null 和unique联合约束后,name变成了主键primary key。(注意:Oracle中不一样!)