Business Rules

Constraints are sometimes annoying in real life, but no society can exist without rules and regulations. The same concept is found in Database Design: no good data can exist without constraints. What they say in Heaven Constraints define what is acceptable in the database and what does not comply with business rules. In Heaven, where the perfect database runs smoothly, no constraint is overlooked and all the data obeys to the rules of angels: Every column accepts only the data it was meant for, using the appropriate data type Every column that requires a value has a NOT NULL constraint Every column that references a key in a different table has a FOREIGN KEY constraint Every column that must comply with a business rule has a CHECK constraint Every column that must be populated with a predefined value has a DEFAULT constraint Every table has a PRIMARY KEY constraint Every group of columns that does not accept duplicate values has a UNIQUE constraint Chaos belongs to hell OK: Heaven is Heaven, but what about hell? [Read More]

Some days ago I blogged about a weird behaviour of “table-level” CHECK constraints. You can find that post here. Somehow, I did not buy the idea that a CHECK with a scalar UDF or a trigger were the only possible solutions. Scalar UDFs are dog-slow and also triggers are evil. I also read this interesting article by Alexander Kuznetsov (blog) and some ideas started to flow. Scalar UDFs are dog-slow because the function gets invoked RBAR (Row-By-Agonizing-Row, for those that don’t know this “Modenism”). [Read More]