Error: Foreign key ****** reference invalid table

[Douglas Buchanan]

Here is a protion of the the DDL that created the table I wish to
referece:

CREATE TABLE [dbo].[tblPerson] (
[PersonID] [int] IDENTITY (1 ,1) NOT NULL CONSTRAINT PKPersonID
PRIMARY KEY,
...
...


Here is the line in my DDL that returns the error:

CREATE TABLE [dbo].[tblAvailability] (
...
...
[fk_Staff] [int] NOT NULL FOREIGN KEY REFERENCES tblPerson(PersonID)
ON DELETE NO ACTION ON UPDATE NO ACTION,
...
...

What's up?

[Simon Hayes]

Post by Douglas Buchanan
Here is a protion of the the DDL that created the table I wish to
CREATE TABLE [dbo].[tblPerson] (
[PersonID] [int] IDENTITY (1 ,1) NOT NULL CONSTRAINT PKPersonID
PRIMARY KEY,
...
...
CREATE TABLE [dbo].[tblAvailability] (
...
...
[fk_Staff] [int] NOT NULL FOREIGN KEY REFERENCES tblPerson(PersonID)
ON DELETE NO ACTION ON UPDATE NO ACTION,
...
...
What's up?

There's nothing obviously wrong, and reducing the DDL to the lines above
works correctly:

CREATE TABLE [dbo].[tblPerson] (
[PersonID] [int] IDENTITY (1 ,1) NOT NULL CONSTRAINT PKPersonID
PRIMARY KEY)
CREATE TABLE [dbo].[tblAvailability] (
[fk_Staff] [int] NOT NULL FOREIGN KEY REFERENCES tblPerson(PersonID)
ON DELETE NO ACTION ON UPDATE NO ACTION
)

So I guess the problem lies somewhere else - can you post the real DDL which
actually gives the error? The full error message text might also be useful.

Simon

[Douglas Buchanan]

Here is the DDL:

CREATE TABLE [dbo].[tblAvailability] (
[AvailabilityID] [int] IDENTITY (1 ,1) NOT NULL CONSTRAINT
PKAvailibilityID PRIMARY KEY,
[EditDate] [smalldatetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
[Editor] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT SYSTEM_USER,
[fk_Staff] [int] NOT NULL FOREIGN KEY REFERENCES tblPerson(PersonID) ON
DELETE NO ACTION ON UPDATE NO ACTION, -- Relationship is really with
vw3Staff(StaffID),
[StartEffectiveDate] [smalldatetime] NULL , -- When the schedule
becomes effective,
[EndEffectiveDate] [smalldatetime] NULL , -- When the schedule become
obsolete,
[fk_ScheduleType] [smallint] NULL FOREIGN KEY REFERENCES tblLookups(id)
ON DELETE NO ACTION ON UPDATE NO ACTION, -- Relationship is really with
vlkp22ScheduleType(ScheduleTypeID),
[Description] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, -- Description given to lend more detail about the schedule type such
as "Thanksgiving Day". Only available for Availabilty calendars,
[fk_DayOfWeek] [smallint] NULL FOREIGN KEY REFERENCES tblLookups(id) ON
DELETE NO ACTION ON UPDATE NO ACTION, -- Relationship is really with
vlkp08DayOfWeek(DayOfWeekID),
[Available] [bit] NOT NULL DEFAULT 0, -- By default entries are
availability not exceptions to availability,
[view_] [tinyint] NOT NULL -- Supply view name
) ON [PRIMARY]
GO

Here is the error message:

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

Here is the script that SQL Server writes from the existing table:

CREATE TABLE [dbo].[tblPerson] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[EditDate] [smalldatetime] NOT NULL ,
[Editor] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TitleOfCourtesy] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Minit] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[TitleOfDegree] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Nickname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOB] [smalldatetime] NULL ,
[ContactPreferences] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[yn] [bit] NULL ,
[view_] [tinyint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPerson] ADD
CONSTRAINT [DF__tblPerson__EditD__37703C52] DEFAULT (getdate()) FOR
[EditDate],
CONSTRAINT [DF__tblPerson__Edito__3864608B] DEFAULT (suser_sname()) FOR
[Editor],
CONSTRAINT [PKPersonID] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
GO



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

<snip>

It works fine for me (see DDL below), assuming that I create tblPerson
before tblAvailability - you can't create a foreign key to a table which
doesn't exist. Perhaps you have the CREATE statements in the wrong order?

Simon

CREATE TABLE [dbo].[tblPerson] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[EditDate] [smalldatetime] NOT NULL ,
[Editor] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TitleOfCourtesy] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Minit] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[TitleOfDegree] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Nickname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOB] [smalldatetime] NULL ,
[ContactPreferences] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[yn] [bit] NULL ,
[view_] [tinyint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPerson] ADD
CONSTRAINT [DF__tblPerson__EditD__37703C52] DEFAULT (getdate()) FOR
[EditDate],
CONSTRAINT [DF__tblPerson__Edito__3864608B] DEFAULT (suser_sname()) FOR
[Editor],
CONSTRAINT [PKPersonID] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
GO

/* I removed the columns which reference tblLookup */

CREATE TABLE [dbo].[tblAvailability] (
[AvailabilityID] [int] IDENTITY (1 ,1) NOT NULL CONSTRAINT
PKAvailibilityID PRIMARY KEY,
[EditDate] [smalldatetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
[Editor] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT SYSTEM_USER,
[fk_Staff] [int] NOT NULL FOREIGN KEY REFERENCES tblPerson(PersonID) ON
DELETE NO ACTION ON UPDATE NO ACTION, -- Relationship is really with
vw3Staff(StaffID),
[StartEffectiveDate] [smalldatetime] NULL , -- When the schedule becomes
effective,
[EndEffectiveDate] [smalldatetime] NULL , -- When the schedule become
obsolete,
[Description] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, --
Description given to lend more detail about the schedule type such as
"Thanksgiving Day". Only available for Availabilty calendars,
[Available] [bit] NOT NULL DEFAULT 0, -- By default entries are availability
not exceptions to availability,
[view_] [tinyint] NOT NULL -- Supply view name
) ON [PRIMARY]
GO

[Douglas Buchanan]

No, It already exists it the databse.......

No wait....

I just discovered the problem. I was trying to run the DDL against
Master! Sorry, how embarasing!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!