SQL-约束篇

server/2024/9/20 7:28:20/ 标签: sql, 数据库

在数据库设计中，约束是确保数据完整性和准确性的关键元素。约束可以限制表中数据的类型、范围和关系，从而维护数据的一致性和可靠性。

1. 主键约束 (Primary Key)

主键约束用于唯一标识表中的每一行数据。一个表只能有一个主键，主键字段的值必须唯一且不能为空。

sql">CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',name VARCHAR(50) NOT NULL UNIQUE COMMENT '姓名',age INT CHECK (age > 0 AND age < 120) COMMENT '年龄',status CHAR(1) NOT NULL DEFAULT '1' COMMENT '状态',gender CHAR(1) NOT NULL COMMENT '性别'
) COMMENT '用户表';insert into user(name,age,status,gender) values('Tomm',25,'1','男'),('Jerry',30,'0','女'),('Lily',20,'1','女');

在上述代码中，id字段被定义为主键，并且使用AUTO_INCREMENT自动生成唯一值。

效果展示：

可以看到我们并没有插入id，他是自己填充的，id旁边的黄色小钥匙代表是主键

2. 唯一约束 (Unique)

唯一约束确保某个字段的值在表中是唯一的。与主键不同，表中可以有多个唯一约束。

sql">name VARCHAR(50) NOT NULL UNIQUE COMMENT '姓名'

在用户表中，name字段被设置为唯一，这意味着不同用户的姓名不能重复。

3. 索引约束 (Index)

索引约束用于加速查询操作。通过在一个或多个列上创建索引，可以提高检索数据的速度。

sql">CREATE INDEX idx_name ON user(name);

这条语句在user表的name字段上创建了一个索引。

4. 非空约束 (Not Null)

非空约束确保某个字段在插入数据时不能为空。

sql">status CHAR(1) NOT NULL DEFAULT '1' COMMENT '状态'

在用户表中，status字段被定义为非空，并且有默认值为1。

5. 默认约束 (Default)

默认约束用于为某个字段指定默认值，当插入数据时未提供该字段的值时，将使用默认值。

sql">status CHAR(1) NOT NULL DEFAULT '1' COMMENT '状态'

如果插入用户数据时没有指定status，则默认值为1。

6. 检查约束 (Check)

检查约束用于限制列中的值，确保其符合特定条件。

sql">age INT CHECK (age > 0 AND age < 120) COMMENT '年龄'

在用户表中，age字段的值必须在0到120之间。

7. 外键约束 (Foreign Key)

外键约束用于在两张表之间建立联系，确保数据的一致性。外键字段的值必须在被引用的表中存在。

sql">INSERT INTO user(name, age, status, gender) VALUES
('Tomm', 25, '1', '男'),
('Jerry', 30, '0', '女'),
('Lily', 20, '1', '女');INSERT INTO dept (id, name) VALUES
(1, '研发部'),
(2, '市场部'),
(3, '财务部'),
(4, '销售部'),
(5, '总经办');INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
(1, '金席', 66, '总裁', 20000, '2000-01-01', NULL, 5),
(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
(3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
(5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

实现外键约束

在员工表中，dept_id字段作为外键引用了部门表的id字段，确保每个员工都属于一个有效的部门。现在创建了两张表，员工表和部门表，但并没有实现外键约束，我们需要通过下面代码实现外键约束。

sql">ALTER TABLE emp ADD CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE CASCADE ON UPDATE CASCADE;

效果展示：

部门表

员工表：

实现外键约束后他会多出一列，dept_id ，蓝色小钥匙就代表外键

删除外键约束

sql">-- 删除外键
alter table emp drop foreign key fk_dept_id;

外键约束删除/更新行为约束

restrict 和 no action 是默认的，他们两个的效果是一样的，如果不设置行为，就会默认是他们两，删除父表中的关联部分，会报错，因为有外键的约束

CASCADE：级联删除或更新。
RESTRICT：限制删除或更新。
NO ACTION：无操作。
SET NULL：设置为NULL。
SET DEFAULT：设置为默认值。

设置行为的话，只需要向下面代码一样，在最后面加上 on delete cascade on update cascade; 当然 cascade 可以替换其他行为

sql">-- 指定外键约束 删除/更新 行为-- cascade: 级联删除/更新-- restrict: 限制删除/更新-- no action: 无操作-- set null: 设置为null-- set default: 设置为默认值alter table emp add constraint fk_dept_id foreign key (dept_id) references dept(id) on delete cascade on update cascade;