CODEWITHSUNDEEP
sql-servert-sql
Creator
Creator

Reputation: 135

Check Constraint with function on temporary table

I am trying to set a check constraint with a function on a temp table and get the following error:

Msg 4121, Level 16, State 1, Line 10
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CheckCustomers", or the name is ambiguous.

... but select dbo.CheckCustomers() works.

Is it generally possible to set such a constraint on temp table?

Upvotes: 2

Views: 1179

Answers (1)

Jason Whitish
Jason Whitish

Reputation: 1438

Keep in mind that temporary tables are actually created in the system database tempDB, not in your actual database. So in order for you to be able to use a function as a check constraint in your temporary table, that function would have to exist in tempDB.

One important thing, however, is that tempDB gets recreated every time the server is restarted. So if you do want to take this approach, you would need to design around that. (Reference for this is Inside Microsoft SQL Server 2008 T-SQL Programming)

Upvotes: 3

Related Questions