MySQL表约束

一、主键约束

primary key主键约束,一个表中只能有一个主键,但是一个主键可以是几个字段

1、直接在创建表的时候创建主键

create table names(
  id int primary key comment "用户id"
)

2、手动添加主键alter table 表名 add primary key(字段1，字段2)

mysql> desc person;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| age      | int(11)      | YES  |     | NULL    |       |
| birthday | datetime     | YES  |     | NULL    |       |
| name     | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table person add primary key(id, name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| age      | int(11)      | YES  |     | NULL    |       |
| birthday | datetime     | YES  |     | NULL    |       |
| name     | varchar(100) | NO   | PRI | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

3、删除主键(一次全部删除,再添加)alter table 表名 drop primary key

mysql> alter table person drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc person;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   |     | NULL    |       |
| age      | int(11)      | YES  |     | NULL    |       |
| birthday | datetime     | YES  |     | NULL    |       |
| name     | varchar(100) | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

4、联合主键(主键是几个字段)

mysql> create table person(
    -> id int not null auto_increment,
    -> name varchar(100) not null,
    -> city varchar(100),
    -> primary key(id, name)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc person;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   | PRI | NULL    |                |
| city  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

二、唯一约束

unique表示唯一,一个表中可以有多个唯一的字段，仅仅是表示该字段不能重复

1、创建表的时候添加

mysql> create table person(
    -> id int primary key auto_increment comment "用户id",
    -> name varchar(100) unique comment "用户名",
    -> age int comment "年龄"
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc person;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  | UNI | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql>

2、手动新增一个unique alter table 表名 add unique(字段名)

mysql> alter table person add unique(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  | UNI | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

3、删除唯一约束alter table 表名 drop index 列名

mysql> alter table person drop index name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

三、非空约束(`not null`)

1、创建非空约束

mysql> create table person(
    -> id int primary key auto_increment comment '用户id',
    -> name varchar(100) not null comment "用户名",
    -> age int comment "年龄"
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc person;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

2、修改字段为非空约束alter table 表名 modify 字段名字段类型 not null

mysql> desc person;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table person modify name varchar(100) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql>

四、检查约束

1、enum的使用: 使用枚举类型,只能选择里面的一个值

mysql> create table person(
    -> id int primary key auto_increment comment "用户id",
    -> name varchar(100) not null comment "用户名",
    -> sex enum("男", "女", "请选择") default "请选择"
    -> );
Query OK, 0 rows affected (0.02 sec)

2、set的使用: 使用set可以选择里面多个值

mysql> create table person(
    -> id int primary key auto_increment comment "用户id",
    -> name varchar(100) not null comment "用户名",
    -> hobby set("学习", "游戏", "旅游") default null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>

五、外键约束`foreign key(约束的)`

重点:作为外键一定要和关联主键的数据类型保持一致

1、创建主表

create table teacher(
  id int primary key auto_increment comment "老师id",
  name varchar(100) not null unique comment "老师名字",
  age int comment "年龄",
  create_at datetime default now() comment "创建时间"
);

2、创建从表

create table student(
id int primary key auto_increment comment "用户id",
name varchar(100) not null unique comment "名字",
city varchar(100) not null comment "城市",
age int not null comment "年龄",
create_at timestamp default current_timestamp comment "创建时间",
teacher_id int,
foreign key(teacher_id) references teacher(id)
);

3、插入数据

insert into teacher(name, age) values("李老师", 20), ("王老师", 25);
insert into student(name, city, age, teacher_id) values ("张三", "广东", 10, 1), ("李四", "广西", 22, 1), ("王五", "广东", 25, 1), ("马六", "湖南", 40, 2);

mysql> select * from student;
+----+--------+--------+-----+---------------------+------------+
| id | name   | city   | age | create_at           | teacher_id |
+----+--------+--------+-----+---------------------+------------+
|  1 | 张三   | 广东   |  10 | 2018-06-29 15:40:43 |          1 |
|  2 | 李四   | 广西   |  22 | 2018-06-29 15:40:43 |          1 |
|  3 | 王五   | 广东   |  25 | 2018-06-29 15:40:43 |          1 |
|  4 | 马六   | 湖南   |  40 | 2018-06-29 15:40:43 |          2 |
+----+--------+--------+-----+---------------------+------------+
4 rows in set (0.00 sec)

mysql>

4、添加外键

mysql> alter table student add constraint abc foreign key(teacher_id) references teacher(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

5、删除外键

alter table student drop foreign key abc;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

六、外键的约束的几种模式

1、外键约束子表: 如果父表中找不到候选键,则不允许在子表中插入数据
2、外键约束父表: 如果父表中进行update/delete,如果子表中存在一条或者多条数据的时候,取决于子表的外键指定的on update/on delete

3、外键约束直接删除父表数据(有子表关联)[不可以直接删除]

mysql> delete from teacher where id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysqltest`.`student`, CONSTRAINT `abc` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`))
mysql>

4、直接删除父表(没有子表关联)[可以直接删除]

mysql> delete from teacher where id = 3;
Query OK, 1 row affected (0.00 sec)

mysql>

5、cascade模式在父表操作会同步到子表中foreign key(teacher_id) references teacher(id) on delete cascade

create table student(
id int primary key auto_increment comment "用户id",
name varchar(100) not null unique comment "名字",
city varchar(100) not null comment "城市",
age int not null comment "年龄",
create_at timestamp default current_timestamp comment "创建时间",
teacher_id int,
foreign key(teacher_id) references teacher(id) on delete cascade
);

6、set null方式在父表中删除了,子表中显示null但是不删除数据foreign key(teacher_id) references teacher(id) on delete set null

create table student(
id int primary key auto_increment comment "用户id",
name varchar(100) not null unique comment "名字",
city varchar(100) not null comment "城市",
age int not null comment "年龄",
create_at timestamp default current_timestamp comment "创建时间",
teacher_id int,
foreign key(teacher_id) references teacher(id) on delete set null
);

mysql> select * from student;
+----+--------+--------+-----+---------------------+------------+
| id | name   | city   | age | create_at           | teacher_id |
+----+--------+--------+-----+---------------------+------------+
|  1 | 张三   | 广东   |  10 | 2018-06-29 16:14:11 |          1 |
|  2 | 李四   | 广西   |  22 | 2018-06-29 16:14:11 |          1 |
|  3 | 王五   | 广东   |  25 | 2018-06-29 16:14:11 |          1 |
|  4 | 马六   | 湖南   |  40 | 2018-06-29 16:14:11 |          2 |
+----+--------+--------+-----+---------------------+------------+
4 rows in set (0.00 sec)

mysql> delete from teacher where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+--------+-----+---------------------+------------+
| id | name   | city   | age | create_at           | teacher_id |
+----+--------+--------+-----+---------------------+------------+
|  1 | 张三   | 广东   |  10 | 2018-06-29 16:14:11 |       NULL |
|  2 | 李四   | 广西   |  22 | 2018-06-29 16:14:11 |       NULL |
|  3 | 王五   | 广东   |  25 | 2018-06-29 16:14:11 |       NULL |
|  4 | 马六   | 湖南   |  40 | 2018-06-29 16:14:11 |          2 |
+----+--------+--------+-----+---------------------+------------+
4 rows in set (0.00 sec)

mysql>

5.MySQL表约束

MySQL表约束

一、主键约束

二、唯一约束

三、非空约束(`not null`)

四、检查约束

五、外键约束`foreign key(约束的)`

六、外键的约束的几种模式

results matching ""

No results matching ""

MySQL表约束

一、主键约束

二、唯一约束

三、非空约束(not null)

四、检查约束

五、外键约束foreign key(约束的)

六、外键的约束的几种模式

results matching ""

No results matching ""

三、非空约束(`not null`)

五、外键约束`foreign key(约束的)`