This version of the Ed-Fi ODS / API is no longer supported. See the Ed-Fi Technology Version Index for a link to the latest version.
How To: Enable Cascading Updates on ODS / API Resources
- Ian Christopher
- Chris Moffatt (Deactivated)
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.1
The following ODS / API resources are already configured for Cascading Updates in the as-shipped solution:
- Class Period
- Grade
- Location
- Section
- Session
- Staff Section Association
- Student School Association
- Student Section Association
- Term Type
As an illustrative example, the Grade table has an association to Student Section that is updated whenever the Student Section Association changes. The Grade metadata and table declarations have the following metadata and SQL:
<Aggregate root="Grade" allowPrimaryKeyUpdates="true"> <Entity table="Grade" /> </Aggregate>
ALTER TABLE [edfi].[Grade] ADD CONSTRAINT [FK_Grade_StudentSectionAssociation] FOREIGN KEY ([StudentUSI], [SchoolId], [ClassPeriodName], [ClassroomIdentificationCode], [LocalCourseCode], [UniqueSectionCode], [SequenceOfCourse], [SchoolYear], [TermDescriptorId], [BeginDate]) REFERENCES [edfi].[StudentSectionAssociation]([StudentUSI], [SchoolId], [ClassPeriodName], [ClassroomIdentificationCode], [LocalCourseCode], [UniqueSectionCode], [SequenceOfCourse], [SchoolYear], [TermDescriptorId], [BeginDate]) ON UPDATE CASCADE
Configuration Steps
Some implementers will find it useful to enable Cascading Updates on additional resources. To configure Cascading Updates on a resource, the following steps are required:
- Step 1. Modify the DomainMetadata.xml file adding the
allowPrimaryKeyUpdates="true"
flag to the desired aggregate. - Step 2. Modify the 0004-Tables.SQL file that enables the
ON UPDATE CASCADE
syntax on the foreign keys that reference the base table’s primary key.
For an example of how this is done, see the code snippets related to the Grade resource, above.
1 For more details on Table Cascading Updates, see https://technet.microsoft.com/en-us/library/ms188066(v=sql.110).aspx