Database Constraints

[3moose]

Hi,

Can someone give me a short explanation of what a database constraint is? I understand that "not null" and "primary key" are constraints... but there are other constraints and I simply don't know what one might use them for... I have this option in VS.NET and just want to understand the subject a bit better and if you can reference any resources, would be grateful.

3Moose.

[r937]

besides not null and primary key constraints, there are two other important types of constraints

foreign key constraints ensure that a value placed into a foreign key must already exist as a primary key value in some table

check constraints ensure that whatever condition is written into them will always be true, and are often used to implement domain integrity

for example,

create table persons
( id integer
, name varchar(937)
, gender char(1)
, constraint valid_gender check ( gender in ('M', 'F', 'X') )
)

[asterix]

Hi, just clarifying things here...

besides not null and primary key constraints, there are two other important types of constraints

Kinds of constraint:

1. Not Null Constraints
2. Unique Constraints
3. Referential Constraints
4. Check Constraints

So there is, IMO, no category "Primary Key Constraints", maybe you meant to say "Unique" constraints? PK Constraints are by far the most common kind of unique constraint, but there are the simple unique constraints too.

Also:

check constraints ensure that whatever condition is written into them will always be true, and are often used to implement domain integrity

Hmmm, nope, not exactly. Domain Constraints are just one kind of check constraint, and they are used to maintain domain validity, that is, to ensure that the value stored in the field conforms to the datatype's domain specification, in your example the sex.

Another kind of check constraint is used to maintain data integrity. OK, so we know that we can store - using your examples - the values X,M or F. That maintains domain validity, but what about the following conditions, expressed as:

"The gender attribute of each person must be either M, F or X, meaning male, female or unknown. All persons must be of the same sex as their counsellor".

You would implement the specification that is in italics by using a check constraint, here an example:

  check (sex =
    (select sex from counsellors c where c.id = counsellorid)
  )

This check constraint does not ensure domain validity (X, M, F) but it does ensure data integrity, according to the specification.

[r937]

picky, picky

i'm surprised you didn't also mention assertions

[rycamor]

There are two ways of thinking about constraints:

1. Those things you can declare in SQL with the keyword CONSTRAINT (which are also implied if you simply do such things as columname REFERENCES othertable(columnname). As r937 pointed out, these include such things as not null, primary key, foreign key, and check constraints.

2. One of the fundamental concepts of relational database systems is the idea of handling business logic by constraints. In that broader context, everything that enforces your data model and the logic by which it operates is a constraint. Constraints can be dealt with at the column level (not null, check constraints, foreign keys, etc..), table level (table-level check constraints, primary keys), or even at the whole database level. Since most DBMS don't include a declarative constraint system at the database level (such as check constraints), these are usually handled by such things as triggers and stored procedures, although some systems also have a concept of rule declarations that can apply to interactions between tables. In that sense, you can enforce logic that says such things as "do not allow X in this table unless conditions Y and Z have been met in any arbitrary combination of other tables".

[asterix]

picky, picky

i'm surprised you didn't also mention assertions

Yes, quite picky
What do you mean when you say assertions?

[asterix]

1. Those things you can declare in SQL with the keyword CONSTRAINT (which are also implied if you simply do such things as columname REFERENCES othertable(columnname). As r937 pointed out, these include such things as not null, primary key, foreign key, and check constraints.

OK, I'm with you so far.

2. One of the fundamental concepts of relational database systems is the idea of handling business logic by constraints.

Hmm, well, I'd call that "maintaining data integrity" but if you call that "business logic" then that's ok too.

In that broader context, everything that enforces your data model and the logic by which it operates is a constraint.

Yes. Nice definition.

Constraints can be dealt with at the column level (not null, check constraints, foreign keys, etc..), table level (table-level check constraints, primary keys), or even at the whole database level. Since most DBMS don't include a declarative constraint system at the database level (such as check constraints), these are usually handled by such things as triggers and stored procedures, although some systems also have a concept of rule declarations that can apply to interactions between tables. In that sense, you can enforce logic that says such things as "do not allow X in this table unless conditions Y and Z have been met in any arbitrary combination of other tables".

If you mean that something like "send me an email when the stocking level of product b drops below 50 units" is a constraint then I would have to disagree. That is a business rule, not a constraint ensuring data integrity.

Constraints ensures that the meaning of the information contained in the data remains unchanged. That is, you should in the future awlays be able to ask the same question of the data and have it answered in the same way. The semantics remain unchanged, and that is what constraints enforce.

[r937]

assertions are part of standard sql

for example,

CREATE ASSERTION enrollLimitCheck 
 CHECK(
   NOT EXISTS (
       SELECT code, enrollLimit, c.size 
         FROM course 
       NATURAL 
         JOIN (
              SELECT code, COUNT(*) AS size
                FROM class 
              GROUP 
                  BY code
              ) AS c
       WHERE enrollLimit < size
              )
      )

[r937]

FYI, i just ran the above (which i, ahem, lifted off a web page i found via google) through the Mimer SQL Validator and it says:

The following features outside Core SQL-200x (draft) are used:

F591, "Derived tables"
F401, "Extended joined table"
T501, "Enhanced EXISTS predicate"
F671, "Subqueries in CHECK"
F672, "Retrospective check constraints"
F521, "Assertions"

just in case you were wondering

if you've never seen assertions, it's probably because so few commercial databases implement them

[asterix]

assertions are part of standard sql

Well there's a surprise, I have never heard of them before!
I use postgres, mysql, oracle and mssql fairly regularly, and none of these do ASSERTION, so is it limited to DB2 or something?

[r937]

i have no idea (haven't used db2 in years), but i would be extremely surprised

standard sql also allows you to create actual DOMAINs, but again, these aren't well supported in commercial databases either

the secret, obvioulsy, it to implement assertions and domains in as much of an isolated way as possible (e.g. with a trigger) so that, when commercial databases finally do catch up with the standard, you can just yank your code and declare the sql object, and it will have minimal disruption

sort of like how we used to write application logic to do ON UPDATE CASCADE before this was actually something you could declare

[asterix]

standard sql also allows you to create actual DOMAINs, but again, these aren't well supported in commercial databases either

Yeah, I've used that a few times in SQLAnywhere. But I don't think it does ASSERTION.

[rycamor]

If you mean that something like "send me an email when the stocking level of product b drops below 50 units" is a constraint then I would have to disagree. That is a business rule, not a constraint ensuring data integrity.

Making things like emails happen is not directly a function of a database management system, but of an application environment. The purpose of a DBMS is to manage data. But still a constraint can be involved in that, in the sense of "don't allow the stocking level of product b to drop below 50 units unless a corresponding record exists for that event in the email_alerts table".

Constraints ensures that the meaning of the information contained in the data remains unchanged. That is, you should in the future awlays be able to ask the same question of the data and have it answered in the same way. The semantics remain unchanged, and that is what constraints enforce.

I would argue that a constraint has a more fundamental definition than the above: a constraint is a declaration of what a value to a variable is allowed to be in on its own or in any given set of declared relationships with any other given set of variables (in one sense, tables--and even whole databases-- are variables). But in general, yes, the idea is that a constraint is a declarative thing, not a procedural one. It is not concerned with "how" something achieves a certain value or condition, but only with the "whatness" of things. The constraints only provide the channel through which the program must navigate.

However, I (and I think C.J. Date also) tend to disagree that a constraint is not a business rule. Most business rules are expressed in exactly the same way a constraint is expressed (logically, if not syntactically). And since modern SQL products are not really completely declarative--and in fact neither is the ANSI SQL spec itself, things get a little muddy about what exactly crosses the border between constraint and procedure. Often we have to use procedures to enforce constraints. Now, such a thing as making sure that changes to a table are logged in another table, I don't know... at first blush I would say it is not a constraint, but in one sense it can be, since you can express it in the form "no value in variable X can be changed unless a corresponding value exists in variable Y". Of course, in that sense, the DBMS engine itself would then be expected to take on the task of making the action occur, but the language is purely declarative. Hmm....

[asterix]

Making things like emails happen is not directly a function of a database management system, but of an application environment. The purpose of a DBMS is to manage data. But still a constraint can be involved in that, in the sense of "don't allow the stocking level of product b to drop below 50 units unless a corresponding record exists for that event in the email_alerts table".

Did you contradict yourself? Sorry, but you are starting to lose me. Earlier you said:

One of the fundamental concepts of relational database systems is the idea of handling business logic by constraints. In that broader context, everything that enforces your data model and the logic by which it operates is a constraint.

Which would seem to mandate an application environment within your DBMS to allow exactly such business rules as emails being sent to be explicitly enforced by your constraints.

I would argue that a constraint has a more fundamental definition than the above: a constraint is a declaration of what a value to a variable is allowed to be in on its own or in any given set of declared relationships with any other given set of variables (in one sense, tables--and even whole databases-- are variables).

Now you are back to calling database objects objects "variables", as though the DBMS is, actually, nothing more than a virtual machine based around set theory. Although I do, actually, agree with your definition about constraints defining the allowed state of tuples, even when they are in a relationship with other tuples. That is, in a nutshell, the fundament of RDBMSs.

I don't, however, think that this is any more fundamental than simply saying "constraints ensure the integrity of the semantics of the data".

But in general, yes, the idea is that a constraint is a declarative thing, not a procedural one. It is not concerned with "how" something achieves a certain value or condition, but only with the "whatness" of things. The constraints only provide the channel through which the program must navigate.

The pendulum swings back again. What "program" do you mean? Is the RDBMS the same as the "program" for you?

However, I (and I think C.J. Date also) tend to disagree that a constraint is not a business rule. Most business rules are expressed in exactly the same way a constraint is expressed (logically, if not syntactically).

When you say "most are...", you mean "some aren't". That is hardly a sound theory, it is more like a heuristic. I specified a business rule earlier, but you say that sending emails on trigger events can't be handled with constraints, only application code. Ergo your argument is not, exactly, watertight.

And since modern SQL products are not really completely declarative--and in fact neither is the ANSI SQL spec itself, things get a little muddy about what exactly crosses the border between constraint and procedure. Often we have to use procedures to enforce constraints. Now, such a thing as making sure that changes to a table are logged in another table, I don't know... at first blush I would say it is not a constraint, but in one sense it can be, since you can express it in the form "no value in variable X can be changed unless a corresponding value exists in variable Y". Of course, in that sense, the DBMS engine itself would then be expected to take on the task of making the action occur, but the language is purely declarative. Hmm....

Hmm, yes.
I don't know where we are going with this line of thinking. Either:

a) RDBMSs enforce business rules, all constraints are also business rules, ergo, RDBMSs are application environments for enforcing business rules.

or

b) Some business rules can be expressed as constraints within the framework of relational database theory, others can't and therefore require application logic.

In the case of a) we can expect products (such as SQL Server 2005 or Oracle 9) which are, actually, application environments based around set theory, or b) a continuation of current practice, in which the database is a bag for storing data in and you had better program your own application if you want (an arbitrary) semantic interpretation of the data.

[rycamor]

Well hey asterix, sorry to be losing you...

You seem to be festering for a fight, while I am just trying to clarify terminology and perhaps examine the implications behind them, in the hope of helping our friend 3moose, who has probably been completely steamrollered by the discussion now.

In short, when I said 'program' above, I mean application, not the DBMS nor the database which it might contain at any one time. When I say "everything that enforces your data model and the logic by which it operates is a constraint", you are assuming that I am implying the triggering of external actions such as emails, etc... when I never said that. But I do think that constraints often imply actions, in which case it is up to the application environment to execute them. Whether that occurs in the DBMS or outside is irrelevant to me, as long as the constraint behind it is enforced. In other words, the DBMS may be taking on extended roles which are not part of the classical definition of DBMS, and in that case even though being physically executed by the DBMS those are still application roles, not database management roles. I'm not implying a right or wrong to this, simply explaining what I see the logical boundaries to be. If I am wrong, please elucidate.

One of the biggest problems in all this is for everyone involved to agree on a terminology and the implications behind the terminology. To me, a business rule is always expressed as a declarative constraint, and as such is nothing more than a constraint. But to others (and apparently you), it is more. Ergo, my discussion above (and the "Hmm...") was meant to be exploratory, rather than some diatribe I wanted everyone to agree with. You yourself throw some terminology around that is fraught with misunderstanding by various parties. I brought up the term "business logic" and you brought up the term "business rule". Both of those are informal, thus not subject to clear established meaning that is a foregone conclusion.

I don't, however, think that this is any more fundamental than simply saying "constraints ensure the integrity of the semantics of the data".

But words like 'integrity' and 'semantics' are less fundamental terms. Not that they are wrong, but they are built upon the base of variables->values. I was trying for a simple, basic explanation for our thread-starter here.

In the case of a) we can expect products (such as SQL Server 2005 or Oracle 9) which are, actually, application environments based around set theory, or b) a continuation of current practice, in which the database is a bag for storing data in and you had better program your own application if you want (an arbitrary) semantic interpretation of the data.

To me these kinds of statements just raise a multitude of new questions, which also don't have easy definitions, and which have arguable assertions. Based on your arguments, I'm really not sure what you think a DBMS is (or should be). But please let's not continue this in this thread. If you really want to discuss how to clearly define "semantic interpretation", or "application environments based around set theory", etc ... shouldn't we start a new thread? But I think such a thread would turn into a book-sized discussion before anything substantive comes out of it .

[asterix]

You seem to be festering for a fight

Not at all. Sorry for creating that impression.

And no, I don't really want to start a new thread either!

[MattR]

Fabian Pascal, everyone's favorite curmudgeon
Integrity constraints are the DBMS-understood meaning of the database, and the formal representation in the database of the informal business rules, which are the user-understood meaning.

Seems to sum it up succinctly. Think of the origin of the term "business rule" -- what is a rule but something that your data must adhere to? More generally, a rule/constraint in a DBMS is a predicate, such as "All employees earn a salary" or "All Volvos are made in Sweden". If you ever have an operation which violates this rule it is aborted (or rolled back in transactional terms).

You can think of constraints as a series of if(..) statements on your data. Consider the Volvo constraint. It could be something like:

If( Car.Make = ‘Volvo’ and Car.Origin= ‘Sweden’ )
Allow insert or update of data
else
Rollback: Constraint violated

SQL DBMS products have taken some general constraints and codified them in some specific syntax. They have made the constraint “All products must be uniquely identifiable” and called that a “Primary Key” constraint. They have turned “All products must have a supplier” and turned that into a “Foreign Key” constraint, etc. etc. Fundamentally all constraints are identical insofar as they are Boolean (and declarative) predicates.

Of course, pragmatically speaking I’d say Rudy’s explanation is just fine; the SQL DBMS products have a certain subset of the predicates available in first-order predicate logic (and also have a subset of predicates available in the SQL spec as Rudy pointed out with the ASSERTION operator!!) and that’s all the original poster was looking for.

[3moose]

Thanks for all your responses! I will need to read this a few times as some of it seems a bit complex - however, I have understood the gist of what constraints are. Thanks again...