The person table
I have a relationship table
I am creating a foreign key relationship between relationship.person_a -> person.person_id with ON UPDATE CASCADE. This works fine. Now I try to create a foreign key relationship between relationship.person_a -> person.person_id with UPDATE CASCADE, but I get an error from SQL Server as follows:
This has been documented as a known issues in SQL Server: http://support.microsoft.com/kb/321843 as this can result in multiple cascade paths.
Since, person_id is auto-generated, it is not updated and hence the update cascade is not required. Thus, although we can just make this change for SQL Server, to keep all databases engines in sync, we should remove that ON UPDATE CASCADE, but still keep the foreign key relationship.