Weblogs Forum - Database Constraints: Use Them or Lose Them?

Articles |
News |
Weblogs |
Books |
Forums

Artima Forums | Articles | Weblogs | Java Answers | News

Sponsored Link •

Weblogs Forum
Database Constraints: Use Them or Lose Them?

56 replies on 4 pages. Most recent reply: Oct 8, 2009 9:07 AM by John Zabroski

Welcome Guest
Sign In

Back to Topic List

Reply to this Topic

Search Forum

Threaded View


Previous Topic		Next Topic

Flat View: This topic has 56 replies on 4 pages [ « | 1 2 3 4 ]

James Watson

Posts: 2024
Nickname: watson
Registered: Sep, 2005

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 2, 2009 10:52 AM

Reply

Advertisement

> Under what circumstances? For a BCNF schema, all data is
> simply a join away. With the SSD multi-machines of today,
> not an issue. Declarative constraints can do anything
> else I've ever needed. Remember: all data in the
> database is determined to be correct, either by the real
> world (a fact which is a "master file" fact; and not
> testable either in code or the data), or by other data.
> Declarative referential integrity.
>
> But you're begging the question. If you have a simplistic
> flat-file image in the datastore, then constraints become
> impossible without gobs of code. But you want gobs of
> code, because you write code. I can't change that, except
> to fire you. If your management can be convinced to
> continue with that mode of development, then you're safe,
> I guess. But that light at the end of the tunnel is the
> Orient Express.

I once built a rule set that was something like this (but more complex):

Given a set of groups of people, two groups must be merged if both groups do not have a cook. The final set should be the largest number of groups under the size of X persons. The user may modify the resulting groups as desired.

Can you tell me how to set up database constraints to implement that?

John Zabroski

Posts: 272
Nickname: zbo
Registered: Jan, 2007

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 2, 2009 11:08 AM

Reply

> The user may modify the resulting groups as desired.

> Can you tell me how to set up database constraints to
> implement that?

It sounds like some sort of dynamic load balancing constraint?

John Zabroski

Posts: 272
Nickname: zbo
Registered: Jan, 2007

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 2, 2009 11:10 AM

Reply

> In the context of this discussion, that's implementing the
> constraints in the application layer, not the database.

Design twist: Not if you're layering mobile code.

John Zabroski

Posts: 272
Nickname: zbo
Registered: Jan, 2007

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 2, 2009 11:17 AM

Reply

> > That said, it would be nice if the database would let
> > let me map errors resulting from check constraints to
> > specific error messages.
>
> For databases which correctly implement information_schema
> (and if not, a layer for each database supported by the
> application; I've never worked on one that did so for more
> than 2), error messages for errors (with runtime
> parameters filled in with a function) assigned to a table.
> Said table is joined during querying, and said table is
> s assigned to its own buffers, of sufficient size as to
> remain resident.
>
> It's a bit of work to set up the first time, but
> maintenance thenceforth is simple. You never have to
> write message handling in any application code. Just call
> the function as part of queries.

I am not sure I follow your advice.

Are you saying you are depending directly on the SQL vendor correctly implementing the SQL standard schema INFORMATION_SCHEMA? INFORMATION_SCHEMA.COLUMNS is certainly not expressive enough.

Mark Thornton

Posts: 275
Nickname: mthornton
Registered: Oct, 2005

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 2, 2009 11:30 AM

Reply

>
> Under what circumstances? For a BCNF schema, all data is
> simply a join away. With the SSD multi-machines of today,
> not an issue. Declarative constraints can do anything
> else I've ever needed. Remember: all data in the
> database is determined to be correct, either by the real
> world (a fact which is a "master file" fact; and not
> testable either in code or the data), or by other data.
> Declarative referential integrity.
>

Perhaps I should present an example from my work where the constraints which can be implemented in a database are too anaemic to be of any great use.

The data represents the loading (and unloading) of a compartmented tanker used for delivery liquid products. The tanker has a number of compartments of varying capacities, and a set of pumps which are used for pumping off the product at a customer (or pumping waste back on). For each product in every consignment you must specify how much is loaded in which compartment(s). For every stop at a customer, the sequence in which products are pumped through the various pumps is specified. Now the constraints:
1. The total volume loaded in a compartment at any point in time can't exceed its capacity.
2. The position of the centre of gravity of the vehicle when on the road (between visits) can't be too close to the rear or too far forward.
3. A compartment can only hold one product at a time.
4. If an empty compartment has not been cleaned, the previous product affects what can be loaded next. There is a table defining what products can follow which.
5. The last product through a pump restricts what can be pumped next through that same pump. Again this restriction is defined in a table.

Now looking at database constraints, you can easily ensure that a product field refers to an existing product and similar referential constraints. But in a database like SQL Server a check constraint can only refer to columns in the same table, so you can't use information in other tables as you might wish to enforce a product compatibility rule.

So what are database constraints doing for me here? Very little. All the major constraints can't (as far as I can see) be implemented reasonably within a standard database.

Now for methods like recomputing a feasible loading when an order changes. I don't fancy doing that in the database, and the code which does must clearly respect the constraints.

James Gates

Posts: 1
Nickname: jamesgates
Registered: Sep, 2008

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 2, 2009 11:59 AM

Reply

Use database constraints to preserve the essential integrity of the data model. Unique or not-null columns and foreign-key references, which are inherent to and invariant in the model, belong in the database--even if you have to duplicate some validation logic in the application tiers.

My rationale for this is two-fold.

First, in every significant system I've worked on, the data has outlived the original application code. For any number of reasons new requirements for accessing the data are identified. So, that original client-server may be replaced by or co-exist with web applications, mobile clients, Flex or other RIA clients, web services, and so on, yet the underlying data persists. In such cases the data is clearly where the lasting business value lies. Preserving its basic integrity has to be a given.

Second, this ability to ensure the basic integrity of a data model is one of the main benefits of a DBMS. By designing fundamental integrity constraints (which are very different from mutable business rules) into the declarative data model, the constraints are exercised and enforced on every possible access path. They are truly invariant and simply can't be violated. The same can't be said for constraints imposed at any other layer.

Note that I'm not arguing for building mutable business rules into the database constraints. But basic model integrity? Absolutely.

I'm also not arguing that input validation should be deferred to the database. I've made good use of Hibernate Validator, and expect to use Beans Validations or whatever is best in the future to consolidate my input validation logic. It's a good thing to be able to have input validated in the web client (for a more responsive user experience) and again in the web application (to catch anyone trying to hack the site) and again in the database (for any input that affects model integrity). These aren't just wasted processor cycles or unnecessary repetition, they're needed to fulfill the joint requirements of usability and system integrity. Overlapping your security zones is a good thing.

robert young

Posts: 361
Nickname: funbunny
Registered: Sep, 2003

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 2, 2009 12:27 PM

Reply

> Are you saying you are depending directly on the SQL
> vendor correctly implementing the SQL standard schema
> INFORMATION_SCHEMA? INFORMATION_SCHEMA.COLUMNS is
> certainly not expressive enough.

http://developer.mimer.com/documentation/html_91/Mimer_SQL_Mobile_DocSet/Data_dic_views2.html

Looks pretty complete to me. Again, if one prefers to use native catalog tables, then so be it.

James Watson

Posts: 2024
Nickname: watson
Registered: Sep, 2005

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 2, 2009 4:42 PM

Reply

> > The user may modify the resulting groups as desired.
>
> > Can you tell me how to set up database constraints to
> > implement that?
>
> It sounds like some sort of dynamic load balancing
> constraint?

It was some work I did years ago to implement facility space optimization.

John Zabroski

Posts: 272
Nickname: zbo
Registered: Jan, 2007

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 3, 2009 6:51 PM

Reply

> > Are you saying you are depending directly on the SQL
> > vendor correctly implementing the SQL standard schema
> > INFORMATION_SCHEMA? INFORMATION_SCHEMA.COLUMNS is
> > certainly not expressive enough.
>
> http://developer.mimer.com/documentation/html_91/Mimer_SQL_
> Mobile_DocSet/Data_dic_views2.html
>
> Looks pretty complete to me. Again, if one prefers to use
> native catalog tables, then so be it.

Alright! Now we're talking.

Never do that! Ever!

Querying a live catalog will cause the DBMS to lock it, and the core engine usually relies on the catalog for various tasks, including planning.

So, even if you do what you are proposing, that's a bad idea. You want an asynchronous model between your application and DBMS, and follow a very specific versioning scheme to correctly ensure *runtime application integrity*.

This is done using object state machines - simple Moore models of the interaction between the canonical data dictionary and the app/db.

Michele Simionato

Posts: 222
Nickname: micheles
Registered: Jun, 2008

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 7, 2009 8:15 AM

Reply

Just today we had a bug in production due to insufficient constraints in our db. In theory the constraint was not
needed, since the data enter through an automatic procedure
and by construction the data *should* enter correctly.

Everything worked fine for years. Four or five months ago
I made an "improvement" in the import procedure. As a consequence in very rare situations some inconsistent
data begun to enter in the db. Today have spent the day to
clean up the inconsistent data :-(

Now I have implemented the constraint at the database
level (it was non-trivial, with the need to define
a custom function) and I feel much happier. I feel confident
that the next time I introduce a bug in the import procedure
the system will break immediately, and it will not be
slowly polluted by inconsistent data for months before
recognizing the issue.

Just a data point in favor of db-level constraints.

robert young

Posts: 361
Nickname: funbunny
Registered: Sep, 2003

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 7, 2009 8:46 AM

Reply

> Alright! Now we're talking.
>
> Never do that! Ever!
>
> Querying a live catalog will cause the DBMS to lock it,
> and the core engine usually relies on the catalog for
> various tasks, including planning.
>

SELECT only access and READ UNCOMMITTED on information_schema should work on all databases with the schema; it's no different (shouldn't be anyway) from any other schema. UR is fine for a production database; one ought not to be updating the schema during runtime.

Catalog tables are just like any others. JDBC/ODBC query them routinely without incident.

Here's some <a href="http://www.simple-talk.com/sql/t-sql-programming/using-information-schema -views/">examples</a> for SQL Server (not my current cup of tea).

I've been kind of busy chasing stocks, so I haven't written up anything yet for the SQL Puzzles posted by the myriad naysayers. Will get to one of them soon.

John Zabroski

Posts: 272
Nickname: zbo
Registered: Jan, 2007

Re: Database Constraints: Use Them or Lose Them?

Posted: Oct 8, 2009 9:07 AM

Reply

> > Alright! Now we're talking.
> >
> > Never do that! Ever!
> >
> > Querying a live catalog will cause the DBMS to lock it,
> > and the core engine usually relies on the catalog for
> > various tasks, including planning.
> >
>
> SELECT only access and READ UNCOMMITTED on
> information_schema should work on all databases with the
> schema; it's no different (shouldn't be anyway) from any
> other schema.

> UR is fine for a production database; one
> ought not to be updating the schema during runtime.

UR = UNCOMMITTED READ?

"runtime"... welcome to the Web... the schema can and will constantly evolve and fault tolerance measures in your apps are necessary to prevent breakage (or allow 'refresh' to resolve breaks).

Apart from that, a DBMS that supports a Transactional DDL is nice (although there are practical scaling limitations that require breaking up DDL changes into micro-transactions.)

> Catalog tables are just like any others. JDBC/ODBC query
> them routinely without incident.

Not routinely. It is an opt-in model. DatabaseMetadata objects are hydrated by a separate transactional request. Stuff like WebSphere JDBC mediator queries choose to opt-in. IMHO, you should only opt-in on a data access fault, to provide fault tolerance.

> Here's some <a
> href="http://www.simple-talk.com/sql/t-sql-programming/usin
> g-information-schema-views/">examples</a> for SQL Server
> (not my current cup of tea).
>
> I've been kind of busy chasing stocks, so I haven't
> written up anything yet for the SQL Puzzles posted by the
> myriad naysayers. Will get to one of them soon.

SQL Puzzles?

Flat View: This topic has 56 replies on 4 pages [ « | 1 2 3 4 ]

Previous Topic

Next Topic

Sponsored Links

Copyright © 1996-2019 Artima, Inc. All Rights Reserved. - Privacy Policy - Terms of Use