Cycles and Multiple Cascade Paths
It is possible to create relationships in Access that cannot be converted into relationships in SQL Server, although this rarely happens. If it does happen, you get an error message similar to the following:
may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
The problem is that your relationship includes cascade update or cascade delete actions, which appear to SQL Server to be causing a feedback loop that can lead to problems. The solution is to remove one or more of the cascade actions. If you still need to implement the cascade operation, you can either write a trigger or create a stored procedure to replace it.
In Access, if you have a multi-part foreign key, if you start to enter values for one part of the key, you can only save the record once you have valid values in all parts of the key.
In SQL Server, when you have a multi-part foreign key, you can save the record even if not all parts of the key are complete (some fields in the key can contain NULL values). In fact, you can even have illegal values in parts of the key, because it is only when the key is complete that the values are checked.
This is not a common problem, but it's worthy of a note. The SQL Standards show a MATCH option that applies to defining relationships (or references); Access uses the FULL match option, and SQL Server uses the SIMPLE match option.
Mismatched Fields in Relationships
With Access, you can construct relationships between columns of different data types and sizes; for example, in a Customer table, you can have a foreign key field to a Country table that is Text(20), and in the Country table, the corresponding field could be a Text(30).
SQL Server does not allow this, and you need to modify your design to ensure that both fields are the same size and of the same data type. For the previous example, you can set the foreign key to a Text(30) field.
You could use code based on the following example to detect and possibly autocorrect these problems:
Sub modUpsizeTests_CheckFieldTypesAndSizesInRelationships() ' Check that the fields used in relationships ' are on the same type and size Dim db As DAO.Database Dim rel As DAO.Relation Dim fldInRel As DAO.Field Dim fld As DAO.Field Dim fld2 As DAO.Field Dim tdef As DAO.TableDef Dim tdef2 As DAO.TableDef Set db = CurrentDb For Each rel In db.Relations For Each fldInRel In rel.Fields Set tdef = db.TableDefs(rel.Table) Set tdef2 = db.TableDefs(rel.ForeignTable) Set fld = tdef.Fields(fldInRel.Name) Set fld2 = tdef2.Fields(fldInRel.ForeignName) If (fld.Type <> fld2.Type) Or (fld.Size <> fld2.Size) Then ' need to alter one of the fields Debug.Print rel.Name & " has a problem with the fields" Else Debug.Print rel.Name, tdef.Name, tdef2.Name End If Next Next End Sub
In this tutorial:
- Upsizing Access to SQL Server
- Planning for Upsizing
- Boolean Data
- IMAGE, VARBINARY(Max), and OLE Data
- Cycles and Multiple Cascade Paths
- Replicated Databases and Random Autonumbers
- Unique Index and Ignore Nulls
- Timestamps and Row Versioning
- Schemas and Synonyms
- Upsizing Wizard and the SQL Server Migration Assistant
- Upsizing to Use an Access Data Project
- Comparing Table Conversion in the Upsizing Wizard and SSMA
- Comparing Query Conversion in the Upsizing Wizard and SSMA
- Developing with Access and SQL Server
- Stored Procedures and Temporary Tables
- Handling Complex Queries
- Performance and Execution Plans
- SQL Server Profiler