MySQL建表约束
约束包括:
- 主键约束(自增约束)
- 外键约束
- 唯一约束
- 非空约束
- 默认约束
1、主键约束
它能够唯一确定一张表中的一条记录,也就是我们给某个字段添加约束,就可以使得该字段不重复且不为空。
mysql> create table user(
-> id int primary key, #添加一个主键约束
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.88 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
| testtype |
| user |
+----------------+
3 rows in set (0.01 sec)
向表里添加数据:
mysql> INSERT INTO user VALUES(1,'吴亦凡');
Query OK, 1 row affected (0.62 sec)
# 但是再输入相同的内容时,mysql会提示你重复了,因为有了主键约束!!
mysql> INSERT INTO user VALUES(1,'吴亦凡');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
这样就可以添加了
mysql> INSERT INTO user VALUES(2,'吴亦凡');
Query OK, 1 row affected (0.17 sec)
id不可以为空,主建约束是为了唯一确定一张表中的一条记录,id不可以为空。
mysql> INSERT INTO user VALUES(NULL,'吴亦凡');
ERROR 1048 (23000): Column 'id' cannot be null
下面介绍一种概念 - - 联合主键
mysql> create table user2(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key (id,name)
-> );
Query OK, 0 rows affected (1.13 sec)
其中id和name只要他们两个加起来不重复就OK,例子如下:
mysql> insert into user2 values(1,'张三','123');
#给user2表添加数据
Query OK, 1 row affected (0.58 sec)
mysql> insert into user2 values(1,'张三','123');
#给user2表添加相同数据
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'PRIMARY'
mysql> insert into user2 values(2,'张三','123');
#id和名字只要有一个不重复就行了
Query OK, 1 row affected (0.42 sec)
mysql> insert into user2 values(2,'李四','123');
Query OK, 1 row affected (0.55 sec)
mysql> select * from user2;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | 张三 | 123 |
| 2 | 张三 | 123 |
| 2 | 李四 | 123 |
+----+--------+----------+
3 rows in set (0.00 sec)
大家可以理解成 ‘’或‘’,注意 联合主键任何一个键都不能为空!!!
联合主键:就是用多个字段一起作为一张表的主键.
2、 自增约束
AUTO_INCREMENT
当插入第一条记录时,自增字段没有给定一个具体值,可以不赋值 可以写成DEFAULT/NULL,那么以后插入字段的时候,该自增字段就是从1开始,每插入一条记录,该自增字段的值增加1。当插入第一条记录时,给自增字段一个具体值,那么以后插入的记录在此自增字段上的值,就在第一条记录该自增字段的值的基础上每次增加1。
例子如下:
mysql> create table user3(
-> id int primary key AUTO_INCREMENT,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.95 sec)
mysql> insert into user3(name) values('zhangsan'); #添加一个数据
Query OK, 1 row affected (0.63 sec)
mysql> select * from user3;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec) #这里发现默认给了id=1
mysql> insert into user3(name) values('zhangsan'); #再添加一个
Query OK, 1 row affected (0.71 sec)
mysql> select * from user3;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)
自增约束------就是管控id的增长
倘若我们建表的时候忘记创建主键约束怎么办?
alter table +表+ add primary key (id);
mysql> create table user4(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.90 sec)
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.10 sec)
mysql>
从上面可以看到主键是空的(key)
mysql> alter table user4 add primary key (id);
Query OK, 0 rows affected (1.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>
这里添加了主键,那嘛如何删除呢?
mysql> alter table +表+ drop primary key;
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (1.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
这里再查询下表的信息,发现主键约束已经被删除了
3、唯一约束
约束修饰字段的值 不可以 重复
我们先创建一个表
mysql> create table user5( #创建表的时候没有给约束
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.66 sec)
mysql> alter table user5 add unique(name); #添加一个唯一约束
除了先创表再添加约束,也可以在创表的时候就添加约束
mysql> create table user6(
-> id int,
-> name varchar(20),
-> unique(name)
-> );
Query OK, 0 rows affected (0.87 sec)
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
再看一眼user5,效果是一样的·
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
其中name的key中的UNI表示name不可重复~~
mysql> insert into user5 values(1,'zhangsan'); #添加一个数据
Query OK, 1 row affected (0.61 sec)
mysql> insert into user5 values(1,'zhangsan'); #不可以重复添加
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'
mysql> insert into user5 values(1,'lisi');
Query OK, 1 row affected (0.17 sec)
mysql> select * from user5;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 1 | lisi |
+------+----------+
2 rows in set (0.00 sec)
这里就有一个疑惑,这不和我们前面学的主键约束很像吗?
简单来说,就是主键约束不允许为空,而唯一约束可以为空,他们俩的相同之处就是都不可以重复。
mysql> insert into user5 values(NULL,'NULL');
Query OK, 1 row affected (0.68 sec)
mysql> select * from user5;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 1 | lisi |
| NULL | wanger |
| NULL | NULL |
+------+----------+
4 rows in set (0.00 sec)
上面显示,唯一约束可以为空(NULL)而上面的主键约束就不可以为空,
这里注意
mysql> insert into user5 values(1,'NULL');
ERROR 1062 (23000): Duplicate entry 'NULL' for key 'name'
这就很有灵性,不能存在两个唯一约束为空,我的理解是,这正是唯一性的原则。在唯一约束中 唯一约束列只能包含一个NULL值。
这里有个细节,就是在创建表的时候
可以像刚才那样,也可以这样:
mysql> create table user7(
-> id int,
-> name varchar(20) unique
-> );
但是这样只能设置一个唯一约束(或者在id int 后也加上unique)
mysql> create table user7(
-> id int,
-> name varchar(20),
-> unique(id,name)
-> );
Query OK, 0 rows affected (0.85 sec)
这样id 加 name都是不可以重复的了,其实这就和联合主键有相似了
注意这里又有区别了,唯一约束可以设置多个唯一键,下面我们分开看:
倘若我们这样设置表的唯一主键unique(id,name)这就和前面的联合主键效果是一样的。
但是我们要是这样设置,分别设置唯一约束,就和联合主键不同了,这样id和name分别都不可以重复了
mysql> create table user8(
-> id int unique,
-> name varchar(20) unique
-> );
如何删除唯一约束呢?
mysql> alter table +表名+ drop index +要删除的约束(name);
mysql> alter table user6 drop index name;
Query OK, 0 rows affected (0.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4、非空约束
保证修饰的字段不能为空,NULL 。在建表的时候加上not null
mysql> create table user9(
-> id int,
-> name varchar(20) not null #加上了not null
-> );
Query OK, 0 rows affected (0.89 sec)
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
可以看到在Null,那一列name上面是NO
5、默认约束
就是当我们插入字段值的时候,如果没有传值,就会使用默认值。
mysql> create table user10(
-> id int,
-> name varchar(20),
-> age int default 10 #default后面加上默认值
-> );
Query OK, 0 rows affected (1.15 sec)
mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
可以看到age的Default的为10,实例如下:
mysql> insert into user10 (id,name) values(1,'zhangsan');
#并没有给age传值,如果写了age就按照写的值为准
Query OK, 1 row affected (0.55 sec)
mysql> select * from user10;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 10 |
+------+----------+------+
1 row in set (0.00 sec)
6、外键约束
涉及到两个表:主表和副表(也可以说是父表和子表),一个表引用另一个表
下面我们建两个表:
班级表
mysql> create table classes(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.88 sec)
学生表
mysql> create table students(
-> id int primary key,
-> name varchar(20),
-> class_id int,
-> foreign key(class_id) references classes(id)
-> );
Query OK, 0 rows affected (1.20 sec)
学生表中的 class_id 的值必须来自 classses 表中的 id 字段,且定义的时候就要确定类型相同。
现在向班级表中插入数据:
mysql> insert into classes values(1, '一班');
Query OK, 1 row affected (0.64 sec)
mysql> insert into classes values(2, '二班');
Query OK, 1 row affected (0.15 sec)
mysql> insert into classes values(3, '三班');
Query OK, 1 row affected (0.15 sec)
mysql> select * from classes;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
+----+--------+
3 rows in set (0.00 sec)
再向学生表添加数据:
mysql> insert into students values(181,'张三',1);
Query OK, 1 row affected (0.64 sec)
mysql> insert into students values(182,'张三',2);
Query OK, 1 row affected (0.08 sec)
mysql> insert into students values(183,'张三',3);
Query OK, 1 row affected (0.18 sec)
mysql> select * from students;
+-----+--------+----------+
| id | name | class_id |
+-----+--------+----------+
| 181 | 张三 | 1 |
| 182 | 张三 | 2 |
| 183 | 张三 | 3 |
+-----+--------+----------+
3 rows in set (0.00 sec)
小结:
1、主表中没有的数据值,在副表中是不可以使用的。
2、当主表中的记录被副表引用时,主表中被引用的值是不可以被删除的。
mysql> delete from classes where id=3;
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`))