Skip to main content
added 55 characters in body
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663

I routinely denormalize so that I can enforce data integrity with constraints. One example is a recent question on this site - I replicate a column in another table, so that I can use a CHECKCHECK constraint to compare it to another column. Another example of this technique is my blog postmy blog post.

You cannot use CHECKCHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECKCHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? 

For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECKCHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is a way to enforce that periods of time have no gaps and no overlapsa way to enforce that periods of time have no gaps and no overlaps.

I routinely denormalize so that I can enforce data integrity with constraints. One example is a recent question on this site - I replicate a column in another table, so that I can use a CHECK constraint to compare it to another column. Another example of this technique is my blog post.

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is a way to enforce that periods of time have no gaps and no overlaps.

I routinely denormalize so that I can enforce data integrity with constraints. One example is a recent question on this site - I replicate a column in another table, so that I can use a CHECK constraint to compare it to another column. Another example of this technique is my blog post.

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? 

For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is a way to enforce that periods of time have no gaps and no overlaps.

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

I routinely denormalize so that I can enforce data integrity with constraints. One example is a recent question on this sitea recent question on this site - I replicate a column in another table, so that I can use a CHECK constraint to compare it to another column. Another example of this technique is my blog post.

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is a way to enforce that periods of time have no gaps and no overlaps.

I routinely denormalize so that I can enforce data integrity with constraints. One example is a recent question on this site - I replicate a column in another table, so that I can use a CHECK constraint to compare it to another column. Another example of this technique is my blog post.

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is a way to enforce that periods of time have no gaps and no overlaps.

I routinely denormalize so that I can enforce data integrity with constraints. One example is a recent question on this site - I replicate a column in another table, so that I can use a CHECK constraint to compare it to another column. Another example of this technique is my blog post.

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is a way to enforce that periods of time have no gaps and no overlaps.

Source Link
A-K
  • 7.4k
  • 3
  • 34
  • 51

I routinely denormalize so that I can enforce data integrity with constraints. One example is a recent question on this site - I replicate a column in another table, so that I can use a CHECK constraint to compare it to another column. Another example of this technique is my blog post.

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is a way to enforce that periods of time have no gaps and no overlaps.