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__48CFD27 E' 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), "ParentRole Id" varchar(18) REFERENCES
"UserRole"( Id), "RollupDescript ion" varchar(80),
"OpportunityAcc essForAccountOw ner" varchar(40),
"CaseAccessForA ccountOwner" varchar(40), "LastModifiedDa te" datetime,
"LastModifiedBy Id" 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), "CompanyNam e" 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), "MobilePhon e" varchar(40), "Alias" varchar(8), "IsActive"
bit, "TimeZoneSidKey " varchar(40), "UserRoleId " varchar(18) REFERENCES
"UserRole"( Id), "LocaleSidK ey" varchar(40), "ReceivesInfoEm ails" bit,
"ReceivesAdminI nfoEmails" bit, "EmailEncodingK ey" varchar(40),
"ProfileId" varchar(18) REFERENCES "Profile"(I d), "LanguageLocale Key"
varchar(40), "EmployeeNumber " varchar(20), "WirelessEm ail" varchar(80),
"LastLoginD ate" datetime, "CreatedDat e" datetime, "CreatedByI d"
varchar(18) REFERENCES "User"(Id), "LastModifiedDa te" datetime,
"LastModifiedBy Id" varchar(18) REFERENCES "User"(Id), "SystemModstamp "
datetime, "UserPermission sMarketingUser" bit,
"UserPermission sOfflineUser" bit, "UserPermission sWirelessUser" bit,
"UserPermission sSuperCssUser" bit, "UserPermission sAvantgoUser" bit);

[Simon Hayes]

<ad**********@m arketsquaresolu tions.com> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.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__48CFD27 E' 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), "ParentRole Id" varchar(18) REFERENCES
"UserRole"( Id), "RollupDescript ion" varchar(80),
"OpportunityAcc essForAccountOw ner" varchar(40),
"CaseAccessForA ccountOwner" varchar(40), "LastModifiedDa te" datetime,
"LastModifiedBy Id" 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), "CompanyNam e" 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), "MobilePhon e" varchar(40), "Alias" varchar(8), "IsActive"
bit, "TimeZoneSidKey " varchar(40), "UserRoleId " varchar(18) REFERENCES
"UserRole"( Id), "LocaleSidK ey" varchar(40), "ReceivesInfoEm ails" bit,
"ReceivesAdminI nfoEmails" bit, "EmailEncodingK ey" varchar(40),
"ProfileId" varchar(18) REFERENCES "Profile"(I d), "LanguageLocale Key"
varchar(40), "EmployeeNumber " varchar(20), "WirelessEm ail" varchar(80),
"LastLoginD ate" datetime, "CreatedDat e" datetime, "CreatedByI d"
varchar(18) REFERENCES "User"(Id), "LastModifiedDa te" datetime,
"LastModifiedBy Id" varchar(18) REFERENCES "User"(Id), "SystemModstamp "
datetime, "UserPermission sMarketingUser" bit,
"UserPermission sOfflineUser" bit, "UserPermission sWirelessUser" bit,
"UserPermission sSuperCssUser" bit, "UserPermission sAvantgoUser" 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**********@m arketsquaresolu tions.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****@sommarsk og.se

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