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:
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 |
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:
allowPrimaryKeyUpdates="true"
flag to the desired aggregate.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