table creation errors

[sepia]

Hi all. I'm trying to create a couple of tables which both are related by primary/foreign keys. Unfortunately, I'm getting the following errors:
Benefits_Test table errors - Foreign key 'FK__xbMISSN_B__Benef__5D5A1895' references invalid table 'xbMISSN_Benefits_History'.
Could not create constraint. See previous errors.
Cannot create an index on 'dbo.xbMISSN_Benefits_Test', because this table does not exist in database 'Aware'.

Benefits_History table errors - Foreign key 'FK__xbMISSN_B__Benef__5A7DABEA' references invalid column 'Benefits_ID' in referenced table 'xbMISSN_Benefits'.
Could not create constraint. See previous errors.
Cannot create an index on 'dbo.xbMISSN_Benefits_History', because this table does not exist in database.

Following is the scripts that creates the tables:

CREATE TABLE dbo.xbMISSN_Benefits_Test (
Benefits_ID int IDENTITY (1, 1) NOT NULL, --primary key)
BenefitsHistory_ID int,
SSN char (11) NOT NULL,
CAN_BIC char (12) NULL ,
surname char (19) NULL ,
middle_initial char (1) NULL ,
given_name char (12) NULL ,
date_of_birth smalldatetime NULL ,
sex char (1) NULL ,
st_agency_code char (3) NULL ,
cat_of_assist_code char (1) NULL ,
st_commun_code char (3) NULL ,
welfare_ID_num char (22) NULL ,
WTPY_response_date smalldatetime NULL ,
err_condition_code char (3) NULL ,
ident_discrep_code char (2) NULL ,
verification_code char (1) NULL ,
verification_SSN_data char (45) NULL ,
record_type char (1) NULL ,
title_2_status char (1) NULL ,
title_16_status char(1) NULL,
, PRIMARY KEY NONCLUSTERED ([Benefits_ID]) WITH FILLFACTOR = 95 ON [PRIMARY]
, FOREIGN KEY ([BenefitsHistory_ID]) REFERENCES [xbMISSN_Benefits_History] ([BenefitsHistory_ID]))
GO


CREATE TABLE dbo.xbMISSN_Benefits_History (
BenefitsHistory_ID int IDENTITY (1, 1) NOT NULL, --primary key)
Benefits_ID int,
SSN char (11),
LAF char(2) NULL,
payment_status_code char(3) NULL,
disability_onset_date smalldatetime NULL,
entitlement_date varchar(7) NULL,
eligibility_date varchar(7) NULL,
termination_date varchar(7) NULL,
suspension_date char(7) NULL,
TAC char(5) NULL,
HiDib smalldatetime NULL,
benefits_history_create_date smalldatetime NULL,
benefits_history_update_date smalldatetime NULL
, PRIMARY KEY NONCLUSTERED ([BenefitsHistory_ID]) WITH FILLFACTOR = 95 ON [PRIMARY]
, FOREIGN KEY ([Benefits_ID]) REFERENCES [xbMISSN_Benefits_Test] ([Benefits_ID]))
GO

CREATE CLUSTERED
INDEX [ixSSN] ON [dbo].[xbMISSN_Benefits_History] ([SSN])
go

Can anyone give me some assistance? Thanks.

 

[SQLDenis]

take out the constraints and add them later with alter table scripts
in the first table you are referencing the second table that is not created yet

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

How are you creating the tables? Are you doing it via Query Analyzer? If so, look in the dropdown box for databases, does that show AWARE or some other database?

BEST PRACTICE: Always use the USE command to explicitly set the database you are using.

USE Aware
CREATE.........

Or use the three part naming convention - database.owner.table

CREATE Aware.dbo.xbMISSN_Benefits_Test

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

Here is the script

CREATE TABLE dbo.xbMISSN_Benefits_Test (
Benefits_ID int IDENTITY (1, 1) NOT NULL, --primary key)
BenefitsHistory_ID int,
SSN char (11) NOT NULL,
CAN_BIC char (12) NULL ,
surname char (19) NULL ,
middle_initial char (1) NULL ,
given_name char (12) NULL ,
date_of_birth smalldatetime NULL ,
sex char (1) NULL ,
st_agency_code char (3) NULL ,
cat_of_assist_code char (1) NULL ,
st_commun_code char (3) NULL ,
welfare_ID_num char (22) NULL ,
WTPY_response_date smalldatetime NULL ,
err_condition_code char (3) NULL ,
ident_discrep_code char (2) NULL ,
verification_code char (1) NULL ,
verification_SSN_data char (45) NULL ,
record_type char (1) NULL ,
title_2_status char (1) NULL ,
title_16_status char(1) NULL,
PRIMARY KEY NONCLUSTERED ([Benefits_ID]) WITH FILLFACTOR = 95 ON [PRIMARY]
)
GO


CREATE TABLE dbo.xbMISSN_Benefits_History (
BenefitsHistory_ID int IDENTITY (1, 1) NOT NULL, --primary key)
Benefits_ID int,
SSN char (11),
LAF char(2) NULL,
payment_status_code char(3) NULL,
disability_onset_date smalldatetime NULL,
entitlement_date varchar(7) NULL,
eligibility_date varchar(7) NULL,
termination_date varchar(7) NULL,
suspension_date char(7) NULL,
TAC char(5) NULL,
HiDib smalldatetime NULL,
benefits_history_create_date smalldatetime NULL,
benefits_history_update_date smalldatetime NULL
, PRIMARY KEY NONCLUSTERED ([BenefitsHistory_ID]) WITH FILLFACTOR = 95 ON [PRIMARY]
, FOREIGN KEY ([Benefits_ID]) REFERENCES [xbMISSN_Benefits_Test] ([Benefits_ID]))
GO

CREATE CLUSTERED
INDEX [ixSSN] ON [dbo].[xbMISSN_Benefits_History] ([SSN])
go

alter table dbo.xbMISSN_Benefits_Test ADD FOREIGN KEY ([BenefitsHistory_ID]) REFERENCES [xbMISSN_Benefits_History] ([BenefitsHistory_ID])


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[sepia]

Thanks guys for helping out a novice.