constraints

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]

In my last post I showed a query to identify non-unique indexes that should be unique. You maybe have some other indexes that could be unique based on the data they contain, but are not. To find out, you just need to query each of those indexes and group by the whole key, filtering out those that have duplicate values. It may look like an overwhelming amount of work, but the good news is I have a script for that: [Read More]

Defining the appropriate primary key and unique constraints is fundamental for a good database design. One thing that I often see overlooked is that all the indexes with a key that includes completely another UNIQUE index’s key should in turn be created as UNIQUE. You could argue that such an index has probably been created by mistake, but it’s not always the case. If you want to check your database for indexes that can be safely made UNIQUE, you can use the following script: [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]

EDITED 2011-08-05: This post is NOT about the "correct" way to implement table-level check constraints. If that is what you're looking for, see this post instead. Today on SQL Server Central I stumbled upon an apparently simple question on CHECK constraints. The question can be found here. The OP wanted to know how to implement a CHECK constraint based on data from another table. In particular, he wanted to prohibit modifications to records in a detail table based on a datetime column on the master table. [Read More]