Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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
share|improve this answer
    
Whereas this approach of "soft-deleting" is common, it is definitely not desirable. It leaves a lot of deprecated data in the production database - slowing performance. What it will also do is make it very hard for the graph of objects to figure out if the chain is relevant as it will have to gather the entire graph and then check if each object is relevant, and if each objects dependencies are also relevant. –  Travis J Mar 27 '12 at 20:24
    
@TravisJ Depending on your business requirements, that may or may not be a performance impact consideration. There's no cut and dry always-right answer to that. If there's frequent deleting then yes, that could be a problem. –  Thomas Stringer Mar 27 '12 at 20:36
    
Also, refrain from using a BIT datatype when the question is generic (no RDBMS specified) - Oracle doesn't support 'BIT', for example. :) –  FreshPhilOfSO Mar 28 '12 at 8:41
add comment

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.
share|improve this answer
    
I like your suggestions. I am thinking about going the route similar to a combination of 2 to 3. I am going to have a junction table for deleted orders which holds the primary key of the deletion, and a foreign key of the order in the archived table. This way orders can be deleted, stored, found, or restored all while keeping the production database sleek. –  Travis J Mar 28 '12 at 19:30
    
It's very common for such systems to have "soft" deletes - a product is deactivated so it can't be selected for new transactions, but would still exist for historical transactions. –  Adam Musch Mar 30 '12 at 19:07
    
@AdamMusch: yes and I recently escaped from such a system! An IsDeleted column in the most commonly used top-level table. 99% of queries against this table had to include ...AND IsDeleted = 0 in its search condition and, users being users, it was sometimes omitted leading to frequent/subtle bugs etc. Had one serious attempt to refactor but was beaten back by effort and risk (no automated testing). Hence my stance: if you want to retain rows that would otherwise be removed them move them to an archive table. –  onedaywhen Apr 11 '12 at 13:05
    
@onedaywhen: That's where views come in; users who need guard rails GET only the guard-railed version. –  Adam Musch Apr 11 '12 at 16:56
    
@AdamMusch: I think I know what you mean but post some more details in the form of an answer and let's see how it stands up :) –  onedaywhen Apr 12 '12 at 7:17
show 1 more comment

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 );   
share|improve this answer
    
In addition to using a view, it would be very pertinent to partition the table on the ACTIVE_FLG to reduce the size of queries. Using the view in the manner suggested would still require a decent amount of overhead. –  Travis J Apr 12 '12 at 18:54
    
Partitioning the table requires both Enterprise Edition and the Partition option; both are pricey. Neither is required to implement such a solution. Further, as row movement between partitions involves an INSERT and a DELETE under the covers, one needs to test the bejeebers out of such an implementation and be aware of the consequences. –  Adam Musch Apr 12 '12 at 19:30
    
In my experience, users will see the PRODUCT case table and favour using that in all cases over the ACTIVE_PRODUCTS_IDX01 viewed table. Maybe I just don't get savvy enough users? ;) –  onedaywhen Apr 13 '12 at 7:47
    
Well, the REVOKE statement is there for a reason, after all. –  Adam Musch Apr 13 '12 at 13:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.