Constraints are there to impose restrictions on what data is allowed in the database. The basic E/R model introduces us to 2 types of constraints:

KEYS

Multiplicities

The relational model allows us to be more flexible. It imposes bounds on attributes. For now, we are able to force an attribute to be:

non null

of a specific domain (integer, string, etc)

But what if we need to express the situation of a student that can only take some courses if his GPA > 3.0? What if, in order to be a teacher assistant, you need to have successfully passed that course with an A or you need to have a 3.0 of cumulative GPA? By introducing the SQL keyword CHECK, we introduce 2 new notions:

attribute-based checks : fancy name to mention that we are imposing a constraint on a column

tuple-based checks: imposing constraints on rows.

We are introducing checks (and in general, constraints), for the following main reasons:

to catch data-entry errors (we can only input a GPA between 0.0 and 4.0)

For the cases where we play with more than one one column, we need to use tuple-based checks (basically row checks). But aren’t rows composed by columns? If we want to check on a row, why can’t we simply do the following:

We can, only that the previous command checks column1 AND column2, not column1 OR column2. What if we want to validate a check on one OR another attribute? That’s where tuple-based checks are useful. The syntax is slightly different:

What if we want to modify our constraints, or remove them? What if for this year you don’t need to have a 3.0 GPA to be accepted to an MBA, but a 2.6? In that case, we need to use a naming constraint convention.

In the following example, we have 2 constraints:

skaterID is an independent key

rating should be between 1 and 10

If we want to name our constraints or to edit them, we’ll do the following: