This is a tough one to explain so bear with me!I have three tables:-StandardReportDefinition------------------------StandardName varchar(10) PKSubPopulationName varchar(10)
ReportResults-------------ReportName varchar(10) PKStandardName varchar(10)SubPopulationName varchar(10)
SubPopulation-------------SubPopulationName varchar(10) PK
My application runs reports. The definition of a report can be stored in the StandardReportDefinition table. The results of a report are stored in the ReportResults table. Hence there is a one-to-many link between StandardReportDefinition and ReportResults with Nulls allowed in the Foreign Key in ReportResults (StandardName).Now each report may have a SubPopulation defined for it (this is a parameter used to generate the report). The possible SubPopulations are held in the SubPopulation table. The ReportResult table therefore has a Foreign Key - SubPopulation - which can be Null. Similarly the StandardReportDefinition also has a Foreign Key - SubPopulation - which can also be Null. The links between SubPopulation and both StandardReportDefinition and ReportResults are one-to-many.Now, finally, here is the problem....I want to set up cascading updates so that if the SubPopulation field (from the SubPopulation table) changes then so does the matching instances in the other two tables.With this structure SQL Server does not allow me to set up these cascade updates, returning the error:-Introducing FOREIGN KEY constraint 'FK__ReportRes__subpo__0A688BB1' on table 'ReportResults' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Can anyone explain why this happens?NB. I think I can get round the problem by using a trigger to do one of the updates, but am I violating some database design rule?The SQL for generating the structure is below...TIA============The Dabbler!Edited by - davidpardoe on 11/15/2001 05:18:47
Cascade Updates
Posted - 2001-11-15 : 05:16:20