Chicken and egg problem: FOREIGN KEY reference to a table that doesn't exist yet

[adammitchell]

I'm trying to create a local copy of a popular CRM database called
Salesforce.com. Many of the tables in the DB have FOREIGN KEY
references that I want to preserve, but I've run into a chicken and egg
problem. Table "A" has a reference to table "B," and table "B" has a
reference to table "A." So I can't CREATE one until the other exists.
Is there a way to disable these checks until I've created all the
schema?

Here's what I see (error first, then SQL that caused it):

Server: Msg 1767, Level 16, State 1, Line 1
Foreign key 'FK__UserRole__LastMo__48CFD27E' references invalid table
'User'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

CREATE TABLE salesforce3.dbo."UserRole" ("Id" varchar(18) PRIMARY KEY ,
"Name" varchar(40), "ParentRoleId" varchar(18) REFERENCES
"UserRole"(Id), "RollupDescription" varchar(80),
"OpportunityAccessForAccountOwner" varchar(40),
"CaseAccessForAccountOwner" varchar(40), "LastModifiedDate" datetime,
"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"
datetime);

CREATE TABLE salesforce3.dbo."User" ("Id" varchar(18) PRIMARY KEY ,
"Username" varchar(80), "LastName" varchar(80), "FirstName"
varchar(40), "CompanyName" varchar(80), "Division" varchar(80),
"Department" varchar(80), "Title" varchar(80), "Street" text, "City"
varchar(40), "State" varchar(20), "PostalCode" varchar(20), "Country"
varchar(40), "Email" varchar(80), "Phone" varchar(40), "Fax"
varchar(40), "MobilePhone" varchar(40), "Alias" varchar(8), "IsActive"
bit, "TimeZoneSidKey" varchar(40), "UserRoleId" varchar(18) REFERENCES
"UserRole"(Id), "LocaleSidKey" varchar(40), "ReceivesInfoEmails" bit,
"ReceivesAdminInfoEmails" bit, "EmailEncodingKey" varchar(40),
"ProfileId" varchar(18) REFERENCES "Profile"(Id), "LanguageLocaleKey"
varchar(40), "EmployeeNumber" varchar(20), "WirelessEmail" varchar(80),
"LastLoginDate" datetime, "CreatedDate" datetime, "CreatedById"
varchar(18) REFERENCES "User"(Id), "LastModifiedDate" datetime,
"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"
datetime, "UserPermissionsMarketingUser" bit,
"UserPermissionsOfflineUser" bit, "UserPermissionsWirelessUser" bit,
"UserPermissionsSuperCssUser" bit, "UserPermissionsAvantgoUser" bit);

[Simon Hayes]

<ad**********@marketsquaresolutions.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

I'm trying to create a local copy of a popular CRM database called
Salesforce.com. Many of the tables in the DB have FOREIGN KEY
references that I want to preserve, but I've run into a chicken and egg
problem. Table "A" has a reference to table "B," and table "B" has a
reference to table "A." So I can't CREATE one until the other exists.
Is there a way to disable these checks until I've created all the
schema?

Here's what I see (error first, then SQL that caused it):

Server: Msg 1767, Level 16, State 1, Line 1
Foreign key 'FK__UserRole__LastMo__48CFD27E' references invalid table
'User'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

CREATE TABLE salesforce3.dbo."UserRole" ("Id" varchar(18) PRIMARY KEY ,
"Name" varchar(40), "ParentRoleId" varchar(18) REFERENCES
"UserRole"(Id), "RollupDescription" varchar(80),
"OpportunityAccessForAccountOwner" varchar(40),
"CaseAccessForAccountOwner" varchar(40), "LastModifiedDate" datetime,
"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"
datetime);

CREATE TABLE salesforce3.dbo."User" ("Id" varchar(18) PRIMARY KEY ,
"Username" varchar(80), "LastName" varchar(80), "FirstName"
varchar(40), "CompanyName" varchar(80), "Division" varchar(80),
"Department" varchar(80), "Title" varchar(80), "Street" text, "City"
varchar(40), "State" varchar(20), "PostalCode" varchar(20), "Country"
varchar(40), "Email" varchar(80), "Phone" varchar(40), "Fax"
varchar(40), "MobilePhone" varchar(40), "Alias" varchar(8), "IsActive"
bit, "TimeZoneSidKey" varchar(40), "UserRoleId" varchar(18) REFERENCES
"UserRole"(Id), "LocaleSidKey" varchar(40), "ReceivesInfoEmails" bit,
"ReceivesAdminInfoEmails" bit, "EmailEncodingKey" varchar(40),
"ProfileId" varchar(18) REFERENCES "Profile"(Id), "LanguageLocaleKey"
varchar(40), "EmployeeNumber" varchar(20), "WirelessEmail" varchar(80),
"LastLoginDate" datetime, "CreatedDate" datetime, "CreatedById"
varchar(18) REFERENCES "User"(Id), "LastModifiedDate" datetime,
"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"
datetime, "UserPermissionsMarketingUser" bit,
"UserPermissionsOfflineUser" bit, "UserPermissionsWirelessUser" bit,
"UserPermissionsSuperCssUser" bit, "UserPermissionsAvantgoUser" bit);

If you have access to a source database, then Enterprise Manager can script
tables and constraints separately, so you could generate one script to
create the tables, then a second to add the constraints with ALTER TABLE.

Alternatively, if all you have to go on is your script, you can use CREATE
SCHEMA (see Books Online):

create schema authorization dbo
create table UserRole (...)
create table [User] (...)

By the way, "User" is a reserved word in MSSQL, so it isn't a good choice
for a table name, but I assume that if it's a third-party product, you can't
do much about that anyway.

Simon

[Erland Sommarskog]

(ad**********@marketsquaresolutions.com) writes:

I'm trying to create a local copy of a popular CRM database called
Salesforce.com. Many of the tables in the DB have FOREIGN KEY
references that I want to preserve, but I've run into a chicken and egg
problem. Table "A" has a reference to table "B," and table "B" has a
reference to table "A." So I can't CREATE one until the other exists.
Is there a way to disable these checks until I've created all the
schema?

Just do:

CREATE TABLE this_one
CREATE TABLE that_one
...
ALTER TABLE this_one ADD CONSTRAINT fk_this_that FOREIGN KEY ...
ALTER TABLE that_one ADD CONSTRAINT fk_that_this FOREIGN KEY ...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp