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;