sql约束

约束

基础知识

约束是为了保证进入数据库都是有效可靠的，会对列的值进行一些约束，确保存进去的数据都是有效的。

一、主键约束

主键约束最显著特征是主键列中的值是不允许重复的。一个表只能有一个primary key 约束，且primary key约束中的列不能接受null值。

1.设置主键约束的几种方式

1.创建表时指定主键约束
create table dept(
dept_no int primary key comment '部门编号',--设置主键
d_name varchar(12) comment '部门名称',
loc varchar(10) comment '部门地址');
create table dept(
dept_no int comment '部门编号',--设置主键
d_name varchar(12) comment '部门名称',
loc varchar(10) comment '部门地址'
primary key('dept_no'));--设置主键

2.修改某一列为主键

ALTER TABLE 表名称 ADD [CONSTRAINT] PRIMARY KEY(id);
alter table dept add primary key(dept_no);
ALTER TABLE 表名称 CHANGE [COLUMN] 字段名 字段名 属性 PRIMARY KEY; --修改列名称和类型
alter table dept change dept_no dept_no int primary key;
ALTER TABLE 表名称 MODIFY [COLUMN] 字段名 属性 PRIMARY KEY; -- 修改列类型
alter table dept modify dept_no int primary key;

3.删除主键约束

alter table dept drop primary key;[mysql8.0.22之后的版本]
ALTER TABLE 表名称 DROP INDEX PRIMARY KEY; [mysql8.0.22 之前的版本]

4.自增长列

自增长列是int类型的，其值是由数据库自动维护的，是永远都不会重复的，因此

自增长是最适合作为主键列的。在创建表时，通过 AUTO_INCREMENT 关键字来标识自

增长列，在MySQL数据库中自增长列必须是主键列

• 标识列必须和一个Key搭配（Key指主键、唯一、外键…）
• 一个表最多有一个标识列
• 标识列的类型只能是数值型
• 标识列可以通过 SET auto_increment_increment = 3; 设置步长（全局，退出数据库重新进入会恢复默认值），可以通过插入行时手动插入标识列值设置起始值。

ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 AUTO_INCREMENT; -- 修改为自增长列
alter table dept modify dept_no int auto_increment;
ALTER TABLE 表名 CHANGE 列名 列名 列类型 AUTO_INCREMENT;
alter table dept change dept_no dept_no int auto_increment
-- 删除自增长列
ALTER TABLE 表名 MODIFY [COLUMN]列名 列类型;
alter table dept modify dept_no int;

5.联合主键

• 当单个列无法唯一标识表中的每一行，但多个列组合在一起可以唯一标识每一

  行数据时。

• 提高查询性能：联合主键可以更有效地支持涉及多个列的查询，避免创建额外

  的索引。

• 在具有多个外键的关联表中，可以使用联合主键来确保外键引用的准确性。

联合主键要求每个列组合的值都是唯一的

CREATE TABLE 表名 (
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
PRIMARY KEY (列名1, 列名2, 列名3)
);
-- 修改列的时候创建
ALTER TABLE 表名 ADD [CONSTRAINT] PRIMARY KEY (列名1, 列名2, 列名3);-- 删除
alter table dept add constraint dept_no_id key (dept_no,dept_name);
ALTER TABLE 表名称 DROP PRIMARY KEY;
alter table dept drop primary key;

二、唯一约束

它允许多个null值存在

-- 创建表时
CREATE TABLE `table_name` (
`id` int,
`name` varchar(20) UNIQUE # 唯一约束
);
CREATE TABLE `table_name` (
`id` int,
`name` varchar(20),
CONSTRAINT uq UNIQUE(name) #唯一约束
);

-- 修改表

ALTER TABLE 表名 ADD UNIQUE(列名称);
alter table dept add unique(dept_name);
ALTER TABLE 表名 ADD CONSTRAINT [constraint_name] UNIQUE(列名称);
alter table dept add constraint up unique;
ALTER TABLE 表名 CHANGE [COLUMN] 列名 列名 类型 UNIQUE;
alter table dept change dept_name dept_name varchar(12) unique; 
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 UNIQUE;
alter table dept modify dept_name varchar(12) unique;

删除唯一约束

ALTER TABLE 表名称 DROP INDEX 设置唯一时的名称;
alter table dept drop index up;

三、默认约束

为列中的值设置默认值， default value

-- 创建表时
CREATE TABLE `table_name` (
`id` int DEFAULT value,
`name` varchar(20) unique,
);
create table dept(
   gender varchar(10) default '男'
);
-- 修改表
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 DEFAULT 默认值;
alter table dept modify gender varchar(10) default '男';
ALTER TABLE 表名 CHANGE 列名 列名 列类型 DEFAULT 默认值;
alter table dept modify gender gender varchar(10) default '男';

--删除
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型;
ALTER TABLE 表名 ALTER [COLUMN] 列名 DROP DEFAULT; [8.0.23以上的版本]
ALTER TABLE 表名 ALTER [COLUMN] 列名 SET DEFAULT NULL;[8.0.23以前的版本]

如果已经设置了值，默认值就无效了

四、非空约束

NOT NULL ：非空，用于保证该字段的值不能为空。

-- 创建表时
CREATE TABLE `table_name` (
`id` int NOT NULL, # 非空约束
`name` varchar(20),
);
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 NOT NULL;
ALTER TABLE 表名 CHANGE 列名 列名 列类型 NOT NULL;
-- 删除
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 [null];

修改列的约束确保现有数据满足非空约束条件，否则可能导致操作失败。

五、检查约束

CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
age INT,
CHECK (age >= 18) -- 添加检查约束：年龄必须大于等于18

);

-- 修改表的时候

ALTER TABLE 表名 CHANGE 列名 列名 列类型 CHECK(condition);

ALTER TABLE 表名 MODIFY 列名 列类型 CHECK(condition);

ALTER TABLE 表名 ADD CONSTRAINT 约束名 列名 CHECK(condition);

-- 删除检查约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

六、外键约束

用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

表中列的值来自于另外一张表的主键或唯一键的列称为外键 FK ，将被引用值的表称

为主表或父表，将引用值的表称为从表或子表。

-- 创建表时
CREATE TABLE `table_name` (
`id` int NOT NULL,
`name` varchar(20),
    `rid` int,
CONSTRAINT fk_a_b FOREIGN KEY(rid) REFERENCES b(id)
);
ALTER TABLE 从表表名 ADD [CONSTRAINT] 约束名称 FOREIGN KEY (从表字段)
REFERENCES 主表表名(主表字段);
ALTER TABLE goods ADD [CONSTRAINT] fk_category_no FOREIGN KEY
(category_no) REFERENCES category(no);
-- 删除
ALTER TABLE 表名称 DROP FOREIGN KEY 索引名 ;# 设置外键时的名称

• 外键列类型需要与引用列类型一致

• 外键列的值必须是主表中引用列的值或者 NULL

• 一个表可以有多个外键列

• 从表列可以随便删除

• 删除主表数据时，会先检查从表中有没有对此数据的关联，如果有就不能直接删除

在 设 置 外 键 的 时 候 后 面 添 加 on delete cascade / on updatecascade 在删除/更新主表时，级联删除/更新外键列的数据

在设置外键的时候后面添加 on delete set null / on update setnull 在删除/更新主表时，外键列的值会变成null
alter table employee drop foreign key employee_dept_id;
alter table employee add constraint employee_dept_id foreign key(dept_id) references dept(dept_id) on delete cascade;

delete set null / on update setnull 在删除/更新主表时，外键列的值会变成null
alter table employee drop foreign key employee_dept_id;
alter table employee add constraint employee_dept_id foreign key(dept_id) references dept(dept_id) on delete cascade;