sql server (alternate): 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,
....
....

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 ***

[quoted text, click to view]

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

[quoted text, click to view]

<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

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 ***