一、主键约束/自增约束
1、主键约束:
它能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使得改字段不重复且不为空。
列如:
创建表约束:
mysql> create table user1(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key(id)
插入数据:
mysql> insert into user values(1,'zhangsan');
Query OK, 1 row affected (0.07 sec)
如果再插入序号为1的张三,就会报错。
查询表属性:
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2、联合主键:
只要联合的主键值加起来不重复就可以了
例子:
创建联合主键表:
mysql> create table user2(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key(id,name)
-> );
Query OK, 0 rows affected (0.06 sec)
查看表属性:
mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(20) | NO | PRI | | |
| password | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
插入数据:
insert into user2 values(1,'张三','123') ----可插入
insert into user2 values(1,'张三','123') ----不可插入
insert into user2 values(2,'张三','123') ----可插入
insert into user2 values(1,'aaa','123') ----可插入
insert into user2 values(1NULL,'张三','123') ----不可插入
主键不能为空!!!无论是主键还是联合主键。
3、自增约束
使主键自动增长,可以不用输入主键也可以
例子:
创建自增约束表:
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.04 sec)
查看表属性:
mysql> desc user3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
只填入了name值:
mysql> select * from user3;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
id按顺序生成
4、如果说我们创建表的时候,忘记创建主键约束了?该怎么办?
例子:
创建没有约束的表:
create table user4(
id int,
name varchar(20)
);
创建表后,再添加主键
mysql> alter table user4 add primary key(id); #把id设置为主键
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
5、如何删除表里的主键?
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
6、修改主键
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
二、外键约束
涉及到两个表:父表,子表或者主表,副表
例如:
创建两个表,一个班级表,一个学生表
mysql> create table classes( #班级表
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.07 sec) mysql> create table students( #学生表
-> id int primary key,
-> name varchar(20),
-> class_id int #class_id为班级表的id,但还没有进行绑定
-> foreign key(class_id) references classes(id) #进行绑定
-> );
uery OK, 0 rows affected (0.11 sec)
插入数据:
插入班级数据:
insert into classes values(1,'yiban');
insert into classes values(2,'erban');
insert into classes values(3,'sanban');
insert into classes values(4,'siban');
插入学生数据:
insert into students values(1001,'zhangsan',1);
insert into students values(1002,'zhangsan',2);
insert into students values(1003,'zhangsan',3);
insert into students values(1004,'zhangsan',4);
insert into students values(1005,'zhangsan',5);-----因为班级id没有5,所有此条插入不成功
总结:
1、主表classes中没有的数据值,在副表中,是不可以使用的
2、主表中的记录被副表引用,是不可以被删除的;
mysql> delete from classes where id=4; #id为4已结被调用了,无法被删除
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_mo`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
三、唯一约束
约束修饰的字段的值不可以重复
总结:
1、建表的时候就添加约束
2、可以使用alter。。。add。。。(建表后再添加)
3、alter。。。modif。。。
4、删除alter 。。。drop。。。
1、设置唯一约束:
例如1:
创建表:
create table user5(
id int,
name varchar(20)
);
添加唯一约束:
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0 查看user5表属性,发现key为UNI
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)
插入数据:
mysql> insert into user5 values(1,'mokaijian');
Query OK, 1 row affected (0.04 sec)
插入相同数据-----报错:
mysql> insert into user5 values(1,'mokaijian');
ERROR 1062 (23000): Duplicate entry 'mokaijian' for key 'name'
插入不同名字的数据,但是id相同-----ok:
mysql> insert into user5 values(1,'mokaijia');
Query OK, 1 row affected (0.13 sec)
例如2:----------联合添加唯一主键
创建user6:
create table user6(
id int,
name varchar(20),
unique(id,name)
);
查询user6属性:
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
发现联合使用后,Key不是 UNI ,而是 MUL 。
插入数据:
insert into user6 values(1,'zhangsan');
insert into user6 values(2,'zhangsan');
insert into user6 values(1,'lisi');
以上三种都可以插入
2、删除唯一约束
alter table user5 drop index name;
例子:(已有user5,唯一约束为name)
删除唯一约束:
mysql> alter table user5 drop index name;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0 查看表属性:
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3、添加----modify
添加唯一约束:(已有user5,状态为无约束)
mysql> alter table user5 modify name varchar(20) unique;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看表属性: 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)
四、非空约束
修饰的字段不能为空 NULL
例子:
创建非空约束表:
mysql> create table user8(
-> id int,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.11 sec) 查看表属性,发现Null中name为NO。
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
插入数据:
mysql> insert into user8 (id) values(1); ----------只插入id,不填写name,无法插入
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into user8 values(1,'mo');---------同时插入id和name------成功
Query OK, 1 row affected (0.00 sec)
mysql> insert into user8 (name) values('mo1');---------只插入name,不插入id--------成功
Query OK, 1 row affected (0.05 sec)
五、默认约束
就是当我们插入字段值的时候,如果没有传值,就会使用默认值
例子:
新建默认约束表:
mysql> create table user9(
-> id int,
-> name varchar(20),
-> age int default 10
-> );
Query OK, 0 rows affected (0.01 sec)
查看表属性:(其中,age的default为默认值10) mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| 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.03 sec)
插入数据,但没有传入age值:
mysql> insert into user9 (id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
查看数据,发现age已经默认为10:
mysql> select * from user9;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 10 |
+------+----------+------+
1 row in set (0.00 sec)