Intro to Database Systems - Part 11 to 13 : Integrity Constraints

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:

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:

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:

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

Here are a few examples of an attribute-based check:

It is important to notice that:

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:

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

If we want to remove a constraint:

If we want to add a constraint:

 
34
Kudos
 
34
Kudos

Now read this

Intro to Database Systems : Schema Refinement - Functional Dependencies

Schema refinement is just a fancy term for saying polishing tables. It is the last step before considering physical design/tuning with typical workloads: 1) Requirement analysis : user needs 2) Conceptual design : high-level description,... Continue →