Talk:Using constraints to restrict values in a column

From SQL Server Wiki
Revision as of 12:07, 4 July 2016 by TomThomson (talk | contribs) (reply to Phil's comment)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

The page appears to say that all use of check constraints to restrict column values should be avoided and foreign key constraints used instead. But this just doesn't work except in cases where the permitted values can reasonably be enumerated (which are certainly cases where foreign keys should be used). In cases where the restriction is a range restriction which doesn't push the number of distinct possible values down to a reasonably small set, the restriction is better expressed by using appropriate column type and check constraints, and in extreme cases (which are not uncommon) a value constraint using the foreign key method is impossible because it requires more storage that currently exists in the world. TomThomson (talk)

This item seems to have drifted from the original smell that I wrote about, possibly in order to keep the original booklet simple. This is where a check constraint is used to enforce an 'enumeration'. This does not apply to ranges such as date ranges, or to value ranges that can be expressed mathematically but 'permitted' values such as the names of departments, products and so on. This is actually disguised denormalisation, and is a code smell. It is much more difficult to maintain where these enumerations change rapidly since each change in a column constraint requires a DDL change and therefore requires a new version in most delivery regimes. I dislike it because it is a subterfuge. It buries an entity. In Tom's opening comment he uses the word 'all'. I'd like to suggest an edit to the definition of the code smell that removes the idea that it is referring to 'all' uses of check constraints, but I can't see where it suggests that.Phil Factor (talk)

You are right, Phil, it didn't say "all" it said "usually". But "usually" is just as wrong as "all", and your original text made no reference to enumerations (and although it was clear to me that enumerations were what you intended to cover, I suspect that a lot of people wouldn't realise that). In my experience (i) about as many value restraints are to ranges (whether value ranges or length ranges) as are to known enumerations and (ii) the average developer or DBA when told something is "usual" will blindly use that something instead of considering whether his current case is one of the "usual ones" - in effect he/she will interpret "usually" as "always"; and the average manager is even worse - if he reads that restricting column values other than by foreign key constraints is a code smell he may well write into a set of mandatory coding stndards that no value restrictions are to be implemented other than by foreign keys. In fact trying to use a foreign key when the appropriate thing is a check constraint is just as nasty a code smell as as trying to use a check constraint when the appropriate thing is a foreign key, so the real code smell is "Using the wrong sort of contraint to restrict values in a column" and maybe the title should be changed to reflect that. And the text of your original appears to be based on the crazy idea that a Foreign Key Constraint is not a constraint! TomThomson (talk) 12:07, 4 July 2016 (UTC)