Mysql学习笔记#6-约束

约束

---

主键约束

---

它能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加约束，就可以使得该字段不重复且不为空

create table user(
	id int primary key,
	name varchar(20)
);

insert into user values(1,'zhangsan');
Query OK,1 row affected(0.17 sec)

insert into user values(1,'zhangsan');
ERROR 1062 (23000):Duplicate entry '1' for key 'primary'
(因为id为主键约束，所以不能再添加)

insert into user values(2,'zhangsan');
Query OK,1 row affected(0.13 sec)

insert into user values(NULL,'zhangsan');
ERROR 1048 (23000):Column 'id' can not be null

主键约束-联合主键

只要联合的主键值加起来不重复就可以，但任何字段都不能为空

create table user2(
	id int,
	name varchar(20)
	password varchar(20)
	primary key(id,name)
);

insert into user2 values(1,'zhangsan',123);
Query OK,1 row affected(0.13 sec)

insert into user2 values(2,'zhangsan',123);
Query OK,1 row affected(0.10 sec)

insert into user2 values(1,'lisi',123);
Query OK,1 row affected(0.12 sec)

主键约束-建表后的添加与删除

如果创建表的时候，忘记创建主键约束了,该怎么办

create table user4(
	id int,
	name varchar(20)
);

添加
alter table user4 add primary key(id);

删除
alter table user4 drop primary key;

修改
alter table user4 modify id int primary key;

自增约束

---

自增约束有自动管控的作用

create table user3(
	id int primary key auto_increment
	name varchar(20)
);

insert into user3 (name) values('zhangsan')
Query OK,1 row affected (0.14 sec)
(自动将zhagnsan的id设置为1)

外键约束

---

涉及到两个表：父表，子表(主表，副表)

• 班级

create table classes(
	id int primary key,
	name varchar(20)
);

• 学生

create table students(
	id int primary key,
	name varchar(20),
	class_id int,
	foreign key(class_id) references classes(id)
);

desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

desc students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+

	insert into classes values(1,'一班');
	insert into classes values(2,'二班');
	insert into classes values(3,'三班');
	insert into classes values(4,'四班');

	insert into students values(1001,'sangsan',1);
	insert into students values(1002,'sangsan',2);
	insert into students values(1003,'sangsan',3);
	insert into students values(1004,'sangsan',4);

insert into students values(1005,'lisi',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (study.students, CONSTRAINT students_ibfk_1 FOREIGN KEY (class_id) REFERENCES classes (id))

结论

1. 主表 classes 中没有的数据值，在副表中，不可以被引用。
2. 主表中的记录被副表引用时，是不可以被删除的。
   delete from classes where id=4;
   ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (study.students, CONSTRAINT students_ibfk_1 FOREIGN KEY (class_id) REFERENCES classes (id))

唯一约束

---

约束修饰的字段的值不可以重复

create table user5(
	id int,
	name varchar(20),
	unique(name)
);

insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'

添加唯一约束
alter table user5 add unique(name);
alter table user5 modify name varchar(20) unique;

直接在字段后面修饰

create table user6(
	id int,
	name varchar(20) unique
)

如何删除唯一约束
alter table user6 drop index name;

多个唯一约束

修饰的字段组合在一起不重复，类似联合主键

create table user7(
	id int,
	name varchar(20),
	unique(id,name)
)

非空约束

---

修饰的字段不能为空 NULL

create table user9(
	id int,
	name varchar(20) not null
);

insert into user9 (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

insert into user9 (id) values(1,'zhangsan');
Query OK,1 row affected (0.14 sec)

insert into user9 (name) values('lisi')
Query OK,1 row affected (0.12 sec)

默认约束

---

当插入字段值的时候，如果没有传值，就会使用默认值

create table user10(
	id int,
	name varchar(20),
	age int default 10
);

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+

insert into user10 (id, name) values(1,'zhangsan');
select * from user10;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | shangsan | 10 |
+------+----------+------+

insert into user10 values(2,'lisi',20);
select * from user1;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | shangsan | 10 |
| 2 | lisi | 20 |
+------+----------+------+

总结

如何添加约束

• 建表的时候就添加约束
• 可以使用alter、add
• alter...modif...
  如何删除约束
  alter...drop...