Create table with both keys as foreign (error)

Question

Post reply

Create table with both keys as foreign (error)

wiredvoltage

SSC Rookie

Points: 25
More actions
December 15, 2014 at 6:01 am

#296033

Hi I'm trying to create a table in Microsoft Server Management Studio 2012. The table has two fields which are both foreign keys.
I created the following:
create table tblRoomEquipment(
RoomID nvarchar(8),
EquipmentType nvarchar(1),
foreign key (RoomID) references tblRoom(ID),
foreign key (EquipmentType) references tblEquipment(Type)
)
Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!
Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.
DB's are not my thing, hopefully someone may have a simple solution for this?
Many thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 1

Quick suggestion

😎

CREATE TABLE tblRoomEquipment( /* Fully descriptive name for the constraints */

RoomID NVARCHAR(8) NOT NULL CONSTRAINT FK_DBO_TBLROOMEQUIPMENT_ROOMID_DBO_TBLROOM_ROOMID FOREIGN KEY REFERENCES dbo.tblRoom(ID)

,EquipmentType NVARCHAR(1) NOT NULL CONSTRAINT FK_DBO_TBLROOMEQUIPMENT_EQUIPMENTTYPE_DBO_TBLEQUIPMENT_TYPE FOREIGN KEY REFERENCES dbo.tblEquipment([Type])

);

wiredvoltage SSC Rookie Points: 25 More actions · Answer 2

Hi many thanks for the reply.

I copied your suggestion and intellisence still highlights tblRoom & tblEquipment as referencing invalid tables?

Regards

Grant Fritchey SSC Guru Points: 399364 More actions · Answer 3

I would add the schema to the tables in all the code you're referencing. Even if it's just the default and the default is 'dbo' you'll have more accurate scripts and code use. But, if the defaults are different, the fact that you're leaving the schemas off your code could be leading to the errors you're hitting.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 4

wiredvoltage (12/15/2014)
Hi many thanks for the reply.
I copied your suggestion and intellisence still highlights tblRoom & tblEquipment as referencing invalid tables?
Regards

Try pressing CTRL+SHIFT+R (in SSMS) to refresh the intellisense cache and to be certain check the schema where these two tables reside. You can generate a create script for the tables and post them here just in case.

😎

wiredvoltage SSC Rookie Points: 25 More actions · Answer 5

Many thanks for the replies - points taken 🙂

Ran the queries successfully with intellisence errors! maybe it's a bit flakey.

Checked object explorer object keys folder and both are there 🙂

Grant Fritchey SSC Guru Points: 399364 More actions · Answer 6

Yes, Intellisense within SSMS is extremely flaky.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning