Ref. Integrity constraint on a PK and across diff schemas - problem

[LucieLastic]

hi

I have the same table in two different schemas, one of them is acting like a static table and the other is used for test loads of the data we send to the client. The tables have a composite PK but I only really need to check the asset_id col but if I have to use both cols in the PK in the one constraint, that is ok too.

[schemaOne].TableA

PK asset_id varchar(18)
PK leg_num int


[schemaTwo].TableB ('static' table)

PK asset_id varchar(18)
PK leg_num int


I've tried the following but neither works:

ALTER TABLE [schemaOne].[TableA]  WITH CHECK ADD  
 CONSTRAINT [FK_TableA] FOREIGN KEY([asset_id])
REFERENCES [schemaTwo].[TableB] ([asset_id])
GO

ALTER TABLE [schemaOne].[TableA]  WITH CHECK ADD  
 CONSTRAINT [FK_TableA] FOREIGN KEY([asset_id],[leg_num])
REFERENCES [schemaTwo].[TableB] ([asset_id],[leg_num])
GO

I'd be grateful for any help to point me in the right direction.

Many thanks
Lou

 

[gmmastros]

What error messages are you getting?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom