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 - Part 6 & 7 : Basic SQL

This part won’t cover the notions of passing from relational algebra to SQL since this has already been covered in the previous post. In this section, we will start by introducing a main overview of a basic SQL query. Then, we structure... Continue →