Original subtitles

In this lesson, we're going to look at check constraints.

So a check constraint is another type of database constraint

that's used to enforce data integrity, which

ensures that data conforms to a business model.

So there is a lot of different ways

that data integrity can be done or performed.

There's considerable argument, and has

been for many years, about whether or 0

it's better to check data integrity at the application

layer or at the database layer.

And so usually, a balance is struck

and things like primary key, foreign key relationships

are done in the database.

And then certain data checking occurs in an application.

But that doesn't have to be the case because we

can use check constraints in a database

to check data before it goes into a row and a table.

So let's take a form entry for example.

So this is just a typical internet form

where you've gone to a website and you want to sign up.

And let's say they put a field in there for your age,

for you to enter your age.

Now some degree of checking should

occur to ensure that the value that's put in for age

is greater than zero, not a negative number,

is not a letter, or something like that.

And so that can be done with a check constraint.

So when that data is put into the form

and it's passed back to usually a PL/SQL package or procedure

to insert the data, then we attempt to put that data in.

And if it doesn't conform to the check constraint,

then an error is returned and the row is not inserted.

So a check constraint is going to allow a check on the data

before it goes in.

Like other types of constraints, it

can be done in line, out of line, or after creation.

So let's take a look at adding check constraints.

So I'm going to connect to Scott and I'm

going to create a hospital table.

Call it hospital_il because we're

going to use an inline check constraint in the example.

Hospital_id, hospital_description,

hospital_code.

And this code is going to be basically a location code.

So we can add an inline check constraint here

that checks and makes sure that that value that's entered in

is within a certain set.

So we could say, check hospital_code in IL

for Illinois, MO for Missouri, or KS for Kansas.

And watch your parentheses here, because we

have an outer one on the check constraint

and then another set of parentheses

to define the set of values.

Then there's the final one that defines the create table

statement itself.

Notice that SQL Developer kind of helps us out with this.

Whenever we highlight a parentheses

it shows us the pairing.

Let's try it again.

And hospital_il is created.

So that's an example of an inline check constraint.

So it is defined as a part of the column itself.

So let's look at an out of line constraint

and how the syntax for that works.

Just grab all of this, my table definition.

Add a comma here.

Change this to ool for out of line.

And then we use the keyword constraint [INAUDIBLE] code

for hospital code, ool_ck for check,

and the keyword for the constraint check.

Now again, this code_ool_ck is just the name

that we're giving to the constraint.

In.

So this is an out of line constraint.

So we have given it a name here.

And notice that we can't give a name to a constraint

when it's used in line.

So that's one of the advantages of either doing it out

of line or after its creation.

But again, it is checking for hospital_code

to be in one of those three values.

And it created successfully.

The last type of check constraint that we can add

would be after creation.

So let's go ahead and create this table,

call it ac for after creation.

And now we've created it without the check constraint.

So to check, we put altar table, ac, add constraint, code_ac_ck.

Check that hospital_code is in this set of values.

And table is altered, so that check constraint is put in.

So let's try inserting data here and let's

see if we can violate the constraint.

Insert into hospital_ac values.

We have a hospital ID, hospital description, and the code.

So let's attempt to put in KY.

It gives us an error, check constraint,

and gives us the name, violated.

The values being inserted do not satisfy the named check.

And then tells us, do not insert values

that violate the constraint.

So let's say this was a mistake, it

was meant to be KS for Kansas.

Clear this out for clarity.

And the row inserts.

Why?

Because it meets the check constraint.

It can be any of these three values, but no other value.

So that's another example of how we

can use a constraint, in this case a check constraint,

to enforce business rules and to enforce data integrity.