ORACLE外键约束

-- 创建测试主表. ID 是主键.

CREATE TABLE test_main (

 id      INT,

 value   VARCHAR(10),

 PRIMARY KEY(id)  

);

 

 

-- 创建测试子表. 

CREATE TABLE test_sub (

  id      INT,

 main_id INT,

 value   VARCHAR(10),

 PRIMARY KEY(id)  

);

 

 

-- 插入测试主表数据.

INSERT INTO test_main(id, value) VALUES (1,'ONE');

INSERT INTO test_main(id, value) VALUES (2,'TWO');

 

-- 插入测试子表数据.

INSERT INTO test_sub(id, main_id, value)VALUES (1, 1, 'ONEONE');

INSERT INTO test_sub(id, main_id, value)VALUES (2, 2, 'TWOTWO');

 

 

 

默认外键约束方式

 

SQL> -- 创建外键(默认选项)

SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons  FOREIGN KEY (main_id)  REFERENCES test_main;

 

Table altered.

 

SQL>

SQL> -- 测试删除主表数据. 将出错 ORA-02292:违反完整约束条件

SQL> DELETE

 2    test_main

 3  WHERE

 4    ID = 1;

DELETE

*

ERROR at line 1:

ORA-02292: integrity constraint(HR.MAIN_ID_CONS) violated - child record found

 

 

测试完毕后,删除外键约束

ALTER TABLE test_subDROP CONSTRAINT main_id_cons;

 

 

 


 

 

DELETE CASCADE 方式

 

SQL> -- 创建外键(使用 ON DELETECASCADE 选项,删除主表的时候,同时删除子表)

SQL> ALTER TABLE test_sub

  2    ADDCONSTRAINT main_id_cons

  3     FOREIGN KEY (main_id) REFERENCES  test_main  ON DELETE  CASCADE;

 

Table altered.

 

SQL>

SQL> -- 测试删除主表数据. 将成功地执行.

SQL> DELETE

 2    TEST_MAIN

 3  WHERE

 4    ID = 1;

 

1 row deleted.

 

SQL>

SQL> -- 测试检索子表,应该只有一条 main_id = 2 的数据.

SQL> SELECT

 2    *

 3  FROM

 4    test_sub;

 

       ID    MAIN_ID VALUE

---------- ---------- --------------------

         2          2 TWOTWO

 

 

测试完毕后,删除外键约束 

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

 

 

 

SET NULL方式

 

SQL> -- 创建外键(使用 ON DELETESET NULL 选项,删除主表的时候,同时将子表的 main

_id 设置为 NULL)

SQL> ALTER TABLE test_sub

  2   ADD CONSTRAINT main_id_cons

  3     FOREIGN KEY (main_id) REFERENCES  test_main  ON DELETE SET NULL;

 

Table altered.

 

SQL>

SQL>

SQL> -- 测试删除主表数据. 将成功地执行.

SQL> DELETE

 2    TEST_MAIN

 3  WHERE

 4    ID = 2;

 

1 row deleted.

 

SQL>

SQL> -- 测试检索子表,应该有一条的 main_id = null

SQL> SELECT

 2    *

 3  FROM

 4    test_sub;

 

       ID    MAIN_ID VALUE

---------- ---------- --------------------

         2            TWOTWO

 

测试完毕后,删除外键约束 

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

 

 

 

 

 

 

启用/禁用

 

当遇到批量数据导入的时候,如果外键处于有效的状态。
那么要求 数据导入的程序, 必须先导入主表的数据,然后再导入子表的数据。
如果表多,且关系复杂的话,会增加很大的工作量。

可以在数据导入以前, 先暂时禁用外键约束。
子表、主表数据都导入完毕后, 再启用外键约束。

 

以下测试环境为 初始的创建表、初始数据的情况。

 


SQL> -- 创建外键(默认选项)
SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons  FOREIGN KEY (main_id)  REFERENCES  test_main;

表已更改。

SQL> -- 测试插入子表数据. 将出错 ORA-02291: 违反完整约束条件
SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE');
INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE')
*
ERROR 位于第 1 行:
ORA-02291: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 未找到父项关键字

 

 

-- 尝试禁用外键约束.
SQL> ALTER TABLE test_sub  MODIFY   CONSTRAINT main_id_cons DISABLE;
表已更改。


SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (3, 3, 'THREETHREE');
已创建 1 行。

SQL> INSERT INTO test_main(id, value) VALUES (3, 'THREE');
已创建 1 行。

SQL> commit;
提交完成。

 

 

-- 恢复启用外键约束.
SQL> ALTER TABLE test_sub  MODIFY   CONSTRAINT main_id_cons  ENABLE;
表已更改。


SQL> DELETE test_main  WHERE ID = 1;
DELETE test_main  WHERE ID = 1
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 已找到子记录

 

 

测试完毕后,删除外键约束 

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

 

 

 

 

延迟约束

 


在编程开发环境中,可能会遇到,要先插入子表, 然后插入主表。
或者先更新主表的 主键, 然后更新子表的外键的情况。


由于是开发环境下,因此不适合使用 先 DISABLE 再 ENABLE 的处理机制。
原因是程序中可能会存在并发处理

也就是

用户A   DISABLE -- 更新处理  -- ENABLE
用户B              DISABLE -- 更新处理  -- ENABLE


这种情况下,需要启用 延迟约束
也就是 INSERT / UPDATE 语句执行的时候, 暂时不检查数据的完整性,等到 Commit 的时候,统一做检查。

 

 

 


SQL> -- 创建外键(默认选项)
SQL> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons 
  2    FOREIGN KEY (main_id)  REFERENCES  test_main 
  3    DEFERRABLE   INITIALLY DEFERRED;
表已更改。


SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (4, 4, 'FOURFOUR');

已创建 1 行。

SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已回退
ORA-02291: 违反完整约束条件 (TEST.MAIN_ID_CONS) - 未找到父项关键字

 


SQL> INSERT INTO test_sub (ID, MAIN_ID, VALUE) VALUES (4, 4, 'FOURFOUR');
已创建 1 行。

SQL> INSERT INTO test_main(id, value) VALUES (4, 'FOUR');
已创建 1 行。

SQL> commit;
提交完成。

 

外键索引

啥时不需要外键索引?
Expert one-on-one Oracle中提到:

  • You do not delete from the parent table.
  • You do not update the parent tables unique/primary key value, either purposely or by accident (via a tool).

这两种情况下,没有外键索引,Oracle会锁子表,当使用ON DELETE CASCADE时,会进行全表扫描。

  • You do not join from the parent table to the child table, of more generally ‐ the foreign key columns do not support an important access path to the child table.

利用join查询时,没有外键索引,Oracle就会进行全表扫描。

当确认这些情况不会出现时,便可以不添加外键索引,但是,如何监测子表是否被锁呢?
Oracle有一个机制:
ALTER TABLE <child table name> DISABLE TABLE LOCK;
这时,UPDATE和DELETE获取锁是都会失败。

 

不从父表删除记录很难避免,因此,如果没有外键索引,将很容易导致死锁,缺外键索引是死锁的主要原因,另外,位图索引的并发更新也是可能导致死锁。

 

以下是ORACLE Database Concept上的描述,记录一下:

Indexes and Foreign Keys

As a rule, foreign keys should be indexed. The only exception is when the matching unique or primary key is never updated or deleted. Indexing the foreign keys in child

tables provides the following benefits:

■ Prevents a full table lock on the child table. Instead, the database acquires a row

lock on the index.

■ Removes the need for a full table scan of the child table. As an illustration,

assume that a user removes the record for department 10 from the departments

table. If employees.department_id is not indexed, then the database must scan

employees to see if any employees exist in department 10.

 

### 回答1: Oracle 中的外键约束用于维护表之间的引用完整性,它定义了一个表中的一列或多列与另一个表中的一列或多列之间的关系。这个关系可以是单向的,也可以是双向的。 在 Oracle 中,创建外键约束需要满足以下条件: 1. 外键列和所引用的主键列的数据类型必须相同。 2. 外键列和所引用的主键列的长度必须相同。 3. 外键列和所引用的主键列必须都已经定义为 NOT NULL。 4. 外键列必须是索引列。 创建外键约束的语法如下: ``` ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 FOREIGN KEY (外键列名称) REFERENCES 主键表名称 (主键列名称); ``` 其中,表名称是指需要添加外键约束的表名,约束名称是自定义的外键约束名称,外键列名称是需要添加外键约束的列名,主键表名称是被引用的表名,主键列名称是被引用表的主键列名。 例如,创建一个名为 "orders" 的表,它有一个外键列 "customer_id",引用了 "customers" 表的 "id" 列: ``` ALTER TABLE orders ADD CONSTRAINT orders_fk1 FOREIGN KEY (customer_id) REFERENCES customers (id); ``` ### 回答2: Oracle 外键约束是一种用于确保数据完整性的约束。它通过在列级别上定义关联关系,限制了一个表中的外键与另一个表中的主键或唯一键之间的关联关系。 外键约束可以用来保证数据的一致性和完整性。当我们在一个表中创建外键约束时,它会将指定列与另一个表的主键或唯一键进行关联。这样,在插入、更新或删除数据时,系统会自动检查外键约束,确保数据操作不会破坏关联关系。 外键约束可以有以下几个特点: 1. 外键关系必须在参照表上存在主键或唯一键约束。 2. 外键列的数据类型必须与参照列的数据类型匹配。 3. 外键列的值必须是参照表的主键或唯一键列中存在的值。 4. 当参照表的主键或唯一键更新或删除时,涉及到外键的操作也会受到影响。 外键约束的作用有以下几点: 1. 数据完整性:外键约束可以保证在关联表之间维护数据的一致性,防止无效或不合法数据的插入或更新。 2. 级联操作:定义级联操作可以在参照表上进行更新或删除操作时同时更新或删除关联的外键表中的数据,避免了手动处理关联数据的复杂性。 3. 查询优化:外键约束可以帮助优化查询,提高数据检索性能。 4. 数据模型的清晰性:通过外键约束,可以更清晰地定义表之间的关联关系,方便他人理解数据模型。 总之,Oracle 外键约束是一种强制性的关联关系,可以保证数据的一致性和完整性,提高数据库查询性能,并且使数据模型更加清晰易懂。 ### 回答3: Oracle外键约束是一种用来保持数据完整性的约束。它定义了两个表之间的关系,并防止数据库中的无效数据。外键约束在两个表之间创建引用,一个表中的一个列是另一个表的主键或唯一键。 通过外键约束,我们可以确保在从表中的外键列只包含在主表中已经存在的值。当我们向从表中插入或更新数据时,如果外键约束被违反,Oracle将抛出一个错误,阻止操作继续进行。 外键约束可以执行以下操作: 1. 防止删除或更新主表中的行,如果在从表中的外键列中仍然存在引用。这可以防止出现孤儿记录。 2. 防止插入或更新从表中的行,如果在主表中没有对应的主键或唯一键值。这可以防止出现无效的引用。 通过使用外键约束,我们可以确保数据库中的数据完整性和一致性。它可以减少数据错误和无效引用的可能性,并确保数据库中的相关数据保持一致。 外键约束可以在创建表时定义,也可以在后期通过ALTER TABLE语句添加。我们可以选择指定外键约束的名称,以及在违反约束时的动作,如RESTRICT、CASCADE或SET NULL。 总而言之,Oracle外键约束可以确保在表之间保持有效关系,防止无效引用和孤儿记录,并维护数据库的数据完整性和一致性。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值