Oracle主键和外键详解及实用技巧

本文主要是介绍Oracle主键和外键详解及实用技巧，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在 Oracle 数据库中，主键（Primary Key）和外键（Foreign Key）用于维护数据库表之间的数据完整性。

1. 主键（Primary Key）

主键是一列或多列，能够唯一标识表中的每一行。表中只能有一个主键，并且主键列不能为空（即 NOT NULL）。

特性：

唯一性：主键中的每一个值都是唯一的，不能重复。
非空性：主键列不能包含 NULL 值。
索引：Oracle 自动为主键创建唯一索引，提升查询性能。

常用操作：

创建表时指定主键

CREATE TABLE employees (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(100), department_id NUMBER
);

在 emp_id 列上创建了主键。

为现有表添加主键
如果表已经存在，但还没有主键，可以使用 ALTER TABLE 命令添加主键。
```
ALTER TABLE employees ADD CONSTRAINT pk_emp_id PRIMARY KEY (emp_id);
```
删除主键
如果需要删除主键，可以使用以下语法：
```
ALTER TABLE employees DROP PRIMARY KEY;
```

查看主键
查询某张表的主键可以通过 USER_CONSTRAINTS 视图：

SELECT constraint_name 
FROM user_constraints 
WHERE table_name = 'EMPLOYEES' AND constraint_type = 'P';

2. 外键（Foreign Key）

外键用于在两个表之间建立关联，通常是从一个表（子表）的列引用另一个表（父表）的主键。外键用来确保引用完整性，意味着外键列的值必须存在于父表的主键中。

特性：

引用完整性：外键保证子表中的某些值在父表中必须存在。
级联操作：在父表中删除或更新主键时，外键可以选择级联操作（CASCADE）。

常用操作：

创建表时指定外键
在创建表时，可以同时指定外键约束：

CREATE TABLE departments (department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100)
);CREATE TABLE employees (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(100), department_id NUMBER,CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

这里 employees 表中的 department_id 列是 departments 表的外键。

为现有表添加外键
可以使用 ALTER TABLE 语句为已经存在的表添加外键：

ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);

删除外键
如果需要删除外键，可以使用以下语法：
```
ALTER TABLE employees DROP CONSTRAINT fk_department;
```
级联删除（ON DELETE CASCADE）
当删除父表中的记录时，可以设置外键的行为，例如级联删除子表中的相关记录：
```
ALTER TABLE employees ADD CONSTRAINT fk_department 
FOREIGN KEY (department_id) 
REFERENCES departments(department_id) 
ON DELETE CASCADE;
```
当父表 departments 中的 department_id 被删除时，employees 表中对应的行也会被删除。

查看外键
查询某张表的外键可以通过 USER_CONSTRAINTS 视图：

SELECT constraint_name 
FROM user_constraints 
WHERE table_name = 'EMPLOYEES' AND constraint_type = 'R';

3. 外键的约束选项

ON DELETE CASCADE：当父表中的主键记录被删除时，子表中相关的记录也自动删除。
ON DELETE SET NULL：当父表中的主键记录被删除时，子表中相关的外键列设置为 NULL。
ON UPDATE CASCADE：在 Oracle 中不支持此选项。

示例操作：

带外键和主键的表操作

-- 创建部门表，包含主键
CREATE TABLE departments (department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100)
);-- 创建员工表，包含外键引用
CREATE TABLE employees (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(100), department_id NUMBER,CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id)ON DELETE CASCADE
);

插入和删除数据时的外键行为

-- 向父表插入数据
INSERT INTO departments (department_id, department_name) VALUES (1, 'HR');
INSERT INTO departments (department_id, department_name) VALUES (2, 'IT');-- 向子表插入数据
INSERT INTO employees (emp_id, emp_name, department_id) VALUES (101, 'Alice', 1);
INSERT INTO employees (emp_id, emp_name, department_id) VALUES (102, 'Bob', 2);-- 删除父表数据时，子表中的相关记录也会被删除
DELETE FROM departments WHERE department_id = 1;