Updating with pmbAustin's suggestion
pmbAustin, thanks. I think that will work. I've created a function "dbo.CK_WinteamSportExists" that returns a 0 or 1. However I'm now getting a baffling error message. When I tested my solution I get the following:
create table #check
(
SportID int
,WinTeamID int
,LoseTeamID int
,check
(
(dbo.CK_WinteamSportExists (WinTeamID , SportID) = 1)
)
)
Error Message: Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CK_WinteamSportExists", or the name is ambiguous.
I tried using a fully qualified name "mydatabase.dbo.CK_WinteamSportExists", no dice.
It appears that I've got the syntax wrong in the check, but for the life of me I can't find the error.
Original Question
I am trying to enforce referential integrity on a table variable. I'm working in SQL Server 2012.
declare @GameID_Table table (
GameID int
,SportID int
,WinTeamID int
,LoseTeamID int
)
Combinations of WinTeamID/SportID and LoseTeamID/SportID in @GameID_Table must be constrained by the existence of a matching row in a separate table Link_TeamSport defined as:
create table Link_TeamSport
(
TeamID int,
SportID int
primary key (TeamID, SportID)
)
Normally I'd do this with a composite foreign key, however these are not allowed in table variables.
I also tried creating user-defined functions that check for the existence of a matching row in Link_TeamSport, but UDFs are not allowed in table variables either.
Here is an example of working code that illustrates my constraint condition. The code below returns a result of 1 if a matching row is found and 0 if not. However, I cannot figure out how to incorporate this concept into the check constraint within @GameID_Table.
declare @winteam int
declare @sportid int
set @winteam = 1001
set @sportid = 4001
select count(*) from
(
select lts.TeamID, lts.sportid from Link_TeamSport lts
where
(@winteam = lts.TeamID and @sportid = lts.sportid)
) a
