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

database-designreferential-integrity

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?

Best Answer

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

Related Solutions

Sql-server – How to select a subset of data from a large ER model

Unfortunately there isn't a cookie-cutter way to do this. You have to start with some base records, write queries that join against them and then repeat. As you go deeper the queries get more complex as they have to go right back to the root.

If you have the foreign keys then you can automate the generation of the queries, as the joins will just be through the foreign key columns. It's not as hard as it sounds, but here are a few pointers if you take this approach:

The queries are only going to retrieve the contents of the table at their leaf, so they are fairly straightforward to generate.
Get the data out in a form that will load through bcp. As a bonus, you can generate bcp control files from the system metadata.
You can probably quite safely just copy all of the reference data, which probably accounts for the majority of your tables anyway.
You may have to start from multiple roots, and you may have to manually add join metadata if you have missing foreign keys. If your relationships to your leaf tables are not purely hierarchical then you may have to manually edit the metadata for the generator. In practice this means you probably want to start from the system data dictionary and then make up a configuration file for your generator that works around this.
You may also need to add custom queries in for certain data items if the selection criteria involve more than one path to the record.
Generate or write a script that uses bcp to load the tables to the destination in the right order.

Automation will probably get you 95% of the way with some manual intervention and the scripts aren't all that hard to write. It's not a 5 minute job, but it's certainly possible.

You could programatically generate SSIS packages to do the load, but I think that generating bcp control files is probably easier. Another option would be to just structure the scripts so they copy the data from the queries into another shadow database. This can then be backed-up/restored to wherever you want.

It's not a trivial undertaking but it's certainly not beyond the wit of man. As a bonus, if you can configure your script so the starting keys can be parameterised, then you can make a generalised utility to copy subsets of your application database. This will be quite useful for rolling out test environments.

How to represent directed graph with multiple parents

Usually create a nodes table and a relationships table. Directed graphs are't really hierarchical and they can have loops which makes querying harder. But if you think of a DAG as being a generalised tree (I.e. a tree that allows multiple parents but is still strictly hierarchical) and a directed graph as being a generalised DAG (i.e. like a DAG but not strictly hierarchical) things get easier.

So for a very simple, PostgreSQL solution we might do something like:

CREATE TABLE node (
    id serial primary key,
    payload jsonb not null
);

CREATE TABLE relationship (
    id serial primary key,
    relationship_type text not null,
    from_node int references node(id) not null,
    to_node int references node(id) not null,
    payload jsonb not null
);

Then you can query something like this:

with recursive dg as (
    select n.id as node_id, null::Int as parent, array[n.id] as path
      from node n
    union all
    select to_node, from_node, path || to_node
      FROM relationship
      JOIN dg on dg.node_id = from_node AND NOT from_node = ANY(path)
)
select * from dg;

Best Answer

Related Solutions

Sql-server – How to select a subset of data from a large ER model

How to represent directed graph with multiple parents

Related Question