维护数据完整性（二）_oracle创建不记录日志的索引-优快云博客

本文链接：https://blog.youkuaiyun.com/yuanymnl/article/details/145100132

主键约束

主键具有唯一键的所有特征，不同之处在于主键列中不允许有NULL值。
一个表只能有一个主键。
Oracle为键中的每个列创建一个唯一索引和非空约束。如果现有索引包含主键的所有列，Oracle可以使用该索引。
以下示例在创建表时同时定义了表的主键和主键索引。存储参数可以显式指定。用于强制唯一键和主键的索引可以像其他索引一样进行管理。然而，这些索引不能被删除。

定义：主键是数据库表中的一个列或列集，用于唯一标识表中的每一行。在关系数据库中，主键是至关重要的，因为它确保了数据的完整性。
唯一性：主键的值必须是唯一的，这意味着表中的任何两行都不能有相同的主键值。
非空性：主键列不能包含NULL值。每一行都必须有一个主键值。
不可变性：一旦主键值被分配给某一行，它就不能更改。如果需要更改主键值，通常需要删除该行并重新插入。
性能考虑：由于主键列通常用于连接操作，因此它们对查询性能有重要影响。主键上的索引可以加快数据检索速度。
复合主键：主键可以由多个列组成，这称为复合主键。在这种情况下，列组合的值必须是唯一的。
自动增长：在某些数据库系统中，如MySQL，可以设置主键列为自动增长，这样每次插入新行时，数据库会自动为该列生成一个唯一的值。
替代键：如果表中没有明显的候选主键，可以使用替代键（也称为代理键）作为主键，这通常是一个没有业务含义的唯一值，如序列号或GUID。
外键关系：主键经常用作其他表中外键的参照，从而在表之间建立关系。
管理索引：虽然用于强制执行主键的唯一性和非空性的索引不能被直接删除，但可以通过删除主键约束来间接删除它们。一旦删除了主键约束，相关的索引也会被删除。
存储参数：在创建主键时，可以指定存储参数，如PCTFREE、PCTUSED等，这些参数可以影响索引的性能和存储。

例子

这段SQL代码是在Oracle数据库中创建一个名为EMPLOYEE的表。以下是代码的详细解释：

CREATE TABLE EMPLOYEE (
  DEPT_NO VARCHAR2 (2),        -- 部门编号，字符类型，长度为2
  EMP_ID NUMBER (4),           -- 员工编号，数字类型，长度为4
  NAME VARCHAR2 (20) NOT NULL, -- 员工姓名，字符类型，长度为20，且不允许为空
  SSN VARCHAR2 (11),           -- 社会安全号码，字符类型，长度为11
  SALARY NUMBER (9,2) CHECK (SALARY > 0), -- 工资，数字类型，总长度为9，小数点后2位，且必须大于0
  CONSTRAINT PK_EMPLOYEE PRIMARY KEY (DEPT_NO, EMP_ID) -- 主键约束，由DEPT_NO和EMP_ID组合而成
  USING INDEX TABLESPACE INDX STORAGE (INITIAL 64K NEXT 64K) -- 指定主键索引的表空间为INDX，并设置存储参数
  NOLOGGING, -- 创建索引时不记录日志，可以提高创建速度，但可能会影响数据恢复
  CONSTRAINT UQ_SSN UNIQUE (SSN) -- 唯一约束，确保SSN列的值是唯一的
  USING INDEX TABLESPACE INDX -- 指定唯一约束索引的表空间为INDX
)
TABLESPACE USERS STORAGE (INITIAL 128K NEXT 64K); -- 指定表EMPLOYEE的表空间为USERS，并设置存储参数

以下是关于这段代码的一些要点：

PRIMARY KEY (DEPT_NO, EMP_ID): 这定义了一个复合主键，由DEPT_NO和EMP_ID两列组成。
USING INDEX TABLESPACE INDX: 这指定了主键和唯一约束的索引应该存储在名为INDX的表空间中。
STORAGE (INITIAL 64K NEXT 64K): 这设置了索引的存储参数，其中INITIAL是初始区的大小，NEXT是后续扩展区的大小。
NOLOGGING: 这个选项用于优化性能，它告诉数据库在创建索引时不记录操作日志。这在数据加载等操作中很有用，但要注意，它可能会影响数据库的恢复能力。
UNIQUE (SSN): 这确保了SSN列中的每个值都是唯一的。
TABLESPACE USERS: 这指定了表EMPLOYEE的数据应该存储在名为USERS的表空间中。
STORAGE (INITIAL 128K NEXT 64K): 这设置了表的存储参数，与索引的存储参数类似。

外键

外键是在表中（子表）创建约束的列或列；被约束引用的键是主键、唯一键列或表中的列（父表）。以下规则适用于外键约束：

你可以在列级别或表级别定义外键约束。在表级别定义多列外键。
外键列和被引用键列可以在同一个表（自引用完整性约束）中。
外键列中允许有NULL值。以下是在CITY表的COUNTRY_CODE和STATE_CODE列上创建外键约束的示例，该约束引用了STATE表的COUNTRY_CODE和STATE_CODE列（STATE表的综合主键）。

参照完整性：外键约束确保了数据库的参照完整性，这意味着子表中的外键值必须与父表中的相应主键或唯一键值匹配，或者为NULL（如果允许）。
级联操作：当父表中的记录被更新或删除时，外键约束可以配置为执行级联更新或级联删除。例如，如果父表中的记录被删除，级联删除会自动删除所有相关的子表记录。
约束命名：在创建外键约束时，通常会为其命名，如示例中的FK_STATE。这是一个好习惯，因为它可以帮助在数据库中更容易地识别和管理约束。
多列外键：外键可以由多个列组成，这种情况通常称为复合外键。在创建复合外键时，子表中的外键列必须与父表中的主键或唯一键列的顺序和类型相匹配。
自引用外键：自引用外键是指外键引用同一表中的主键或唯一键。这种类型的约束常用于表示层次结构或组织结构，如员工表中的经理与员工的关系。
NULL值：外键列可以包含NULL值，这意味着子表中的记录不必与父表中的任何记录相关联。这是与主键的一个重要区别，主键列不能包含NULL值。
性能考虑：外键约束可能会对数据库的性能产生影响，因为数据库需要额外的资源来维护参照完整性。在设计数据库时，应该权衡外键约束的优点和性能成本。

术语	定义
外键	在参照完整性约束定义中，指向被引用键的列或列集。
被引用键	被外键引用的键，通常是主键或唯一键，可以位于相同或不同表中。
依赖或子表	包含外键的表，它依赖于父表中被引用键的值。
被引用或父表	包含被引用键的表，其值被子表中的外键所依赖。
参照完整性约束	确保子表中外键的值与父表中被引用键的值相匹配。

删除动作

SQL中用于管理参照完整性约束的几种不同的删除规则，它们定义了当父表（被引用表）中的记录被删除时，应该如何处理子表（引用表）中的相关记录。

DELETE NO ACTION

作用：当尝试删除父表中的记录，而该记录的主键值被子表中至少一个外键值引用时，NO ACTION规则将阻止删除操作。这是默认行为，旨在保护数据的完整性。
相关知识：NO ACTION规则在实际删除之前不会进行检查，而是在事务提交时进行检查。如果违反了约束，事务将被回滚。

DELETE CASCADE

作用：当删除父表中的记录时，CASCADE规则将自动删除所有引用该记录主键值的子表记录。这确保了数据库中不会留下孤立的子记录。
相关知识：CASCADE规则在删除操作中非常有用，尤其是在需要维护复杂的数据层次结构时。但是，使用时需要小心，因为它可能会导致大量数据的意外删除。

DELETE SET NULL

作用：当删除父表中的记录时，SET NULL规则将子表中所有引用该记录主键值的外键列设置为NULL。这允许子表中的记录保持存在，但断开了与被删除父记录的关联。
相关知识：SET NULL规则要求外键列允许NULL值。在使用此规则之前，需要确保子表的设计允许外键列为NULL。

ON DELETE子句用于指定当父表中的一行被删除且子表中存在引用该删除父主键的记录时应该采取的操作。您可以选择删除子行（CASCADE）或将外键列的值设置为NULL（SET NULL）。如果省略这个子句，如果存在子记录，Oracle将不允许您直接从父表删除记录。您必须先删除子记录，然后才能删除父记录。以下是两个在外键中指定删除操作的示例。

例子

以下是从提供的SQL命令中提取的步骤及其解释：

显示当前用户

SQL> show user
USER is "WILSON"

这条命令用于显示当前登录到SQL*Plus的用户名。输出表明当前用户是"WILSON"。
2. 创建部门表

SQL> CREATE TABLE dept (dept_id int, dept_name char(10));
Table created.

这条命令创建了一个名为dept的表，其中包含两个列：dept_id（整数类型）和dept_name（字符类型，长度为10个字符）。
3. 创建员工表

SQL> CREATE TABLE emp(emp_id int, name char(10), dept_id int);
Table created.

这条命令创建了一个名为emp的表，其中包含三个列：emp_id（整数类型），name（字符类型，长度为10个字符），和dept_id（整数类型），后者将用作外键。
4. 尝试添加外键约束（失败）

SQL> ALTER_TABLE emp ADD CONSTRAINT fk_emp1 FOREIGN KEY(dept_id)
2 REFERENCES dept(dept_id)
3 ON DELETE CASCADE;
REFERENCES dept(dept_id)
ERROR at line 2: ORA-02270: no matching unique or primary key for this column-list

这条命令尝试在emp表的dept_id列上添加一个名为fk_emp1的外键约束，该约束引用dept表的dept_id列，并指定了ON DELETE CASCADE动作。但是，命令执行失败，因为dept表的dept_id列还没有被设置为主键或唯一键，这是外键约束所必需的。
5. 在部门表上添加主键约束

SQL> ALTER TABLE dept ADD PRIMARY KEY(dept_id);
Table altered.

这条命令在dept表的dept_id列上添加了一个主键约束，这满足了外键约束的要求。
6. 重新添加外键约束（成功）

SQL> ALTER TABLE emp ADD CONSTRAINT fk_emp1 FOREIGN KEY(dept_id)
2 REFERENCES dept(dept_id)
3 ON DELETE CASCADE;
Table altered.

在dept表上成功添加了主键约束后，这条命令再次尝试在emp表的dept_id列上添加外键约束fk_emp1，这次成功了。这个外键约束指定了如果dept表中的一行被删除，所有在emp表中引用该行的记录也将被级联删除。
这个序列说明了在创建外键约束之前，必须确保被引用的列在父表中有一个唯一或主键约束。如果没有这样的约束，外键约束的创建将会失败。

创建禁用约束

创建禁用约束：在创建约束时，它将自动启用。你可以在约束定义后指定DISABLED关键字来创建一个禁用的约束。例如：

ALTER TABLE CITY ADD CONSTRAINT FK_STATE FOREIGN KEY (COUNTRY_CODE, STATE_CODE)
REFERENCES STATE (COUNTRY_CODE, STATE_CODE) DISABLE;
ALTER TABLE BONUS ADD CONSTRAINT CK_BONUS CHECK (BONUS > 0) DISABLE;

扩展知识：
在Oracle数据库中，约束是用来确保数据的完整性和准确性的规则。当你创建一个约束时，数据库会自动检查现有数据以确保它们符合约束条件。如果现有数据违反了约束，那么约束的创建将会失败。然而，在某些情况下，你可能希望在创建约束时不立即检查现有数据，或者你可能不希望约束立即影响数据的插入、更新或删除操作。这时，你可以创建一个禁用的约束。
以下是关于禁用约束的一些额外信息：

禁用约束的优点：
- 允许你在不违反现有数据的情况下添加约束。
- 可以在维护操作期间暂时禁用约束，以避免约束检查带来的性能开销。
- 可以在数据迁移或数据修正期间禁用约束，以便于数据的批量操作。
禁用约束的步骤：
- 在创建约束时使用DISABLE关键字，如上面的例子所示。
- 如果你想要在之后启用约束，可以使用ALTER TABLE ... ENABLE CONSTRAINT命令。
启用约束的步骤：
- 使用ALTER TABLE ... ENABLE CONSTRAINT命令来启用约束。
- 在启用约束之前，确保所有数据都符合约束条件，否则启用操作将会失败。
注意事项：
- 禁用的约束不会在数据操作时进行检查，这可能会导致数据不一致。
- 在启用约束之前，可能需要进行数据清理或修正操作。
  使用禁用约束是一种管理数据库变更和保证数据完整性的有效方法，但它需要谨慎使用，以确保不会引入数据不一致的问题。

删除约束

删除约束：要删除约束，可以使用ALTER TABLE语句。你可以通过指定约束名称来删除任何约束。

ALTER TABLE BONUS DROP CONSTRAINT CK_BONUS2;

要删除带有被引用外键的唯一键约束，请使用CASCADE子句来同时删除外键约束和唯一约束。需要指定唯一键的列。例如：

ALTER TABLE EMPLOYEE DROP UNIQUE (EMP ID) CASCADE;

要删除带有被引用外键约束的主键约束，请使用CASCADE子句先删除所有相关的外键约束，然后再删除主键。

ALTER TABLE BONUS DROP PRIMARY KEY CASCADE;

扩展知识：
在数据库管理中，删除约束是一个常见的操作，特别是在进行数据库重构或清理不再需要的规则时。以下是一些关于删除约束的额外信息：

删除约束的注意事项：
- 在删除约束之前，确保理解该约束的作用以及删除它可能带来的影响。
- 如果约束与其他表中的外键约束相关联，那么删除该约束可能会导致级联删除相关的外键约束，这可能会影响数据的完整性。
- 删除约束可能会使数据库中的数据处于不一致的状态，因此在进行此类操作之前应该进行充分的备份。
CASCADE子句的作用：
- CASCADE子句用于自动删除所有依赖于要删除的约束的其他约束。例如，如果你删除一个主键约束，所有引用该主键的外键约束也将被自动删除。
- 使用CASCADE子句时，操作将是原子性的，要么全部成功，要么全部失败，这有助于保持数据库的完整性。
删除约束的步骤：
- 确定要删除的约束的名称。
- 使用ALTER TABLE语句和DROP CONSTRAINT子句来删除约束。
- 如果约束与其他约束有关联，考虑是否需要使用CASCADE子句。
检查约束依赖关系：
- 在删除约束之前，可以使用数据库的数据字典或系统视图来检查约束的依赖关系。
- 例如，在Oracle数据库中，可以使用USER_CONSTRAINTS和USER_CONS_COLUMNS数据字典视图来查看约束的详细信息。
  删除约束是一个需要谨慎操作的过程，因为它可能会对数据库的结构和数据完整性产生重大影响。因此，在进行此类操作之前，应该进行充分的规划和测试。

添加主键约束（但语法错误）:
```
SQL> ALTER TABLE t ADD PRIMARY KEY(id) DISABLE;
Table altered.
```
这条命令尝试向表t添加一个禁用主键约束。
正确执行后，表t应该会被修改以包含一个主键约束，但这里因为语法错误，实际上并没有正确添加主键约束。

查询用户对象:

SQL> select object_name, object_type from user_objects;
OBJECT_NAME   OBJECT_TYPE
T             TABLE

这条命令查询当前用户拥有的所有对象及其类型。结果显示有一个名为t的表。

插入重复的主键值:
```
SQL> INSERT INTO t VALUES(0, ' boobooke');
1 row created.
SQL> INSERT INTO t VALUES(0, ' boobooke');
1 row created.
```
这里两次向表t中插入相同的ID值0，因为之前没有正确添加主键约束，所以数据库允许插入重复的ID。
提交事务:
```
SQL> COMMIT;
Commit complete.
```
提交事务后，插入的数据被永久保存到数据库中。
查询表数据:
```
SQL> SELECT * FROM t;
ID  NAME
0   boobooke
0   boobooke
```
查询表t中的所有数据，结果显示有两个具有相同ID的行。
尝试启用主键约束（不验证现有数据）:
```
SQL> ALTER TABLE t ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE t ENABLE NOVALIDATE PRIMARY KEY
ERROR at line 1:
ORA-02437: cannot validate (WILSON.SYS_c003926) - primary key violated
```
尝试启用主键约束，但不验证现有数据。然而，因为表中已经存在重复的ID值，违反了主键的唯一性约束，所以操作失败，并抛出错误ORA-02437。
总结来说，这段操作中存在几个问题：

命令中存在语法错误，导致主键约束没有被正确添加。
在没有主键约束的情况下，插入了重复的ID值。
尝试启用主键约束时，因为存在重复的ID值，导致操作失败。

以下是对数据库约束状态及其转换的有序组织和一些额外的知识点及注意事项：

约束状态定义：

DISABLE：
- 约束存在，但不检查数据。
- 允许违反约束的数据操作。
NOVALIDATE：
- 约束存在，检查新数据，但不检查现有数据。
- 用于创建约束时，避免全面检查现有数据。
ENABLE NOVALIDATE：
- 约束存在，检查新数据，不检查现有数据。
- 允许约束对新的数据操作进行检查。
ENABLE VALIDATE：
- 约束存在，检查所有数据，包括新数据和现有数据。
- 默认状态，确保数据完整性。

约束状态转换：

DISABLE 到 ENABLE NOVALIDATE：
1. ALTER TABLE ... DISABLE CONSTRAINT;
2. ALTER TABLE ... ENABLE NOVALIDATE CONSTRAINT;
DISABLE 到 ENABLE VALIDATE：
1. ALTER TABLE ... DISABLE CONSTRAINT;
2. ALTER TABLE ... ENABLE VALIDATE CONSTRAINT;
ENABLE NOVALIDATE 到 ENABLE VALIDATE：
1. ALTER TABLE ... ENABLE NOVALIDATE CONSTRAINT;
2. ALTER TABLE ... ENABLE VALIDATE CONSTRAINT;
ENABLE VALIDATE 到 DISABLE：
1. ALTER TABLE ... ENABLE VALIDATE CONSTRAINT;
2. ALTER TABLE ... DISABLE CONSTRAINT;
ENABLE VALIDATE 到 ENABLE NOVALIDATE：
1. ALTER TABLE ... ENABLE VALIDATE CONSTRAINT;
2. ALTER TABLE ... ENABLE NOVALIDATE CONSTRAINT;
ENABLE NOVALIDATE 到 DISABLE：
1. ALTER TABLE ... ENABLE NOVALIDATE CONSTRAINT;
2. ALTER TABLE ... DISABLE CONSTRAINT;

当然，以下是每个约束状态转换的完整 SQL 示例。假设我们有一个名为 employees 的表，并且有一个名为 emp_dept_fk 的外键约束，该约束关联到 departments 表的 dept_id 列。

DISABLE 到 ENABLE NOVALIDATE

-- 禁用约束
ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;
-- 启用约束，不验证现有数据
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT emp_dept_fk;

DISABLE 到 ENABLE VALIDATE

-- 禁用约束
ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;
-- 启用约束，并验证所有数据
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT emp_dept_fk;

ENABLE NOVALIDATE 到 ENABLE VALIDATE

-- 启用约束，不验证现有数据
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT emp_dept_fk;
-- 启用约束，并验证所有数据
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT emp_dept_fk;

其他的转换类似。

约束状态转换及其速度

当前状态 \ 目标状态	DISABLE	NOVALIDATE	ENABLE NOVALIDATE	ENABLE VALIDATE
DISABLE	N/A	快速	快速	慢速（需要验证所有数据）
NOVALIDATE	快速	N/A	N/A	慢速（需要验证所有数据）
ENABLE NOVALIDATE	快速	快速	N/A	慢速（需要验证所有数据）
ENABLE VALIDATE	快速	慢速（需要验证所有数据）	慢速（需要验证所有数据）	N/A