All language subtitles for [SubtitleTools.com] Check Constraints - Learning Oracle 12c [Video]

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.