Oracle Database Constraints : Formal Predicates

Why Oracle?

To my knowledge, Oracle is the database vendor that has the best support for declarative constraints.

What do I mean by declarative? That constraints are defined in DDL (Data Definition Language): This means that you do not have to code them in your application.

Oops: Let's talk a little about that: Where should constraints be defined: In the database, or in your application?

In my view, there is no discussion about where to handle database constraints: In the database, not in the application. Why?

Applications come and go, but your database will be used by several applications. If you include constraints in your application, you will have to copy that code into new applications as well.

You're easily able to see the consequences: Code must be maintained in several places, it will (most likely) perform poorer than if in the database, and most importantly: What if you forget to include a constraint in your new application?

But, Oracle database constraints, while giving you some possibilities for declarative constraints, still is not good enough.

This series of articles explain the possibilities, but also the restrictions you will meet when using Oracle database constraints.

After you have gone through the articles, at the bottom of this page, we will look into a few possible solutions for including constraints in other ways than the (limited) possibilities that Oracle is giving you.

• What are Oracle constraints? This article on Oracle constraints defined describes basic features for Oracle's constraint ability.
• Without a naming standard, constraints will be very difficult to manage. This article suggests a constraint naming standard that you might find useful.
• Once you have your database up and running, enabling and disabling Oracle constraints may be performed. This article shows you how it can be done, and consequences.
• Defining Unique constraints is implicitly done via primary keys. However, you may want to define such constraints also for Candidate keys, as well.
• Foreign key constraints are defined in order to enforce Referential Integrity. Without it, relationships between tables are uncontrollable.
• You can "toggle" constraints. If you need to use deferred integrity constraints, however, you most likely have a design flaw in your database.
• How can you combine indexes and constraints in order to gain performance? This article discusses Oracle constraints and Indexing.
• Did you know that you can have constraints on views? This article explains the possibilities for Constraints on Views.
• This article discusses disabling of constraints in more detail, and also includes scripts for Disabling Constraints.
• Oracle allows you to create User-defined types. However, you are pretty much on your own, with limited support...

Return to Database Design home