Error Invalid Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error Invalid Table

Author

Topic

mhopler
Starting Member

1 Post

Posted - 2010-06-09 : 16:13:19

I am attempting to build my first table (I know the structure is horrible, but it is a short term project and it has to be this way, so please ignore that).

First question is that the way everyting is written, this must be almost impossible to read. Is there a standard way to identify which part is code and which is questions?

Second and main question:

I am getting the error message:

Msg 1767, Level 16, State 0, Line 1
Foreign key 'FK_PropType' references invalid table 'Lookups.PropertyTypes'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

I have created a Table called Lookups.PropertyTypes with one column PropertyType. Bleow is the code used for it.

Create Table Lookups.PropertyTypes
(PropertyType Varchar (100) Not Null Primary Key)

I then created another table with the following code (its long, also assume there are tables for Lookups.States and Lookups.ValueType):

Create Table Data.StoredData
(FacilityID Int Identity (1,1) Primary Key Not Null,
ApprDate DateTime Null,
ApprLTV int Null,
ORRDate DateTime Null,
CrossDefault Char(1) check (CrossDefault in('Y','N')) Not Null,
CollatCross Char (1) Check (CollatCross in ('Y','N')) Not Null,
FDICIAGuidCat VarChar(100) Null Constraint FK_PropType Foreign Key References Lookups.PropertyTypes (PropertyType),
DSCDate DateTime Null,

Pri_CollateralCode Char(25) Not Null,
Pri_CollatCity Varchar (50) Null,
Pri_CollatState Char (2) null Constraint FK_Pri_States Foreign Key References Lookups.States(States),
Pri_CollatZipCode Char (5) Check (Len(Pri_CollatZipCode) = 5),
Pri_CollatCountry Varchar (50) null,
Pri_CollatValue Int null,
Pri_CollatValueDate DateTime null,
Pri_CollatValueType Varchar (20) null Constraint FK_Pri_CollatValueType Foreign Key References Lookups.ValueType (Valuetype),
Pri_LienPosition TinyInt null,
Pri_CollatNOI Int null,
Pri_TenantConc Char (1) check (Pri_TenantConc in ('Y','N')) Null,

Sec_CollateralCode Char(25) Not Null,
Sec_CollatCity Varchar (50) Null,
Sec_CollatState Char (2) null Constraint FK_Sec_States Foreign Key References Lookups.States(States),
Sec_CollatZipCode Char (5) Check (Len(Sec_CollatZipCode) = 5),
Sec_CollatCountry Varchar (50) null,
Sec_CollatValue Int null,
Sec_CollatValueDate DateTime null,
Sec_CollatValueType Varchar (20) null Constraint FK_Sec_CollatValueType Foreign Key References Lookups.ValueType (Valuetype),
Sec_LienPosition TinyInt null,
Sec_CollatNOI Int null,
Sec_TenantConc Char (1) check (Sec_TenantConc in ('Y','N')) Null,
Sec_TenantAbundanceOfCaution char (1) check (Sec_TenantAbundanceOfCaution in ('Y','N')) Null,

Ter_CollateralCode Char(25) Not Null,
Ter_CollatCity Varchar (50) Null,
Ter_CollatState Char (2) null Constraint FK_Ter_States Foreign Key References Lookups.States(States),
Ter_CollatZipCode Char (5) Check (Len(Ter_CollatZipCode) = 5),
Ter_CollatCountry Varchar (50) null,
Ter_CollatValue Int null,
Ter_CollatValueDate DateTime null,
Ter_CollatValueType Varchar (20) null Constraint FK_Ter_CollatValueType Foreign Key References Lookups.ValueType (Valuetype),
Ter_LienPosition TinyInt null,
Ter_CollatNOI Int null,
Ter_TenantConc Char (1) check (Ter_TenantConc in ('Y','N')) Null,
Ter_TenantAbundanceOfCaution char (1) check (Ter_TenantAbundanceOfCaution in ('Y','N')) Null)

Considering there is a table Lookups.PropertyTypes, why am I getting the error message invalid table.

Mark

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-10 : 02:52:27

I just ran this code locally on my SQL 2008 Std and it worked without any errors whatsoever:

create schema data
go
create schema Lookups
go
Create Table Lookups.PropertyTypes (
  PropertyType Varchar (100) Not Null Primary Key
  )
go
create Table Data.StoredData (
  FacilityID Int Identity (1,1) Primary Key Not Null,
  FDICIAGuidCat VarChar(100) Null Constraint FK_PropType Foreign Key References Lookups.PropertyTypes (PropertyType),
  )
go

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-10 : 02:57:00

Looks Like Lookups.PropertyTypes is the Table Name

if you are using any special character request you to use the [] parentisis. as show below.
since your table contain a dot(.)
[Lookups.PropertyTypes]

Let me know if any issues.

Lets unLearn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources