约束包括:
- 1.主键约束
primary key
; - 2.非空约束
not null
; - 3.唯一约束
unique
; - 4.外键约束
foreign key
。
其中主键约束包括了非空约束和唯一约束。
我使用的表:
mysql> create table stu(
-> id int,
-> name varchar(20),
-> age int,
-> score double(4,2),
-> address_id int
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table address(
-> id int primary key auto_increment, # id自增
-> description varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
insert into address(description)values('France'),('England'),('China');
非空约束
创建:
- 创建表时添加非空约束
name varchar(20) not null
。 - 创建好以后再添加
alter table stu modify name varchar(20) not null
。
删除:
alter table stu modify name varchar(20)
。
例子:
创建表以后:
mysql> alter table stu modify name varchar(20) not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| score | double(4,2) | YES | | NULL | |
| address_id | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
创建表时:
mysql> create table stu(
-> id int,
-> name varchar(20) not null,
-> age int,
-> score double(4,2),
-> address_id int)
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> desc stu;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| score | double(4,2) | YES | | NULL | |
| address_id | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
删除非空约束:
mysql> alter table stu modify name varchar(20);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| score | double(4,2) | YES | | NULL | |
| address_id | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec
主键约束
创建:
- 创建表时,直接写上主键约束:
id int primary key
或者再最后写primary key (id)
。 - 创建表以后再添加主键约束:
alter table stu modify id int primary key
和alter table stu add primary key (id)
。
注意:使用primary key(id)
时,会设置默认值为0。
删除:
- 使用
drop primary key
,注意此时不再使用modify
进行修改。
例子:
创建好以后添加主键约束:
方式一:
mysql> alter table stu modify id int primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| score | double(4,2) | YES | | NULL | |
| address_id | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
方式二:
mysql> alter table stu add primary key (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| score | double(4,2) | YES | | NULL | |
| address_id | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
这两种方式一个有默认值,一个没有默认值。
创建时添加主键约束:
mysql> create table stu(
-> id int primary key,
-> name varchar(20) not null,
-> age int,
-> score double(4,2),
-> address_id int
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc stu;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| score | double(4,2) | YES | | NULL | |
| address_id | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
外键约束
创建:
- 在创建表的时候创建:
constraint FK_stu_address foreign key (address_id) references address(id)
。其中:constraint
是约束关键字FK_stu_address
是外键名称,addresss_id
是外键列名,address
是主表名称,根据自己的实际情况修改,constraint FK_stu_address
这个可以不用写。 - 创建好以后再添加:
add constraint FK_stu_address foreign key (address_id) references address(id)
。
删除:
alter table stu drop foreign key FK_stu_address
。
级联:当修改主表时,从表也会跟着修改相应的数据,使用时直接在后面加上就行。
on update cascade
级联更新on delete cascade
级联删除- 使用小例子:
mysql> alter table stu add constraint FK_stu_address foreign key (address_id) REFERENCES address(id) on update cascade;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
例子:
创建以后添加外键:
mysql> alter table stu add constraint FK_stu_address foreign key (address_id) REFERENCES address(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建时添加外键:
mysql> create table stu(
-> id int,
-> name varchar(20),
-> age int,
-> score double(4,2),
-> address_id int,
-> primary key(id),
-> constraint FK_stu_address # 这一行可以不写
-> foreign key(address_id) # 外键列名
-> REFERENCES address(id) # 对应主表中的东西
-> );
Query OK, 0 rows affected (0.02 sec)
删除外键:
mysql> alter table stu drop foreign key FK_stu_address;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0