CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldatabase-design
Eric Eskildsen
Eric Eskildsen

Reputation: 4769

What is the simplest way to delete a child row when its parent is deleted, without knowing what its parent is?

Given multiple entity types:

  1. Cluster
  2. Hypervisor
  3. VirtualMachine

and given properties that could belong to any one of them (but no more than one per row):

What is the simplest way to delete a property with its parent?

Attempted Solutions

ON DELETE CASCADE

ON DELETE CASCADE seems to require a nullable foreign key for each possible parent, which strikes me as a poor design:

CREATE TABLE CpuInfo
(
    -- Properties
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CpuSpeed INT,
    AllocatedTotal INT,
    CpuTotal INT,
    AvailableTotal INT,

    -- Foreign keys for all possible parents
    ClusterId INT,
    HypervisorId INT,
    VirtualMachineId INT,

    FOREIGN KEY (ClusterId) REFERENCES Cluster(Id) ON DELETE CASCADE,
    FOREIGN KEY (HypervisorId) REFERENCES Hypervisor(Id) ON DELETE CASCADE,
    FOREIGN KEY (VirtualMachineId) REFERENCES VirtualMachine(Id) ON DELETE CASCADE
);

Junction Tables with Triggers

Parents are related to properties through junction tables. For example:

CREATE TABLE HypervisorCpuInfo
(
    HypervisorId INT NOT NULL,
    CpuInfoId INT NOT NULL,

    FOREIGN KEY (HypervisorId) REFERENCES Hypervisor(Id),
    FOREIGN KEY (CpuInfoId) REFERENCES CpuInfo(Id) ON DELETE CASCADE
);

There is then a DELETE trigger for each entity type. The trigger selects the IDs of the entity's properties and deletes them. When the properties are deleted, the child junction rows are then deleted also, via ON CASCADE DELETE.

This doesn't model the business rules very well, though, since it allows the same CpuInfo to belong to multiple entities. It also adds a lot of tables to the design.

Is there a simpler solution?

Upvotes: 3

Views: 529

Answers (1)

user__42
user__42

Reputation: 573

I think a "junction table" might be fitting for DRYness (it isn't a real junction because of the 1:n relation)

You could call your "junction table" a "super table" (something like "machine" [sorry I'm not native]):

In this table you put all the keys to your properties (make each foreign key column unique to ensure 1:1*). The very type of your "machine" (Cluster,Hypervisor,VirtualMachine) is in the "triple key" you already tried - also in the super-table. To ensure "machine" is only of one entity add a constraint:

ALTER TABLE CpuInfo WITH CHECK ADD CONSTRAINT [CK_keyIDs] CHECK (
(ClusterId IS NULL AND HypervisorId IS NULL AND VirtualMachineId IS NOT NULL)
OR (ClusterId IS NULL AND HypervisorId IS NOT NULL AND VirtualMachineId IS NULL)
OR (ClusterId IS NOT NULL AND HypervisorId IS NULL AND VirtualMachineId IS NULL)) GO

The good thing is you are quite free with your entities, you could allow a PC to be a Cluster at the same time.

*the key-column! the ID already has to be unique

Upvotes: 2

Related Questions