All language subtitles for [SubtitleTools.com] Foreign Keys - Learning Oracle 12c [Video]

In this lesson, we
look at foreign keys.

So foreign keys,
like primary keys,

are used to enforce
data integrity.

They are what we call
database constraints.

And they ensure that data
conforms to a business model.

So in the parent-child
relationship

of data in an RDBMS, the foreign
key is the child in that.

So where the primary
key is the parent value,

the foreign key value is
the child, if you will.

So foreign keys are similar to
primary keys in their addition

or creation.

They can be added out of
line or after creation.

They can't be done inline.

So let's look at creating some
tables with foreign keys that

refer back to other tables
with primary key values.

I'm going to connect
to the Scott user.

And let's do an out of
line foreign key constraint

in a table.

patient_ool for out of
line, patient_id column,

patient_name, and doctor_id.

This is the doctor
of the patient.

And here we use the
keyword constraint.

And we're going
to give it a name.

We can do this with
primary keys as well

when we do them out of line.

Call it fk for foreign key.

And then the
keyword FOREIGN KEY.

Then the column, which is
doctor_id, and the keyword

REFERENCES.

Table that has the
primary key value.

And the column in that table.

All right.

So there's a lot here.

So let's go back through
it and kind of break it

down a little bit.

So up to this point, this is a
typical create table statement.

However, we wanted to create a
table that has a foreign key.

So when you create a foreign
key, you create the constraint.

But you have to specify
where the parent is.

So where is the parent value?

Now, you don't have to
do this in a primary key,

because a primary key can
exist without a foreign key.

But a foreign key
needs a primary key

to reference back to.

So that's the same
as saying, you

can have parents
without children,

but not children
without parents.

So we use the
keyword constraint.

And then we've given
it a name here.

If we don't give
it a name, it just

gets a system-generated name.

The keyword FOREIGN KEY.

And this is the column
in the patient table,

so it's doctor_id.

And then it references the
doctor out of line table

that we've already created.

And the column that it has a
primary key on is doctor_id.

So you can see they're
named the same.

We've named the
columns the same.

And this is a very
common way to construct

the names of your primary key,
foreign key relationships,

because it makes it more evident
when you look at a data model

itself to say, OK, these
are common columns,

because that's what we
join on in an RDBMS,

common columns that
have common values.

In this relationship,
let's say a doctor

can have many patients.

But a patient in our
case for this example

would only have one doctor.

And so that doctor_id is back
in the doctor out of line table.

So that's creating an out of
line foreign key constraint.

So let's try one after creation.

Look at the syntax for that.

Grab all of this for the
sake of not retyping.

Change this to patient_ac
for after creation.

So now we just have
a table that doesn't

have a relationship
to any other table,

no foreign key constraint.

But let's say we
want to add one.

So let's use the alter
table patient_ac,

add constraint, patient_ac_fk.

foreign key, then the column
in the patient_ac table

that is the foreign key.

And again, uses references
table called doctor_ac

and the primary key
column in that table.

So let's look at a little
bit about how this works when

we insert data into a table.

So let's insert a value
into the patient_ac table.

All right.

So the patient_ac table has
patient_id, patient_name,

and doctor_id.

We're going to attempt to insert
into the patient_ac table.

So notice the error that we get
here, integrity constraint--

and there's the name
that we gave it--

is violated.

The parent key is not found.

So what happened?

We attempted to put a row
into the patient_ac table.

The patient_ac table has
a foreign key, primary key

relationship between
the doctor_ac.

And so when we
attempted to put a 2

into the doctor_id column,
a part of the primary key,

foreign key relationship
is for Oracle

to check that that exists
in the other table,

in the doctor_ac table.

It did not, so we
receive an error.

And it does not allow us to
put that value into the table.

So let's fix this by inserting
a doctor into doctor_ac.

So his doctor_id is 2, which
matches what we're attempting

in the patient table.

One row inserted.

We commit.

And now I'm going to
clear this for clarity.

And then we're going
to attempt to run

our insert into the table
with the foreign key.

And the row was inserted.

And we commit.

select star from patient_ac.

And our values are put in there.

And this works because
the doctor_id number

2 in this table matches
the doctor_id value of 2

in the doctor_ac table.

So this is just a
little bit about how

that foreign key and primary key
relationship works in Oracle.