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

Z时代
2024-01-10
分类：综合

问题：

您要使用主键约束和外键约束来强制Oracle中的数据库完整性。

解

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

让我们看一些显示主键约束的实施的示例。customer表的主键是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表称为子表，反映了product表中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

如果您尝试将产品表中一行的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 );