How to maintain referential integrity of an object graph when a node is deleted

Question

In a relational database, if a node in an object graph is deleted, how can the object graph's referential integrity be maintained?

For example:

A product is deleted, how is the invoice containing the order which had a reference to the product kept relevant, in that the order may no longer be relevant. Moreover, how is the order kept relevant?

Answer 1

Typically what you'd do in this scenario is, instead of deleting the products, just set a bit (or use an int type field if there could be more than 2 options) to denote that it is "deleted", or inactive.

create table Product
(
    Id int identity(1, 1) not null,
    Name varchar(100) not null,
    Description varchar(1000) null,
    IsActive bit not null default 1
)
go

Then, instead of calling a DELETE on that table for a product you'll no longer support, just do this:

create procedure DeleteProduct
    @ProdId int
as

    update Product
    set IsActive = 0
    where Id = @ProdId

go

That way you don't cascade delete your orders records, or make them completely useless by setting their foreign key references to NULL or DEFAULT.

That is a pretty specific use-case that you brought up in your question, which is why I geared the first part of my answer off of that. But the beauty of Referential Integrity is that this is handled by the RDBMS with direction of the database designer/developer. Typically, you'd choose exactly how you want that foreign key to be handled when the referenced primary key entry is deleted or updated.

You didn't specify an RDBMS in your question, and I can't speak for the specifics of other RDBMSes, but for SQL Server you can choose the following:

• ON DELETE NO ACTION : this will prevent the referenced primary key record from being deleted because it has a foreign key referenced (probably a good safetey net for your specific scenario question)
• ON DELETE CASCADE : this will delete the foreign key referencing record when the primary key referenced record is deleted
• ON DELETE SET NULL : sets the foreign key value to NULL when the referenced primary key record is deleted
• ON DELETE SET DEFAULT : similar to ON DELETE SET NULL, except instead of setting the referencing column to NULL, it'll set it to the DEFAULT value for that field

Answer 2

Suggestions:

1. Disallow deletion of a product that has been ordered.
2. Move the order detail row to an archive table that has no foreign key to products (perhaps denormalized to store product name etc that would otherwise be lost) then delete the product.
3. Move the product to an archive table and the order detail to another archive table with a foreign key reference to the product archive table.

Answer 3

For OneDayWhen:

Assuming the Product and Order_Detail tables:

create table PRODUCT
(
    PRODUCT_ID     NUMBER(38,0)             not null,
    PRODUCT_NAME   VARCHAR2(100)            not null,
    PRODUCT_DESCR  VARCHAR2(4000)               null,
    ACTIVE_FLAG    NUMBER(1,0)    DEFAULT 1 not null
);

create table ORDER_DETAIL
(
    ORDER_DETAIL_ID  NUMBER(38,0)           not null,
    ORDER_ID         NUMBER(38,0)           not null,
    PRODUCT_ID       NUMBER(38,0)           not null,
    LIST_PRICE       NUMBER(18,3)           not null,
    DISC_PRICE       NUMBER(18,3)           not null,
    QUANTITY         NUMBER(10,0)           not null
);

When one deactivates a product, there may still be outstanding orders which include that product. Archiving the orders doesn't solve the problem. Similarly, what if you want to add a product, but can't accept orders until some date when it's actively released? (Think Apple products.)

Having the application use a view (ACTIVE_PRODUCTS_VW) to show only active products for ordering makes this much easier.

create view ACTIVE_PRODUCTS_VW
as
select * from PRODUCT where ACTIVE_FLG = 1;

In Oracle, you can leverage a feature of indexing (where entirely NULL index keys are not indexed) to ensure that the indexes against the active products are small.

CREATE INDEX ACTIVE_PRODUCTS_IDX01 on PRODUCT
(  CASE ACTIVE_FLG WHEN 0 THEN NULL ELSE PRODUCT_ID END );