从NOT NULL到FOREIGN KEY，Oracle约束的五大关键应用

周同学的技术栈

已于 2024-08-17 22:49:13 修改

阅读量531

点赞数 19

分类专栏：数据库技术之路文章标签：数据库

于 2024-08-17 22:48:09 首次发布

本文链接：https://blog.csdn.net/zgt_certificate/article/details/141288413

版权

数据库技术之路专栏收录该内容

130 篇文章 3 订阅

订阅专栏

在这里插入图片描述

1. Oracle数据库约束的概念：

约束是用于强制数据库表中数据完整性的一种规则。在数据表中插入或修改数据时，必须遵守预先定义的约束条件。例如，员工的性别必须是“男”或“女”，部门编号必须是已经存在的部门编号等。约束可以确保数据库中数据的一致性和准确性。

2. 确保数据完整性的三种方法：

在Oracle数据库中，确保数据完整性主要有三种方法：
1. 应用程序代码：使用编程逻辑在插入或更新数据前进行验证。虽然灵活，但需要编写大量代码，维护复杂。
2. 触发器：在数据表上定义触发器，自动执行数据验证和业务逻辑。触发器的维护性高，但效率较低。
3. 约束：直接在数据库表上定义约束，强制执行数据规则。约束不仅维护方便，还能提高数据操作的性能，是确保数据完整性的最佳选择。

3. 定义约束的两种方式：

约束可以在创建表时直接定义，也可以在表创建之后单独添加。

创建表时定义约束：在编写CREATE TABLE语句时，同时指定表中各个字段的约束。例如：

CREATE TABLE student (
  sno number(8) PRIMARY KEY,
  sname char(8) NOT NULL,
  gender char(2) CHECK(gender in ('男', '女')),
  birthday date,
  school varchar(40)
);

创建表后定义约束：可以在表创建后，使用ALTER TABLE语句为表中的列添加约束。这种方式较为灵活，适合已有数据的表。例如：
```
ALTER TABLE student ADD CONSTRAINT chk_gender CHECK (gender in ('男', '女'));
```

4. Oracle中五种常见的约束类型：

Oracle数据库提供了五种常见的约束类型，用于确保不同场景下数据的完整性：
1. NOT NULL约束：用于确保某列不能为空。例如，在dept表中，列deptno定义为NOT NULL，插入或更新数据时，必须为该列提供一个非空值。
  - 示例：
```
INSERT INTO dept (deptno) VALUES (70);  -- 必须指定一个非空的deptno值
```
2. UNIQUE约束：用于确保某列的数据唯一，但允许空值。例如，在dept表中的dname列设置UNIQUE约束，确保部门名称不重复。
  - 示例：
```
ALTER TABLE dept ADD CONSTRAINT unq_dname UNIQUE (dname);
```
3. PRIMARY KEY约束：用于唯一标识表中的一行数据，不能有重复值或空值。一个表只能有一个主键，通常在创建表时定义。
  - 示例：
```
CREATE TABLE employee (
  emp_id number(10) PRIMARY KEY,
  emp_name varchar(50)
);
```
4. FOREIGN KEY约束：用于建立两个表之间的关系，确保一个表中的数据引用另一个表中的主键数据。例如，在emp表中，deptno列作为外键，引用dept表中的主键deptno。
  - 示例：
```
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno);
```
5. CHECK约束：用于指定列的值必须满足的条件。例如，员工的工资必须在1000到8000之间。
  - 示例：
```
ALTER TABLE emp ADD CONSTRAINT chk_salary CHECK (salary BETWEEN 1000 AND 8000);
```

5. 查询表中已存在的约束信息：

Oracle数据库中的约束信息被记录在数据字典中，可以使用查询语句来检索。相关的数据字典表包括user_constraints和user_cons_columns。

查询表中的约束信息：

通过查询user_constraints，可以获取表中所有约束的名称、类型和状态等信息。

示例：

SELECT constraint_name AS 名称, constraint_type AS 约束类型, status AS 状态 
FROM user_constraints 
WHERE table_name = 'STUDENT4';

查询约束在哪些列上定义：
- 通过查询user_cons_columns，可以进一步查看具体某个约束施加在哪些列上。
  - 示例：
```
SELECT constraint_name, column_name 
FROM user_cons_columns 
WHERE table_name = 'STUDENT4';
```