SQLServerCentral / Editorials / SQLServerCentral.com / Voluntary Constraint? / Latest Posts

RE: Voluntary Constraint?

GSquared — Thu, 05 Jan 2012 06:26:49 GMT

[quote][b]Ken Lee-263418 (1/4/2012)[/b][hr]Thanks, a blindingly obvious solution, only you need to know about it. I didn't know you could filter the index because the last time I REALLY looked at an index, you couldn't and didn't encounter one using a where clause since then.Since their cleanup process was obviously flawed, did they implement the suggestion and did they run into problems with the process if they did? Since the data is not unique to begin with how did you handle creating the index? Create a new table with the index and copy into it, then replace the table. Determine the unique rows, set everything else to 0, when it fails to create, try again?Looked up something: The Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns.It can't be clustered on a filtered index can it?[/quote]Clean up of the existing data would have been easy enough. For each ID with multiple actives, keep the one with the latest data (there were easy ways to determine that based on audit trails), and set the rest to 0. Then add the filtered unique index to prevent future issues.Would cause whatever flawed code was creating the duplicates in the first place to start throwing errors. But that's a good thing long-term, because they had no faintest clue what code was causing the violations, so errors in production were the only way they'd ever find it.I don't know if they implemented my recommendations or not, since I left that place before anything was done on that.

RE: Voluntary Constraint?

Phil Factor — Thu, 05 Jan 2012 01:34:58 GMT

@GSquared That's a really good example of where constraints would have saved a catastrophe. The basic design wasn't at fault, I reckon, but the implementation sure was! Thinking of your description...[quote]they were totally certain it was working perfectly until I did a simple Select ... Where Active = 1 Group By pseudo-ID Having Count(*) > 1, [/quote].. I put into every database I write an 'impossible' stored procedure that runs on the schedule (daily) and at startup, double-checking all these rules across the database, and emailing me if anything fails a check. As well as making sure I sleep at night, the code is a very handy reminder of some of the more arcane domain rules in the database. Has it ever emailed me? er.. Yes. The impossible always happens in IT.@Ken There was a great article by Alex Kuznetsov here [url=http://www.simple-talk.com/sql/sql-training/yet-another-sql-strategy-for-versioned-data/ ]Yet another SQL Strategy for Versioned Data[/url] that explains this technique using filtered indexes that can be used for all sorts of temporal databases.

RE: Voluntary Constraint?

Ken Lee-263418 — Wed, 04 Jan 2012 23:40:07 GMT

Thanks, a blindingly obvious solution, only you need to know about it. I didn't know you could filter the index because the last time I REALLY looked at an index, you couldn't and didn't encounter one using a where clause since then.Since their cleanup process was obviously flawed, did they implement the suggestion and did they run into problems with the process if they did? Since the data is not unique to begin with how did you handle creating the index? Create a new table with the index and copy into it, then replace the table. Determine the unique rows, set everything else to 0, when it fails to create, try again?Looked up something: The Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns.It can't be clustered on a filtered index can it?

RE: Voluntary Constraint?

GSquared — Wed, 04 Jan 2012 14:02:23 GMT

[quote][b]sqlvogel (1/4/2012)[/b][hr][quote][b]GSquared (1/4/2012)[/b][hr]For each pseudo-ID, there was only ever supposed to be one row marked active, and all the rest were to have a 0 in that column. [/quote]How would you enforce that in SQL Server? I'm pretty sure it can't be done reliably with CHECK constraints. Indexed views and filtered unique indexes won't stop you updating all rows to be Active=0. Would you use triggers?[/quote]All rows set to 0 = row deleted, in this kind of schema. Nothing wrong with that. Sorry for the ambiguity in the statement. Should have been:[quote]For each pseudo-ID, there was only ever supposed to be one row marked active, and all the rest were to have a 0 in that column, or all rows marked 0 for "deleted" data.[/quote]That's kind of mentioned later in my post, but not clearly enough.Filtered unique index was the solution I recommended to them.

RE: Voluntary Constraint?

GPO — Wed, 04 Jan 2012 13:41:16 GMT

[quote]it is tempting to assume that data integrity should always be enforced by the database[/quote]Pretty sure that's not the argument. I wouldn't use the word "always". Merely "where possible". If column_A in table_B should only sensibly take values between 1 and 10, but within that should only be a 1 when certain combinations of data in other tables exist, should you throw out the constraint on column_A altogether because it's not sophisticated enough?[Changing tack]Of course, if I'm a really clever developer, I'll employ super-flexible "multi-purpose" tables where a lot of columns are set to varchar because they need to accommodate a variety of data types. Then I have a really strong argument to throw out constraints... don't I? I mean... it's the constraints that are the problem, not my design... surely.

RE: Voluntary Constraint?

sqlvogel — Wed, 04 Jan 2012 07:51:13 GMT

[quote][b]GSquared (1/4/2012)[/b][hr]For each pseudo-ID, there was only ever supposed to be one row marked active, and all the rest were to have a 0 in that column. [/quote]How would you enforce that in SQL Server? I'm pretty sure it can't be done reliably with CHECK constraints. Indexed views and filtered unique indexes won't stop you updating all rows to be Active=0. Would you use triggers?

RE: Voluntary Constraint?

GSquared — Wed, 04 Jan 2012 07:38:36 GMT

[quote][b]sqlvogel (1/4/2012)[/b][hr]From a SQL developer's perspective it is tempting to assume that data integrity should always be enforced by the database. Unfortunately SQL Server (like most SQL DBMSs) is only really effective for enforcing a few very simple data integrity rules - for example those that can be expressed as FOREIGN KEY constraints or "one row at a time" CHECK constraints. Other types of rules either aren't supported (e.g. mandatory cardinality "at least N" constraints) or require procedural code rather than being part of the data model (inclusion dependency and join dependency constraints).Relying on procedural Transact SQL to enforce complex rules or even simple rules has significant disadvantages. It's a basic tenet of business rules management that rules should be specified [i]declaratively[/i] - something that just isn't achievable in SQL. Additionally: T-SQL doesn't integrate easily with other components outside the database; it doesn't support code reuse to the extent of other languages like C# or Java; it is weakly-typed; it can't easily be scaled out across multiple servers; it lacks support by developer tools (e.g. for modelling, static analysis, testing and instrumentation).To simplify development, maintenance and support it is therefore quite common for business rules to be centralised in a domain layer or in rules engines and data integration / data quality tools outside the database. That way rules can easily be shared, maintained and inspected by applications, tools and the people who support them. So the DBMS isn't always the preferred place to implement data integrity - but it's certainly a better place than embedding integrity rules separately into every application.[/quote]The problem with that is that I've never yet seen it work. It seems to be in the category of "sounds great on paper, but it doesn't actually fly".My question on philosophies like that is always, "why bother with a fire extinguisher in the kitchen when you can call 911 and get a truck there to put the fire out?" In other words, have a data rules layer, on top of or integrated with the data access layer, but also enforce the rules at other levels, including the database.Yes, there are rules that the database can't readily enforce. Constraints across tables is one of the more obvious ones, where the only answer (triggers) is often worse than the problem it's meant to prevent. And there are rules you want to enforce WAY before the data ever gets sent to a table. You really don't want an SQL error if someone formats a date incorrectly in a form on a website. That should be handled much earlier in the process, even if only to avoid trips to and from the database for performance reasons.But there are also rules that really do need to be prevented in the database layer, even if "just in case", like a fire extinguisher. If you don't have a fire extinguisher in your kitchen, you've basically decided that your home burning down because of a grease-spill on the stove is an acceptable outcome. Not having data protection in the database is the same kind of thing.An eggregious example I ran into a couple of years ago was a company with a firm policy of never deleting any data or updating any data. They had a bit column in each table named "Active", which went along with a pseudo-ID. For each pseudo-ID, there was only ever supposed to be one row marked active, and all the rest were to have a 0 in that column. For example, customer 123 might have 20 or 30 rows in the Customers table, but only one of those rows would have a 1 in the Active column, the rest would have 0. Deletions would mark all the associated rows with a 0, and updates would insert a new row, with the new data, mark it active and mark the old row inactive. Sounds great on paper. They had all the rules for it built into a business rules layer, to make enforcement more efficient. After a year of it being in use, they were totally certain it was working perfectly until I did a simple Select ... Where Active = 1 Group By pseudo-ID Having Count(*) > 1, and found that some IDs had hundreds of "active" rows, a couple even had thousands, and the percentage of error was simply huge. Because of this, management for that company had been basing key decisions on seriously flawed data, and nobody could figure out why reality never matched the reports very well at all.Had the data been protected at every level, instead of one, it's highly probable that this scenario could have been prevented. It would have been easy to prevent, but they saw that as unnecessary because the business rule layer would take care of it. Kind of like the Titanic not having lifeboats, a double-hull, or sealed compartments, because the barrier walls would "take care of it".

RE: Voluntary Constraint?

sqlvogel — Wed, 04 Jan 2012 06:16:59 GMT

From a SQL developer's perspective it is tempting to assume that data integrity should always be enforced by the database. Unfortunately SQL Server (like most SQL DBMSs) is only really effective for enforcing a few very simple data integrity rules - for example those that can be expressed as FOREIGN KEY constraints or "one row at a time" CHECK constraints. Other types of rules either aren't supported (e.g. mandatory cardinality "at least N" constraints) or require procedural code rather than being part of the data model (inclusion dependency and join dependency constraints).Relying on procedural Transact SQL to enforce complex rules or even simple rules has significant disadvantages. It's a basic tenet of business rules management that rules should be specified [i]declaratively[/i] - something that just isn't achievable in SQL. Additionally: T-SQL doesn't integrate easily with other components outside the database; it doesn't support code reuse to the extent of other languages like C# or Java; it is weakly-typed; it can't easily be scaled out across multiple servers; it lacks support by developer tools (e.g. for modelling, static analysis, testing and instrumentation).To simplify development, maintenance and support it is therefore quite common for business rules to be centralised in a domain layer or in rules engines and data integration / data quality tools outside the database. That way rules can easily be shared, maintained and inspected by applications, tools and the people who support them. So the DBMS isn't always the preferred place to implement data integrity - but it's certainly a better place than embedding integrity rules separately into every application.

RE: Voluntary Constraint?

Ken Lee-263418 — Tue, 03 Jan 2012 21:59:15 GMT

Just noticed your by-line ...for "Row-By-Agonizing-Row"Don't you just "curse OR" rant when that happens?:-)

RE: Voluntary Constraint?

GSquared — Tue, 03 Jan 2012 12:44:31 GMT

I've never yet seen an application correctly validate [b][i]all[/i][/b] the data being fed in through it. Most? Sure. All? No way.Just ran into a situation where "the data format is controlled by the front end" was the policy. But someone using a browser with Java scripting turned off managed to bypass all the data validation and put junk data that violated almost every business rule in the place.

RE: Voluntary Constraint?

Evil Kraig F — Tue, 03 Jan 2012 12:30:19 GMT

And then there's me, who's a DB Developer and not a front-end guy, and I build the back end architecture.Why don't I usually use constraints except in "absolute, must have" scenarios?Because I'm a lazy git and I don't want to do a schema deployment when you change your mind in a week. Besides, this way the Constraint illustrates the absolute necessity of that data in that particular system, rather than being littered with them everytime you need to make a length change.

RE: Voluntary Constraint?

Cary Hower-563110 — Tue, 03 Jan 2012 05:11:06 GMT

If a developer argues that these constraints are not necessary because the code manages the integrity, it can be argued that after implementing these constraints in the database there will never be a violation of the database constraint. So what's the problem with having them in the database. Furthermore, can it be guaranteed that all access to the database will be through the application? Perhaps. It can be guaranteed that all access to the database will be through the database.On the topic of purchasing products with their proprietary databases, perhaps if enough data architects were involved in the purchase of these products and if enough of us said no to those which do not adhere to these constraints, the software vendors would have to build them in or not sell their products. Those are a couple of pretty hefty ifs, but worth pursuing.

RE: Voluntary Constraint?

Frank Hamersley — Tue, 03 Jan 2012 04:55:18 GMT

[quote][b]Ken Lee-263418 (1/1/2012)[/b][hr]... Maybe I'm just frustrated with some deadlock investigations where neither transaction was doing anything wrong, and if both had asked a few milliseconds apart, both would have succeeded, but instead one of them died in deadlock. Then tracing through the constraints, trying to figure out exactly how it happened and coming to the conclusion there isn't any way to stop it from happening.Then sometimes, you can solve the problem by setting up locking hints to cause locking in the right order. Just, why do you have to jump through all these hoops, to solve a problem produced by SQL logic?[/quote]I have been here too (with SQL Server as it happens) and the reason is not IMO (1) SQL logic or (2) embedded constraints. In my case it was perverse behaviour of the implementation - in particular the query optimiser recycling query plans in a case where 2 instances of the query with different sargs had run at the same time (previously) and produced inverse (of each other) lock escalation strategies ... then voila a deadlock when reused at a later date!What made it even more perverse was "in testing" (ie. without 20K sessions seen in production) the 2nd "bad" query plan never got created (and therefore not left in cache) so showplan just played out sweetness to the unskilled reader. Unfortunately the vendor was not much interested and there was not much room to move on a 2 table join.

RE: Voluntary Constraint?

colin.hodges — Mon, 02 Jan 2012 16:30:05 GMT

Agreed. Only recently have I felt strongly about designing in constraints. I now see an inverse relationship between the number of constraints versus post implementation support issues.

RE: Voluntary Constraint?

Jeff Moden — Mon, 02 Jan 2012 15:46:09 GMT

The article sounded a bit like a rant... ... I only wish I'd thought of it first! :-)Well said, Phil.

RE: Voluntary Constraint?

Phil Factor — Mon, 02 Jan 2012 02:33:10 GMT

@Ken LeePut your question to ASK.sqlServerCentral.com, or one of the forums, and see if one of the experts can help with this problem. I agree that, when faced with a frustration like this, one's instinct is to strip off as much complexity as possible to get to the root of the problem, but I've never known constraints to be the guilty party of a deadlock problem, but I'd rather one of the specialists dived in to help you. See Brad's [url=http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/]'How to track down Deadlocks using SQL Server POrofiler'[/url] Also check out Jonathan Kehayias and Ted Krueger's book [url=http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/] Troubleshooting SQL Server: A Guide for the Accidental DBA[/url]

RE: Voluntary Constraint?

Ken Lee-263418 — Sun, 01 Jan 2012 23:29:30 GMT

I certainly am not arguing with Phil, or the two posters. Maybe I'm just frustrated with some deadlock investigations where neither transaction was doing anything wrong, and if both had asked a few milliseconds apart, both would have succeeded, but instead one of them died in deadlock. Then tracing through the constraints, trying to figure out exactly how it happened and coming to the conclusion there isn't any way to stop it from happening.Then sometimes, you can solve the problem by setting up locking hints to cause locking in the right order. Just, why do you have to jump through all these hoops, to solve a problem produced by SQL logic?

RE: Voluntary Constraint?

Frank Hamersley — Sun, 01 Jan 2012 17:03:26 GMT

Also well said Old Hand.One of the conundrums I often observe in the hobbyist intellectual is that even after the first sign of dark clouds on the horizon they don't seem inclined to join the dots! I think in many cases the ego that created the success (often by force of effort) is not receptive to a retrospective analysis.I guess in many cases where success in the venture occurs, that a cohort of underlings has to be engaged to scale up the solution - and it is these unfortunates who have to wrestle with the monster that has been created. In many cases this backfill cohort is younger (cheaper), impressionable, dis empowered and not at all necessarily talented - all of which leads to a perpetuation of the original "dodgy" practices under the "ain't broke" mantra.In a personal experience where I have sneaked a peek under the hood of a second time success story (the first having been very successful as well) I was dismayed to see an appalling database implementation - ably masked by a glamorous user interface - but in reality simply a brute force effort built on foundations of pure sand.

RE: Voluntary Constraint?

GPO — Sat, 31 Dec 2011 19:01:20 GMT

Thank you, thank you, thank you, Phil.The question isn't so much about whether constraints are a good idea, it's more about understanding the psychology of developers who don't want to know that they're a good idea. It seems to me, that in the sphere of application development, it is too easy for bad database design to be implemented. But why?I suspect I'm not the only one who has to deal with software developed by (usually highly intelligent) people who are NOT developers by trade, but who: :arrow:are very good at what they do in their particular profession of choice:arrow:enjoy dabbling in software development as a kind of hobby:arrow:can see where some quick wins could be gained by a "simple" bit of software:arrow: because they are intelligent and good at what they're trained to do, develop an arrogance that there's nothing they could possibly learn from database designers. "All that guff will just slow us down and cost a fortune" ...which is true in a very narrow sense. What happens is that hobby software ends up in production, and is on the face of it, much better than what was there before (paper processes for example). Once it's in production though, removing it eventually becomes akin to cutting out a cancer. Things that worked OK for a few thousand rows eventually start to disintegrate, but only after hundreds or thousands of business dependencies have been built up around them. I deal with databases from around 20 different software vendors. The seven most problematic (i.e. over 30%) followed the above path on the way to becoming commercial software. The sheer audacity and arrogance of some hobby developers ("I know the business, therefore I know every aspect of the software requirements") is a large part of their commercial success. As sales people selling to people in their own professions, their cases can be compelling. I honestly don't see a cure.I was sitting in a meeting with a vendor once and we were attempting to discuss the manifold data integrity problems we were having because of the non-existence of constraints. The vendor simply closed the discussion down by saying "It's not the software's fault that the data entry staff are idiots." And that was that.

Voluntary Constraint?

Phil Factor — Sat, 31 Dec 2011 11:53:13 GMT

Comments posted to this topic are about the item [B]Voluntary Constraint?[/B]