What is the simplest way to delete a child row when its parent is deleted without knowing what its parent is?
Given several types of entities:
-
Cluster
-
Hypervisor
-
VirtualMachine
and the specified properties that can belong to any of them (but not more than one per line):
-
CpuInfo
-
CpuSpeed
-
CpuTotal
- ...
-
-
DataStore
- ...
What's the easiest way to remove a property with its parent?
Attempts to solve
ON DELETE CASCADE
ON DELETE CASCADE
It seems that a null foreign key is required for every possible parent, which looks like bad 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
);
Trigger connection tables
Parents are associated with properties through connection 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
);
Then there is a trigger DELETE
for each entity type. The trigger fetches the property IDs of the object and removes them. When properties are removed, then the child connection lines are removed, via ON CASCADE DELETE
.
This does not model business rules very well as it allows the same thing CpuInfo
to belong to multiple objects. He also adds a lot of tables to the design.
Is there an easier solution?
source to share
I think a "jump table" might be appropriate for DRY (it is not a real join due to the 1: n relationship)
You can name your "junction table" "super-table" (something like "machine" [sorry I'm not native]):
In this table, you will put all keys in your properties (make each foreign key column unique to ensure 1: 1 *). The very type of "machine" ( Cluster
, Hypervisor
, VirtualMachine
) is in the "triple key", which you have already tried, and in the super-table. To make the "machine" only one object, 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
It is good that you are completely free with your essences, you can allow yourself to PC
be Cluster
at the same time.
* key column! the id must already be unique
source to share