Cascading Key Updates on ODS / API Resources

The Ed-Fi ODS SQL Server data store allows for the configuration of Cascading Updates on entities. Cascading Update specifies that if an update is made to a key value in a data row where the key value is referenced by foreign keys, then all existing foreign key values are updated to the new key value.¹

The following ODS / API resources are already configured for Cascading Updates in the as-shipped solution:

• Class Period
• Grade
• GradebookEntry
• Location
• Section
• Session
• Student School Association
• Student Section Association

However, some implementers may find it useful to enable Cascading Updates on additional resources including core resources and extension resources.

Configure Cascading Updates on an Ed-Fi Core resource

Cascading key updates are configured in MetaEd by 'allow primary key updates' language construct. However since the core model cannot be updated, we provide steps to modify generated artifacts to enable cascading key updates on core resources that are not preconfigured for cascading updates.  

1. Update the Api Model
   1. Locate the ApiModel.json file located in <source code root directory>Ed-Fi-ODS\Application\EdFi.Ods.Standard\Artifacts\Metadata
   2. Find the model you want to enable cascading updates on
   3. Within the identifiers array, find the primary key identifier (isPrimary flag will be true)
   4. Update the isUpdatable property to true
2. Run the code generation steps outlined in the Getting Started Guide (i.e., from a PowerShell prompt run Initialize-PowershellForDevelopment.ps script, followed by the initdev command)
3. Update Foreign Key Constraints for all dependencies
   1. SQL Server - Locate the 0030-ForeignKeys.sql file located in <source code root directory>\Ed-Fi-ODS\Application\EdFi.Ods.Standard\Artifacts\MsSql\Structure\Ods
   2. PostgreSQL- Locate the 0030-ForeignKeys.sql file located in <source code root directory>\Ed-Fi-ODS\Application\EdFi.Ods.Standard\Artifacts\PgSql\Structure\Ods 
   3. Identify all dependent resource foreign key constraints (Search for "REFERENCES [edfi].[<Resource_Name>]")
   4. Copy each of the the ALTER TABLE scripts to a separate text document
   5. Add a ALTER TABLE <TableName> DROP CONSTRAINT <ConstraintName> to the line before each of the copied ALTER TABLE Scripts
   6. Add ON UPDATE CASCADE¹ to each of the copied ALTER TABLE scripts
   7. Execute all of these SQL Scripts on the target ODS

ALTER TABLE [edfi].[AccountAccountCode] DROP CONSTRAINT [FK_AccountAccountCode_Account] 
ALTER TABLE [edfi].[AccountAccountCode] WITH CHECK ADD CONSTRAINT [FK_AccountAccountCode_Account] FOREIGN KEY ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
REFERENCES [edfi].[Account] ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [edfi].[Actual] DROP [FK_Actual_Account]
ALTER TABLE [edfi].[Actual] WITH CHECK ADD CONSTRAINT [FK_Actual_Account] FOREIGN KEY ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
REFERENCES [edfi].[Account] ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
ON UPDATE CASCADE

ALTER TABLE [edfi].[Budget] DROP CONSTRAINT [FK_Budget_Account]
ALTER TABLE [edfi].[Budget] WITH CHECK ADD CONSTRAINT [FK_Budget_Account] FOREIGN KEY ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
REFERENCES [edfi].[Account] ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
ON UPDATE CASCADE

ALTER TABLE [edfi].[ContractedStaff] DROP CONSTRAINT [FK_ContractedStaff_Account]
ALTER TABLE [edfi].[ContractedStaff] WITH CHECK ADD CONSTRAINT [FK_ContractedStaff_Account] FOREIGN KEY ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
REFERENCES [edfi].[Account] ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
ON UPDATE CASCADE

ALTER TABLE [edfi].[Payroll] DROP CONSTRAINT [FK_Payroll_Account]
ALTER TABLE [edfi].[Payroll] WITH CHECK ADD CONSTRAINT [FK_Payroll_Account] FOREIGN KEY ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
REFERENCES [edfi].[Account] ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])

ALTER TABLE [budgetextension].[BudgetAlternative] DROP CONSTRAINT [FK_BudgetAlternative_Account]
ALTER TABLE [budgetextension].[BudgetAlternative] WITH CHECK ADD CONSTRAINT [FK_BudgetAlternative_Account] FOREIGN KEY ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
REFERENCES [edfi].[Account] ([AccountIdentifier], [EducationOrganizationId], [FiscalYear])
ON UPDATE CASCADE

Configure Cascading Updates on an Ed-Fi Extension resource

For configuring cascading key updates on the extension resource, simply add the 'allow primary key updates' language construct to the .metaed file in your extension project. For more details on creating Ed-Fi Extensions, see here.