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
Fieldstable, 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.
