March 12, 2015 at 10:29 am
I want to create a check constraint on a table but the constraint values depend upon another table column as well, now one possible way is to create a function to check the column value. But I don’t want to use the function
Can somebody please help me that how I can achieve this.
Can I do this in a view if so then how can I achieve this, any example will be much appreciated
and also if this is achievable by view then how the view will enforce these changes to table
Many Thanks
March 12, 2015 at 11:02 am
Why don't you want to use a function? It's really the only way to do what you want.
Gerald Britton, Pluralsight courses
March 12, 2015 at 1:05 pm
This is not possible in a view. A view can't enforce data constraints. It's nothing but a query against the table. You should look to CHECK CONSTRAINTS on the table.
"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
March 13, 2015 at 3:12 am
Be careful of using a function in a constraint. The constraint only gets checked when the table that it is on changes. If the table that the function reads from changes, the constrain won't be checked and you can end up with data that violates the check constraint and you'll only notice the next time the table that the constraint is on gets updated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2015 at 7:39 am
GilaMonster (3/13/2015)
Be careful of using a function in a constraint. The constraint only gets checked when the table that it is on changes. If the table that the function reads from changes, the constrain won't be checked and you can end up with data that violates the check constraint and you'll only notice the next time the table that the constraint is on gets updated.
True enough! but also the only way to write a check constraint that needs to look in another table.
Gerald Britton, Pluralsight courses
March 16, 2015 at 2:57 am
g.britton (3/13/2015)
GilaMonster (3/13/2015)
Be careful of using a function in a constraint. The constraint only gets checked when the table that it is on changes. If the table that the function reads from changes, the constrain won't be checked and you can end up with data that violates the check constraint and you'll only notice the next time the table that the constraint is on gets updated.True enough! but also the only way to write a check constraint that needs to look in another table.
Sure, but you may as well not bother as it's a constraint that you never have any idea whether it's true or not for any row and which can cause errors when someone updates an unrelated column in the table that has the constraint.
In a case like that, I'd probably put a trigger on the two tables involved to check from both sides that data doesn't violate the constraint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply