Complex Foreign Key Relationships

VistaDB can generate Foreign Key relationships that when exported to SQL Server do not work. You will see an error message like the one below:

Msg 1785, Level 16, State 0, Line 1 
Introducing FOREIGN KEY constraint 'FK_TicketSubProject_TicketProject' on table 'TicketSubProject' may cause cycles
or multiple cascade paths. 
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.    

SQL Server does not like having two columns that can cascade to another table during a single operation.

Many to Many Mapping

The most common situation I see for this error is a many to many mapping table. You typically build a table like Tickets, and then a mapping table for DuplicateTickets that contains a mapping from a DuplicateTicketID to the OriginalTicketID. Putting a FK on both of those columns will lead to this error in SQL Server.

VistaDB does not have this problem (we can handle multiple paths to the target table), but you will not know this is an error until you attempt to scale up your database to SQL Server and get the FK errors. We are working on a way to warn you about this when you build the relationships.

Remarks

See the Microsoft Support Article ID: 321843

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree