如何在 Oracle 中使用主键约束和外键约束来增强数据库完整性？

问题

您想在 Oracle 中使用主键约束和外键约束来增强数据库完整性。

解决方案

任何在定义了完整性的表上执行的 DML 语句（INSERT、UPDATE 或 DELETE）都有助于确保表中的行保持其完整性。

让我们看一些示例，这些示例展示了主键约束的执行方式。customers 表的主键是 customer_id 列，这意味着存储在 customer_id 列中的每个值都必须是唯一的。如果您尝试插入主键值重复的行，数据库将返回错误 ORA-00001，如下例所示。

示例

INSERT INTO customers (customer_id, first_name, last_name, dob, phone) VALUES (1, 'Roger', 'Federer', '01-JAN-83', '001-001-0001');

INSERT INTO customers (
*
ERROR at line 1:
ORA-00001: unique constraint (STORE.CUSTOMERS_PK) violated

如果您尝试将主键值更新为表中已存在的值，数据库将返回相同的错误。

示例

UPDATE customers
   SET customer_id = 1
 WHERE customer_id = 2;
 
ERROR at line 1:
ORA-00001: unique constraint (STORE.CUSTOMERS_PK) violated

外键关系是指一个表中的列在另一个表中被引用的关系。例如，products 表中的 product_type_id 列引用 product_types 表中的 product_type_id 列。product_types 表称为父表，products 表称为子表，反映了 products 表中 product_type_id 列对 product_types 表中 product_type_id 列的依赖性。

如果您尝试将不存在的 product_type_id 插入到 products 表中，数据库将返回错误 ORA-02291。此错误表示数据库找不到匹配的父键值。

示例

INSERT INTO products (product_id, product_type_id, name, description, price) VALUES (999, 999, 'Test product', 'Test Product', 23);
INSERT INTO products (
*
ERROR at line 1:
ORA-02291: integrity constraint (STORE.PRODUCTS_FK_PRODUCT_TYPES)
 violated - parent key not found

如果您尝试将 products 表中某一行的 product_type_id 更新为不存在的父键值，数据库将返回相同的错误。

示例

UPDATE products
   SET product_type_id = 999
 WHERE product_id = 999;
 
UPDATE products
*
ERROR at line 1:
ORA-02291: integrity constraint (STORE.PRODUCTS_FK_PRODUCT_TYPES)
 violated - parent key not found

最后，如果您尝试删除父表中具有从属子行的行，数据库将返回错误 ORA-02292。例如，如果您尝试从 product_types 表中删除 product_type_id 为 1 的行，数据库将返回此错误，因为 products 表包含 product_type_id 为 1 的行。

示例

 DELETE FROM product_types
  WHERE product_type_id = 1;

DELETE FROM product_types
*
ERROR at line 1:
ORA-02292: integrity constraint (STORE.PRODUCTS_FK_PRODUCT_TYPES)
 violated - child record found

上述问题使用的表定义。

示例

--------------create scripts----------------------------
create table customers (
   customer_id     integer generated by default on null as identity,
   email_address   varchar2(255 char) not null,
   full_name       varchar2(255 char) not null)
 ;
 
create table stores (
   store_id          integer generated by default on null as identity ,
   store_name        varchar2(255 char) not null,
   web_address       varchar2(100 char),
   physical_address  varchar2(512 char),
   latitude          number,
   longitude         number,
   logo              blob,
   logo_mime_type    varchar2(512 char),
   logo_filename     varchar2(512 char),
   logo_charset      varchar2(512 char),
   logo_last_updated date)
 ;
 
create table products (
   product_id         integer generated by default on null as identity ,
   product_name       varchar2(255 char) not null,
   unit_price         number(10,2),
   product_details    blob,
   product_image      blob,
   image_mime_type    varchar2(512 char),
   image_filename     varchar2(512 char),
   image_charset      varchar2(512 char),
   image_last_updated date)
 ;
 
 
--------------------------constraints--------------------------------------
 
alter table customers add constraint customers_pk primary key (customer_id);

alter table customers add constraint customers_email_u unique (email_address);

alter table stores add constraint stores_pk primary key (store_id);

alter table stores add constraint store_name_u unique (store_name);

alter table stores add constraint store_at_least_one_address_c
  check (
    web_address is not null or physical_address is not null
  );

alter table products add constraint products_pk primary key (product_id);

alter table products add constraint products_json_c
                     check ( product_details is json );

Kiran P

更新于： 2020-12-05

458 次查看

开启您的 职业生涯

通过完成课程获得认证

开始学习