Elvis · 个人站
文章 分类 标签 关于
Elvis · 个人站

目录

MySQL数据库之建表约束

   约 1930 字   预计阅读 4 分钟  收录于 数据库

主键约束

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

create table user(
	id int primary key, --设置id为主键
	name varchar(20)
);
mysql> insert into user values(1,'张三');
Query OK, 1 row affected (0.17 sec)
mysql> insert into user values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into user values(2,'张三');
mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
+----+--------+
mysql> insert into user values(NULL,'张三');
ERROR 1048 (23000): Column 'id' cannot be null

联合主键

只要联合的主键值加起来不重复就可以

create table user2(
	id int,
	name varchar(20),
	password varchar(20),
	primary key(id,name)  --id和name为联合主键
);

insert into user2 values(1,'张三','123');
insert into user2 values(2,'张三','123');
insert into user2 values(1,'李四','123');

自增约束

主键的值自动增加，不用人为设置

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

mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.14 sec)
mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.06 sec)
mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)

唯一约束

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

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

mysql> alter table user5 add unique(name); --对name进行唯一约束
Query OK, 0 rows affected (0.16 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)

mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.16 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)

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


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

create table user8(
	id int,
	name varchar(20),
	unique(id,name)  --表示两个键在一起不重复就行
);

删除唯一约束 alter table user7 drop index name; 添加唯一约束 alter table user7 modify name varchar(20) unique;

非空约束(not null)

修饰的字段不能为空(NULL)

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

mysql> create table user9(
    -> id int,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.15 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)


insert into user9 (id) values(1);

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

insert into user9 values(1,'张三');
mysql> select * from user9;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+
1 row in set (0.00 sec)

insert into user9 (name) values('lisi');
mysql> select * from user9;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
| NULL | lisi   |
+------+--------+
2 rows in set (0.00 sec)

默认约束

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

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

mysql> create table user10(
    -> id int,
    -> name varchar(20),
    -> age int default 10
    -> );
Query OK, 0 rows affected (0.16 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.01 sec)

insert into user10 (id, name) values(1,'zhangsan');
mysql> insert into user10 (id, name) values(1,'zhangsan');
Query OK, 1 row affected (0.09 sec)

mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
+------+----------+------+
1 row in set (0.00 sec)

传了值，就不会使用默认值
mysql> insert into user10 values(1,'zhangsan', 19);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    1 | zhangsan |   19 |
+------+----------+------+
2 rows in set (0.00 sec)

外键约束

涉及到两个表：父表，子表（主表，副表）。

-- 班级
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) --外键约束
);


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

insert into students values(1005,'lisi',5);

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

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

mysql> select * from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  2 | 二班   |
|  3 | 三班   |
|  4 | 四班   |
+----+--------+
4 rows in set (0.01 sec)
  1. 主表(父表) classes 中没有的数据值，在副表（子表）中，是不可以使用的
  2. 主表中的记录被副表引用，是不可以被删除的。
delete from classes where id=4;
mysql> delete from classes where id=4;
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`))

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

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

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

修改表结构，添加主键 alter table user4 add primary key(id);

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

如何删除 alter table user4 drop primary key;

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

使用 modify 修改字段，添加约束 alter table user4 modify id int primary key;

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
更新于 2019-04-13
 Tags
返回 | 主页