MySQL约束

MySQL约束

概念

实际上就是表中数据的限制条件；

作用

表在设计的时候加入约束的目的就是为了表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

分类

*主键约束（primary key）PK

*自增长约束（auto_increment）

非空约束（not null）

唯一性约束（unique）

默认约束（default）

零填充约束（zerofill）

外键约束（foreign key）FK

主键约束

概念

MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，方便在RDBMS中尽快的找到某一行。

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不用允许空值。

每个表最多只允许一个主键（多个列在一起称为联合主键）

主键约束的关键是：primary key

当创建主键的约束时，系统默认会在所在列和列组合上对应的索引。

操作

添加单列主键

添加多列联合主键

删除主键

添加单列主键

 

 

use mydb1;

create table emp1(

eid int primary key,

name varchar(20),

deptId int,

salary DOUBLE

);

create table mydb1.emp2(

eid int,

name varchar(20),

deptId int,

salary double, -- constrait PK1可以省略

constraint pk1 primary key(eid)

);

添加联合主键

 

create table mydb1.emp3(

name varchar(20),

deptId int,

salaty DOUBLE,

primary key(name,deptId)

);

通过修改表结构添加主键

添加单列主键

create table mydb1.emp4(

eid int,

name varchar(20),

deptId int, salary double

);

USE mydb1;

alter table emp4 add primary key(eid);

添加多列主键

create table mydb1.emp5(

eid int,

name varchar(20),

deptId int, salary double

);

USE mydb1;

alter table emp5 add primary key(eid,NAME);

删除主键属性

 

删主键不分单列还有联合主键

-- 删除单列主键

alter table emp1 drop primary key;

-- 删除多列主键

alter table emp5 drop primary key;

自增长约束

概念

在MySQL中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由系统根据定义自动赋值。每添加一条记录，主键会自动一相同的步长进行增长。

通过给字段添加auto_increment属性来实现主键自增长

特点

默认情况下，auto_increment的初始值是一，每新增一条记录，字段自动加1.

一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免序号重复

auto_increment约束必须具备NOT NULL属性。

auto_increment约束的字段只能是整数类型

auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

-- 指定自增长的初始值

方式一：创建表时指定

create table t_user2( id int primary key auto_increment, name varchar(20) )auto_increment = 100;

insert into t_user2 values(NULL,'李四');

方式二：创建表之后指定

create table t_user3(

id int primary key auto_increment,

name varchar(20)

);

alter table t_user3 auto_increment=200;

insert into t_user3 values(NULL,'李四');

delete和truncate的区别

DELETE FROM t_user1;

insert into t_user1 values(NULL,'张三');

insert into t_user1 values(NULL,'李四'); -- delete删除之后，还是在最后一个值的基础上加一

truncate t_user1; -- truncate删除之后，自增长从一开始。定义初始值也从1开始

insert into t_user1 values(NULL,'张三'); insert into t_user1 values(NULL,'李四');

 

非空约束

方式一：创建表时指定

CREATE TABLE mydb1.t_user6(

id int,

NAME VARCHAR(20) not NULL,

address VARCHAR(20) not NULL

);

INSERT INTO t_user6(id) VALUES(1);-- 不可以

INSERT INTO t_user6(id,NAME,address) VALUES(1,'NULL','NULL');-- 可以(字符串)

INSERT INTO t_user6(id,NAME,address) VALUES(1,'','');-- 可以(空串)

方式二：创建表后指定

CREATE TABLE mydb1.t_user7( id int, NAME VARCHAR(20), address VARCHAR(20) )

ALTER TABLE t_user7 MODIFY name varchar(20) not null; ALTER TABLE t_user7 MODIFY address varchar(20) not null;

查看表结构

desc t_user7;

 

删除非空约束

-- alter table 表名 modify 字段 类型

ALTER TABLE t_user7 MODIFY name varchar(20);

ALTER TABLE t_user7 MODIFY address varchar(20);

desc t_user7;

 

唯一约束

方式一：在创建表的时候指定

CREATE TABLE mydb1.t_user8(

id int,

name varchar(20),

address varchar(20) unique -- 指定唯一约束

);

insert into t_user8 values(1,'张三',123);

insert into t_user8 values(1,'张三',124);

insert into t_user8 values(1,'李四',NULL);

insert into t_user8 values(1,'李四',NULL); --在MYSQL中NULL和任何书都不相同，甚至和自己也不同

 

方式二：在创建表之后指定

use mydb1;

CREATE TABLE mydb1.t_user9(

id int, name varchar(20),

address varchar(20)-- 指定唯一约束

);

alter table t_user9 add constraint unique_add unique(address);

insert into t_user9 values(1,'张三',123);

insert into t_user9 values(1,'张三',123);

删除唯一约束

格式：alter table <表名> drop index <唯一约束名>

alter table t_user9 drop index unique_add;

没有约束名就是你使用列的名字。

默认约束

 

在创建表时指定

use mydb1;

create table mydb1.t_user10(

id int,

name varchar(20),

address varchar(20) default '榆林' -- 指定默认的值

);

insert into t_user10(id,name) values(1,'张三');

insert into t_user10(id,name,address) values(1,'张三','上海');

insert into t_user10(id,name,address) values(1,'张三',NULL);

方式二：在创建表之后指定

use mydb1;

create table mydb1.t_user11( id int,

name varchar(20),

address varchar(20)

);

alter table t_user11 modify address varchar(20) default '西安';

insert into t_user11(id,name) values(1,'张三');

删除默认约束

-- alter table <表名> change column <字段名> <类型> default null;

alter table t_user11 modify address varchar(20) default null;

insert into t_user11(id,name) values(1,'李四');

零填充约束

 

添加约束

use mydb1;

create table mydb1.t_user12(

id int zerofill, -- 零填充约束，加上zerofill后默认的值是10.

name varchar(20)

);

insert into t_user12 VALUES(1,'杜沙琪');

insert into t_user12 VALUES(2,'杜沙琪');

删除约束

alter TABLE t_user12 modify id int;

desc t_user12;