Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Error message when you try to upgrade Microsoft Dynamics CRM by using the upgrade wizard: "The foreign key constraints in the Microsoft CRM database are not consistent"

Error message when you try to upgrade Microsoft Dynamics CRM by using the upgrade wizard: "The foreign key constraints in the Microsoft CRM database are not consistent"

View products that this article applies to.

Symptoms

When you try to upgrade Microsoft CRM 1.2 to Microsoft Dynamics CRM 3.0 by using the upgrade wizard, you receive the following error message in the Upgrade Diagnostic Wizard window:
The foreign key constraints in the Microsoft CRM database are not consistent with Microsoft CRM 1.2 foreign key constraints.
When you try to upgrade Microsoft Dynamics CRM 3.0 to Microsoft Dynamics CRM 4.0 by using the upgrade wizard, you receive the following error message in the Upgrade Diagnostic Wizard window:
The foreign key constraints in the Microsoft CRM database are not consistent with Microsoft CRM 3.0 foreign key constraints.
Additionally, you see one or more of the following details if you view the installation log file. You see the following details if you deleted a foreign key:
09:41:33| Error| remark: deleted foreign key
09:41:33| Error| tablename: quotebase
09:41:33| Error| columnname: accountid
09:41:33| Error| referencedtablename: accountbase
09:41:33| Error| referencedcolumnname: accountid
09:41:33| Error| foreignkeyname: account_quotes
You see the following details if you added a constraint:

10:35:45|Warning| remark: added unique constraint on column
10:35:45|Warning| tablename:<TableName>
10:35:45|Warning| columnname:<ColumnName>
The <TableName> placeholder is a placeholder for the actual table name. The <ColumnName> placeholder is a placeholder for the actual column name.

Notes
  • The default location of the installation log file is the following location in which the System_drive placeholder represents the actual drive of the system:
    System_drive:\Documents and Settings\User\Application Data\Microsoft\MSCRM\Logs.
  • The TableName placeholder is a placeholder for the table name. The ColumnName placeholder is a placeholder for the actual column name.

↑ Back to the top

Cause

Upgrade Microsoft CRM 1.2 to Microsoft Dynamics CRM 3.0

This problem occurs because the foreign keys and the references to the foreign keys are missing from the Microsoft CRM 1.2 tables. Or, this problem occurs because constraints were manually added to the Microsoft CRM 1.2 tables.

Upgrade Microsoft Dynamics CRM 3.0 to Microsoft Dynamics CRM 4.0

This problem occurs because the foreign keys and the references to the foreign keys are missing from the Microsoft Dynamics CRM 3.0 tables. Or, this problem occurs because constraints were manually added to the Microsoft Dynamics CRM 3.0 tables.

↑ Back to the top

Resolution

Resolution 1: The foreign keys and the references to the foreign keys are missing

To resolve this problem, run the following script to add the foreign keys to the Microsoft Dynamics CRM tables.

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.
 
Alter Table <tablename> Add Constraint <foreignkeyname>
Foreign Key <referencedcolumnname> References <referencedtablename> <referencedcolumnname>
Notes
  • The <tablename> placeholder represents the actual table name.
  • The <foreignkeyname> placeholder represents the foreign key name.
  • The <referencedcolumnname> placeholder represents the name of the column that is referenced.
  • The <referencedtablename> placeholder represents the name of the table that is referenced.
To determine which foreign key is missing, view the setup log file.

Note The default location of the setup log file is the following location in which the System_drive placeholder represents the actual drive of the system:
System_drive:\Documents and Settings\User\Application Data\Microsoft\MSCRM\Logs.
For example, you view the following setup log:

09:41:33| Error| remark: deleted foreign key
09:41:33| Error| tablename: quotebase
09:41:33| Error| columnname: accountid
09:41:33| Error| referencedtablename: accountbase
09:41:33| Error| referencedcolumnname: accountid
09:41:33| Error| foreignkeyname: account_quotes
09:41:33| Error|
In this example, run the following script against the MSCRM database to add the foreign key to the quotebase table. 
alter table quotebase add constraint account_quotes foreign key (accountid) references accountbase (accountid)

Resolution 2: Constraints were manually added

To resolve this problem, delete the constraints that were manually added to the Microsoft Dynamics CRM tables. To do this, follow these steps:
  1. Start SQL Enterprise Manager.
  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand the instance of SQL Server that contains the Microsoft Dynamics CRM databases, and then expand Databases.
  3. Expand the OrganizationName_MSCRM database, and then click Table.
  4. In the list of tables on the right side of the window, right-click the table name that is mentioned in the log, and then click Design Table.
  5. Search the Column Name field for the column name that is mentioned in the log. Right-click the column name, and then click Check Constraints.
  6. Click Delete to delete the manually created constraint.

↑ Back to the top

Keywords: KB919641, kbprb, kbmbsmigrate, kberrmsg, kbmbsupgrade, kbtshoot, kbmbscrm40, kbarchive, kbnosurvey

↑ Back to the top

Article Info
Article ID : 919641
Revision : 4
Created on : 1/16/2015
Published on : 1/16/2015
Exists online : False
Views : 722