Preferred design to avoid circular/multiple update paths

Question

Consider the following design:

CREATE TABLE dbo.Farmers
(
    FarmerName varchar(10) NOT NULL
        PRIMARY KEY 
);

CREATE TABLE dbo.FarmEquipment
(
    FarmEquipmentName varchar(10) NOT NULL
        PRIMARY KEY 
    , CreatorFarmer varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.Farmers(FarmerName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE dbo.Fields
(
    FieldName varchar(10) NOT NULL
    , OwnerFarmer varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.Farmers(FarmerName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
    , FarmEquipment varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.FarmEquipment(FarmEquipmentName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Use case:

• Farmer Joe owns land.
• Farmer Ted owns land.
• Farmer Joe builds a combine-harvester, and loans it to Farmer Ted for use on one of Farmer Ted's fields.
• If Farmer Joe changes his name to Farmer Joseph, I want that to reflect in the Fields table, so Farmer Ted knows who owns that combine harvester he's been using.

I realize this is the exact use-case for surrogate keys, but I'm trying to determine how this would work if you're using only natural keys.

In SQL Server, you cannot actually create this structure, since the ON UPDATE CASCADE and ON UPDATE DELETE clauses in dbo.Fields produce the following error message:

Msg 1785, Level 16, State 0, Line 21
Introducing FOREIGN KEY constraint 'FK__Fields__FarmEqui__3AD6B8E2' on table 'Fields' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Msg 1750, Level 16, State 0, Line 21
Could not create constraint. See previous errors.

How should this model be expressed in SQL Server?

I realize I can just remove the ON UPDATE clauses, but that's not really the point. I want updates and deletes to cascade.

Answer 1

The solution I've seen has been to remove the foreign key constraint, and to maintain relational integrity via triggers.

On the foreign (or "child") table, you need INSERT and UPDATE triggers. if the "foreign key" column(s) are inserted/updated, then you have to make sure the value(s) exists in the primary key of the "parent" table.

On the "parent" table, on UPDATE of the primary key column(s), you have to locate all rows in any "child" tables (FarmEquipment and Fields would both be "children" of Farmers) with the old value, and update them to the new value.

On the "parent" table, on DELETE, you have to check the "child" tables to see if any rows use the primary key(s) in question, and delete them all.

Having worked with a vendor-supplied system that worked this way, I can't recommend it.

• As noted above, you must maintain three chunks of code for each foreign key relationship, across a minimum of two triggers (let's leave self-referential situations out of the discussion, for the moment at least).
• A "parent" table that tied to multiple "child" tables would need a check for each "child" table. While the code should be very similar for each "child" table, that can actually make modifications more difficult, as it would be easy to make a change meant to affect FarmEquipment to Fields by mistake. (In the real-world example I mentioned, there was at least one parent table with at least 20 "FK" relationships - sometimes having multiple links in the same child table, to different fields.

• Documentation of the relationship is no longer built-in. While you can't (to the best of my knowledge) right-click a table and find all foreign key relationships that point to it, it's easy to find a query to retrieve this information from SQL Server's structural tables. When the relationship is maintained via triggers, you pretty much have to go through the triggers manually to identify all the relationships, and the precise definition (the "parent" table's DELETE trigger is where you can determine if the relationship is meant to be CASCADE, NO ACTION, SET NULL, or SET DEFAULT - with foreign key constraints, this can be seen from the "child" table).

  There are workarounds (the trigger code does exist in a queryable form), but they require that the developers follow strict rules regarding naming conventions, the construction of the query, and possibly even the formatting of the code. Even if one person is maintaining the code, and is using a template to set everything up, keeping everything exactly so as changes are made and bugs are found and fixed becomes much more difficult.

The system I worked with did work, with this method, for more than 10 years; they'd started their app in an environment that didn't have effective foreign keys. But even they had gotten to the point where they were using foreign keys for new tables, and starting to put them in place on existing tables where possible. It's just that working with it wasn't easy.

I suspect this wouldn't meet your criteria; however, as it is a viable solution, I thought it was at least worth covering.

Answer 2

The design I'm going to use consists of using triggers to control referential integrity, exactly as described by @RDFozz in his answer.

I'm adding this as an answer for reference in my other question on natural vs surrogate keys.

SET NOCOUNT OFF;

IF OBJECT_ID(N'dbo.Fields', N'U') IS NOT NULL
DROP TABLE dbo.Fields;

IF OBJECT_ID(N'dbo.FarmEquipment', N'U') IS NOT NULL
DROP TABLE dbo.FarmEquipment;

IF OBJECT_ID(N'dbo.Farmers', N'U') IS NOT NULL
DROP TABLE dbo.Farmers;
GO

CREATE TABLE dbo.Farmers
(
    FarmerName varchar(30) NOT NULL
        PRIMARY KEY 
    , RV rowversion
);

CREATE TABLE dbo.FarmEquipment
(
    FarmEquipmentName varchar(30) NOT NULL
        PRIMARY KEY 
    , CreatorFarmer varchar(30) NOT NULL
    , RV rowversion
);

CREATE TABLE dbo.Fields
(
    FieldName varchar(30) NOT NULL
        PRIMARY KEY
    , OwnerFarmer varchar(30) NOT NULL
    , FarmEquipment varchar(30) NOT NULL
    , RV rowversion
);
GO

CREATE NONCLUSTERED INDEX IX_FarmEquipment_CreatorFarmer
ON dbo.FarmEquipment(CreatorFarmer);

CREATE NONCLUSTERED INDEX IX_Fields_OwnerFarmer
ON dbo.Fields(OwnerFarmer);

CREATE NONCLUSTERED INDEX IX_Fields_FarmEquipment
ON dbo.Fields(FarmEquipment);
GO

CREATE TRIGGER Farmers_Update
ON dbo.Farmers
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE dbo.Farmers
    SET Farmers.FarmerName = i.FarmerName
    FROM inserted i
        INNER JOIN deleted d ON i.RV = d.RV
    WHERE Farmers.FarmerName = d.FarmerName;

    UPDATE dbo.FarmEquipment
    SET FarmEquipment.CreatorFarmer = i.FarmerName
    FROM inserted i
        INNER JOIN deleted d ON i.RV = d.RV
    WHERE FarmEquipment.CreatorFarmer = d.FarmerName;

    UPDATE dbo.Fields
    SET Fields.OwnerFarmer = i.FarmerName
    FROM inserted i
        INNER JOIN deleted d ON i.RV = d.RV
    WHERE Fields.OwnerFarmer = d.FarmerName;
END
GO

CREATE TRIGGER Farmers_Delete
ON dbo.Farmers
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @farmEquipmentCount int;
    DECLARE @fieldsCount int;
    SET @farmEquipmentCount = (
            SELECT COUNT(1) 
            FROM dbo.FarmEquipment fe
                INNER JOIN deleted d ON fe.CreatorFarmer = d.FarmerName
        );

    SET @fieldsCount = (
            SELECT COUNT(1) 
            FROM dbo.Fields f
                INNER JOIN deleted d ON f.OwnerFarmer = d.FarmerName
        )
    IF @farmEquipmentCount = 0 AND @fieldsCount = 0
    BEGIN
        DELETE dbo.Farmers
        FROM dbo.Farmers f
            INNER JOIN deleted d ON f.RV = d.RV;
    END
    ELSE
    BEGIN
        DECLARE @msg nvarchar(1000);
        SET @msg = N'Cannot remove rows from dbo.Farmers since referenced rows exist in dbo.FarmEquipment or dbo.Fields';
        RAISERROR (@msg, 10, 1);
        ROLLBACK TRANSACTION
    END
END
GO

CREATE TRIGGER FarmEquipmentInsert 
ON dbo.FarmEquipment
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @farmerCount int;
    SET @farmerCount = (
        SELECT COUNT(1)
        FROM dbo.Farmers 
            INNER JOIN inserted on Farmers.FarmerName = inserted.CreatorFarmer
        );
    IF @farmerCount > 0
    BEGIN
        INSERT INTO dbo.FarmEquipment (FarmEquipmentName, CreatorFarmer)
        SELECT inserted.FarmEquipmentName
            , inserted.CreatorFarmer
        FROM inserted;
    END
END
GO

CREATE TRIGGER FarmEquipmentUpdate
ON dbo.FarmEquipment
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.FarmEquipment
    SET FarmEquipment.FarmEquipmentName = i.FarmEquipmentName
    FROM inserted i
        INNER JOIN deleted d ON i.RV = d.RV
    WHERE dbo.FarmEquipment.RV = d.RV;

    UPDATE dbo.Fields
    SET Fields.FarmEquipment = i.FarmEquipmentName
    FROM inserted i
        INNER JOIN deleted d ON i.RV = d.RV
    WHERE Fields.FarmEquipment = d.FarmEquipmentName;
END
GO

CREATE TRIGGER FarmEquipmentDelete
ON dbo.FarmEquipment
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @FieldsCount int;
    SET @FieldsCount = (
        SELECT COUNT(1)
        FROM dbo.Fields 
            INNER JOIN deleted ON Fields.FarmEquipment = deleted.FarmEquipmentName
        );
    IF @FieldsCount = 0
    BEGIN
        DELETE FROM dbo.FarmEquipment
        FROM dbo.FarmEquipment
            INNER JOIN deleted ON FarmEquipment.RV = deleted.RV;
    END
    ELSE
    BEGIN
        DECLARE @msg nvarchar(1000);
        SET @msg = N'Cannot remove rows from dbo.FarmEquipment since referenced rows exist in dbo.Fields';
        RAISERROR (@msg, 10, 1);
        ROLLBACK TRANSACTION
    END
END
GO

CREATE TRIGGER FieldsInsertUpdate
ON dbo.Fields
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @IsUpdate bit;
    SET @IsUpdate = CASE WHEN (SELECT COUNT(1) FROM deleted) > 0 THEN 1 ELSE 0 END;
    DECLARE @FarmerCount int;
    DECLARE @EquipmentCount int;
    SET @FarmerCount = (
        SELECT COUNT(1)
        FROM dbo.Farmers
            INNER JOIN inserted ON Farmers.FarmerName = inserted.OwnerFarmer
        );
    SET @EquipmentCount = (
        SELECT COUNT(1)
        FROM dbo.FarmEquipment
            INNER JOIN inserted ON FarmEquipment.FarmEquipmentName = inserted.FarmEquipment
        );
    IF @FarmerCount > 0 AND @EquipmentCount > 0
    BEGIN
        IF @IsUpdate = 1
        BEGIN
            UPDATE dbo.Fields
            SET Fields.FarmEquipment = inserted.FarmEquipment
                , Fields.FieldName = inserted.FieldName
                , Fields.OwnerFarmer = inserted.OwnerFarmer
            FROM inserted
                INNER JOIN deleted ON inserted.RV = deleted.RV
            WHERE deleted.RV = Fields.RV;
        END
        ELSE
        BEGIN
            INSERT INTO dbo.Fields (FieldName, FarmEquipment, OwnerFarmer)
            SELECT inserted.FieldName
                , inserted.FarmEquipment
                , inserted.OwnerFarmer
            FROM inserted;
        END
    END
    ELSE
    BEGIN
        DECLARE @msg nvarchar(1000);
        IF @IsUpdate = 1
            SET @msg = N'Cannot update rows in dbo.Fields without referenced values present in dbo.Farmers and dbo.FarmEquipment.'
        ELSE
            SET @msg = N'Cannot insert rows into dbo.Fields without referenced values present in dbo.Farmers and dbo.FarmEquipment.';
        RAISERROR (@msg, 10, 1);
        ROLLBACK TRANSACTION;
    END
END
GO

INSERT INTO dbo.Farmers (FarmerName)
VALUES ('Joe')
    , ('Ted');

INSERT INTO dbo.FarmEquipment (CreatorFarmer, FarmEquipmentName)
VALUES ('Joe', 'Combine Harvester')
    , ('Joe', 'Tractor');

INSERT INTO dbo.Fields (OwnerFarmer, FieldName, FarmEquipment)
VALUES ('Ted', 'Field 1', 'Combine Harvester')
    , ('Joe', 'Field 2', 'Tractor');

UPDATE dbo.Farmers
SET Farmers.FarmerName = 'Joseph'
WHERE Farmers.FarmerName = 'Joe';

DELETE FROM dbo.Farmers
WHERE Farmers.FarmerName = 'Joseph';

UPDATE dbo.Fields
SET Fields.FarmEquipment = 'Blah'
WHERE Fields.FarmEquipment = 'Tractor';

UPDATE dbo.Fields
SET Fields.FieldName = 'Field 3'
WHERE Fields.FieldName = 'Field 2';

DELETE FROM dbo.FarmEquipment;
DELETE FROM dbo.Fields;
DELETE FROM dbo.Farmers;

SELECT *
FROM dbo.Farmers;

SELECT *
FROM dbo.FarmEquipment;

SELECT *
FROM dbo.Fields;