mysql约束

数据库表的约束概述

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

分类约束名约束关键字主键primary key唯一unique非空not null外键foreign key检查约束check 注：mysql 不支持主键约束作用

用来唯一标识数据库中的每一条记录。

哪个字段应该作为表的主键？

通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用 的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。 如：身份证，学号不建议做成主键

主键的特点唯一且非空一张表只能有一个主键格式

在创建表的时候给字段添加主键

字段名 字段类型 PRIMARY KEY

在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

代码演示

-- 创建表学生表 student01, 包含字段(id, name, age)将 id 做为主键
create table student01 (
    id int primary key, -- id为主键
    name varchar(20),
    age int
);

-- 删除student01上面的主键
alter table student01 drop primary key;

-- 重新为表student01添加主键
alter table student01 add primary key(id);

主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值

AUTO_INCREMENT： 表示自动增长，但字段类型必须是整数类型

默认 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下列 SQL 语法

AUTO_INCREMENT=初始值

创建好以后修改起始值（只能往大的修改）

ALTER TABLE 表名 AUTO_INCREMENT = 初始值;

创建表后，删除自增

ALTER TABLE 表名 MODIFY 字段名 字段类型;

创建表后，添加自增

ALTER TABLE 表名 MODIFY 字段名 字段类型 AUTO_INCREMENT;

DELETE 和TRUNCATE对自增长的影响

DELETE：删除所有的记录之后，自增长没有影响。

TRUNCATE：删除以后，自增长又重新开始。

代码演示

-- 创建表 student02, 包含字段(id, name, age)将 id 做为自增主键，起始值为5
create table student02 (
    id int primary key auto_increment, -- 自增主键
    name varchar(20),
    age int
) auto_increment = 5;

-- 直接插入数据，主键会自动增长
insert into student02 (name, age) values('Jason', 21);
insert into student02 (name, age) values('June', 22);
insert into student02 (name, age) values('Charlie', 23);

-- 创建好以后修改起始值为10
alter table student02 auto_increment = 10;

-- 插入数据
insert into student02 values (null, 'Bob', 24);
insert into student02 values (null, 'Sql', 25);
insert into student02 values (null, 'My', 26);

-- DELETE：删除所有的记录之后，自增长没有影响。
delete from student02;

-- 插入数据
insert into student02 values (null, 'is', 30);
insert into student02 values (null, 'it', 31);
insert into student02 values (null, 'just me', 32);

-- TRUNCATE：删除以后，自增长又重新开始。
truncate table student02;

-- 插入数据
insert into student02 values (null, 'Sasha', 36);
insert into student02 values (null, 'Sloan', 40);

-- 删除自增
alter table student02 modify id int(11);

-- 再次添加自增
alter table student02 modify id int(11) auto_increment;

唯一约束概述

约束表中某一列不能出现重复的值（可以有多个NULL）

格式

创建表时，设置唯一约束

字段名 字段类型 UNIQUE;

在创建表后，为字段添加唯一约束

ALTER TABLE 表名 ADD [CONSTRAINT 唯一约束名] UNIQUE (列名);

删除唯一约束

ALTER TABLE 表名 DROP INDEX 列名/唯一约束名;

代码演示

-- 创建学生表 student03, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生
create table student03 (
    id int,
    name varchar(20) unique -- 设置唯一约束
);


-- 添加一个同名的学生
insert into student03 (id, name) values (1, 'Jason');
insert into student03 (id, name) values (2, 'Jason');

-- 可以存在多个null
insert into student03 (id, name) values (3, null);
insert into student03 (id, name) values (4, null);

--  创建表后，为列名id设置唯一约束
alter table student03 add constraint unique_id UNIQUE (id);

-- 删除列名id的唯一约束
alter table student03 drop index unique_id;

非空约束概述

规定某一列不能为 null。

格式

创建非空约束

字段名 字段类型 NOT NULL

删除非空约束

ALTER TABLE 表名 MODIFY 字段名 字段类型 NULL;
或
ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 字段类型 NULL;

创建表后，为字段设置非空约束

ALTER TABLE 表名 MODIFY 字段名 字段类型 NOT NULL;
或
ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 字段类型 NOT NULL;

代码演示

-- 创建表学生表 student04, 包含字段(id,name,gender)其中 name 不能为 NULL
create table student04 (
    id int,
    name varchar(20) not null, -- 非空约束
    gender varchar(4)
);

-- 添加一条记录其中姓名不赋值
insert into student04 values (1, null, '男');


-- 删除字段名为name的非空约束
alter table student04 change column name name varchar(20) NULL;

-- 重新为字段名为name添加上非空约束
alter table student04 change column name name varchar(20) NOT NULL;

默认值概述

给字段默认赋一个值。

格式

字段名 字段类型 DEFAULT 默认值;

代码演示

-- 创建一个学生表 student05，包含字段(id,name,address)， 地址默认值是广州
create table student05 (
    id int,
    name varchar(20),
    address varchar(100) default '广州'
);

-- 添加一条记录,使用默认地址
insert into student05 (id,name) values(1, 'Jason');

疑问:如果一个字段设置了非空与唯一约束，该字段与主键的区别?

主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。自增长只能用在主键上外键约束概述外键：在从表中与主表主键对应的那一列主表：一方，用来约束别人的表从表：多方，被别人约束的表特点必须先删除了从表，才能删除主表从表的外键值可以为NULL，但是不能为主表主键值外不存在的值格式

新建表时增加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);

已有表增加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOERIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

删除外键

ALTER TABLE 从表 DROP FOREIGN KEY 外键名称;

代码演示

-- 创建表department，作为主表
create table department (
    id int primary key auto_increment,
    dep_name varchar(20),
    dep_location varchar(20)
);

-- 向主表添加数据
insert into department values (null, '研发部', '北京');
insert into department values (null, '开发部', '上海');

-- 创建从表 employee 并添加外键约束 emp_depid_fk
create table employee (
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    -- 创建外键约束
    constraint emp_depid_fk foreign key (dep_id) references department (id)
);

-- 向从表添加数据
insert into employee (name, age, dep_id) values ('Jason', 21, 2);
insert into employee (name, age, dep_id) values ('Charlie', 22, 1);
insert into employee (name, age ,dep_id) values ('June', 23, 1);
insert into employee (name, age, dep_id) values ('Bob', 25, 2);
insert into employee (name, age ,dep_id) values ('Run', 19, 2);
insert into employee (name, age, dep_id) values ('Cat', 26, 1);

-- 尝试往从表中插入不存在的部门（报错）
insert into employee (name, age, dep_id) values ('KOK', 48, 3);

-- 删除employee表的emp_depid_fk外键
alter table employee drop foreign key emp_depid_fk;

外键的级联

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。

级联操作语法描述ON UPDATE CASCADE级联更新，只能是创建表的时候创建级联关系。
更新主表中的主键，从表中的外键 列也自动同步更新ON DELETE CASCADE级联删除

代码演示

-- 删除 employee 表
drop table employee;

-- 重新创建 employee 表，添加级联更新和级联删除
create table employee (
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    constraint emp_depid_fk foreign key (dep_id) references department (id) 
    on update cascade on delete cascade -- 创建外键约束,添加级联更新、级联删除
);

-- 再次添加数据到employee表和department表
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

-- 把部门表中 id 等于 1 的部门改成 id 等于 10
update department set id = 10 where id = 1;


-- 不能直接删除主表department,必须先删除了从表
drop table department;


-- 删除部门号是 2 的部门
delete from department where id = 2;