SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为,行为会被约束终止。约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。可以使用约束来限定表中数据准确性、完整性、一致性、联动性等。
sql有6中约束,not null, default , prmary key, foreign key ,unique ,check
create table student(
id int auto_increment,
name varchar(50) not null,
age tinyint not null,
sex varchar(5) default '男',
address varchar(255),
phone_num varchar(11) unique not null,
primary key (id)
);
1.非空
没有给表字段添加默认值约束时,当我们插入数据的时候如果没有传递对应字段的数据则默认为NULL 即default null
。如果需要限制某个字段必须赋值,可以添加 not null 约束
insert into student (name,age) values ('aa',null);
-- Column 'age' cannot be null
insert into student (name,age) values ('aa',23);
-- Field 'phone_num' doesn't have a default value
字段添加非空约束以后,则必须传递有意义的数据,就是说不传值或者传递null
值,都会报错‘字段不能为null’
2.默认值
我们在创建表或者修改表的时候,给某个字段添加默认值,这样如果为字段传入数据则为传递数据值,不提供数据就是用默认值。如果没有使用默认值约束,则默认使用default null
,在上面也提到过了。
show create table student;
Table | Create Table
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` tinyint(4) NOT NULL,
`sex` varchar(5) DEFAULT '男',
`address` varchar(255) DEFAULT NULL,
`phone_num` varchar(11) NOT NULL,
PRIMARY KEY (`id`)
UNIQUE KEY `phone_num` (`phone_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
从上面也可以看到MySQL 自动为address
使用了 default null
insert into student (name,age,phone_num) values ('aa',23,'1766666612');
select * from student;
+----+------+-----+------+---------+------------+
| id | name | age | sex | address | phone_num |
+----+------+-----+------+---------+------------+
| 1 | aa | 23 | 男 | NULL | 1766666612 |
3.唯一
当某个字段不允许重复时,可以使用 unique
添加唯一约束。当添加数据的时候就会检查表中是否已经存在该数据,存在则报错否则正常插入。
注:unique 约束的字段可以为null,并且对null值不起限定作用(可以插入多个null)。所以unique 只对插入的有意义的数值起到限定
UNIQUE KEY `phone_num` (`phone_num`)
-- 第一个phone_num是unique约束名称(应该是索引名称),第二个在括号里的phone_num是字段的名称
MySQL删除unique 约束
alter table student drop {index|key} phone_num //删除唯一约束
从上面也可以看到,删除唯一约束时使用index或key
(在MySQL中key和index意思差不多吧),大概是mysql唯一约束是利用唯一索引实现的吧。
从下面也可以看到,给列添加主键约束时,也会创建索引
添加唯一约束
alter table student add constraint stu_phone_num_con unique key phone_num_unique (phone_num)
--或者,此时mysql会根据列名自动生成 约束的名称
alter table student add unique (phone_num)
从上面添加唯一约束的方式来看,也确实时创建了一个唯一索引。
可以为多列指定唯一约束,也可以为某列创建多个唯一约束。
4.主键
主键用于标识表的某一行数据,所以主键列数据不能重复 ,并且主键需要有实际意义。所以主键就有两种约束的特性,主键= not null + unique
--显示表信息 describe tableName
desc student;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | tinyint(4) | NO | | NULL | |
| sex | varchar(5) | YES | | 男 | |
| address | varchar(255) | YES | | NULL | |
| phone_num | varchar(11) | NO | UNI | NULL | |
+-----------+--------------+------+-----+---------+----------------+
1)可以看到id
,非空且唯一,而且 key
列显示pri
。在extra
列auto_increment
,auto_increment
必须作用于主键上,而且这是MySQL独有的,在其他sql语言中一般称之为序列。
2) 很多字段默认值都为null(default null
),但是使用not null约束以后default null
实际上不起作用了
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。但是每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。一般主键使用自增的方式,在MySQL中使用auto_increment
。
索引除了主键索引(pri)和唯一索引(uni)还有普通索引(MUL)
5.外键
外键约束是保证两个表之间的参照完整性,一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
外键所在表称之为从表,主键所在表称之为主表。这样主表的数据不能随意修改或者直接更新从表数据。
1).使用外键约束
create table dep(
id int primary key,
name varchar(80)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
create table teacher(
id int primary key ,
depId int ,
name varchar(50) not null,
foreign key (depId) references dep(id)
)ENGINE=InnoDB CHARSET=utf8
teacher depId外键引用了dep 表中的id
需要说明的是,外键也可以为空。
2)新增操作
insert into teacher(id,name) values(1,'aa');
-- 可以插入成功
insert into teacher(id,name,depId) values(1,'aa',1);
-- 插入有意义的数据时就会去dep 表中查询是否存在该值,不存在则报错
-- Cannot add or update a child row: a foreign key constraint fails (`mydata`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `dep` (`id`))
insert into dep(id,name) values(1,'计算机');
insert into teacher(id,name,depId) values(2,'pig 奇',1);
######3)更新操作
update dep set id =2 where id=1;
-- Cannot delete or update a parent row: a foreign key constraint fails (`mydata`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `dep` (`id`))
更新主表的被关联列时,该行为被阻止。可以使用级联操作强制数据更新
使用级联操作的两种方式
1.创建表的时候增加级联更新的选项
foreign key (depId) references dep(id) on update cascade
2.修改表
-- 1.删除外键
alter table teacher drop foreign key teacher_ibfk_1;
-- 新增外键,添加级联更新选项
alter table teacher add foreign key(depId) references dep(id) on update cascade;
update dep set id =2 where id=1 ;
--可以正常运行了
4).删除数据
删除数据和跟新数据类似的,也会报错。需要增加 on delete cascade
小结: 当有外键关联时,被关联列的数据发生改变时,要么该行为被阻止,要么会更新从表中对应的数据
级联更新
ON UPDATE CASCADE
级联删除
ON DELETE CASCADE
当然也可以使用触发器来代替外键
6.check
check 约束在MySQL中目前还不支持