Using a User Defined Function as a constraint within a temporary table.

Your logic works OK in SQL Server 2014 using tempdb.

Here is the test.

USE tempdb;
GO
CREATE function dbo.CK_LoseTeamSportExists (@loseteam int, @sportid int)
 returns bit
 as
 begin
 declare @return bit
 if exists 
 (
 select ProductID, ReorderPoint from AdventureWorks.Production.Product
 where 
 (
 ProductID = @loseteam 
 and
 ReorderPoint = @sportid
 )
 )
 set @return = 1
 else set @return = 0
 return @return
 end
 go

create  table #check
 (
 SportID int
 ,WinTeamID int
 ,LoseTeamID int
 ,check 
 (
 (dbo.CK_LoseTeamSportExists(LoseTeamID,SportID) = 1)
 )
 )

 GO
 -- Command(s) completed successfully.

 -- SELECT ProductID, ReorderPoint FROM AdventureWorks.Production.Product WHERE ProductID = 800
 -- 800 75

 INSERT #check VALUES (75, 1900, 800);
 -- (1 row(s) affected)

 INSERT #check VALUES (76, 1900, 800);
 /*
 Msg 547, Level 16, State 0, Line 42
The INSERT statement conflicted with the CHECK constraint "CK__#check____________BA9C3A41". The conflict occurred in database "tempdb", table "dbo.#check______________________________________________________________________________________________________________00000000017A".
The statement has been terminated.
*/

UDF CHECK constraint:  http://ift.tt/1uWieEw 

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming

New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012