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

In this lesson, we'll
be taking a look

at primary and unique keys.

So primary keys are
commonly associated

with the term, data integrity.

And data integrity
is the process

of ensuring that data conforms
to a particular business model.

In order to achieve
data integrity,

we use what's known as
a constraint or database

constraint.

And a constraint is a
database object that

enforces that data integrity.

We can build some of our
business models, some

of our business rules
into the database

in the form of constraints.

And those constraints help
us to achieve data integrity.

So the primary key/foreign
key relationship

is really the core of the RDBMS
because it allows two tables

to have a relationship
between each other.

And that's what an
RDBMS is all about.

The idea that we can
separate the data out

into tables as opposed to the
old flat file model but still

have them be related.

And so the primary key/foreign
key relationship is really

we could think of as the
parent/child data relationship

where the parent is
the primary key value

and the child is the
foreign key value.

So a primary key has
certain characteristics

that it has to conform to.

First and foremost,
it cannot be null.

So a primary key
cannot be a null value.

And it must be unique.

It cannot have any
duplicates of a primary key.

So when we talk
about a primary key,

we use the phrase that a
primary key uniquely identifies

every row in a table.

So any primary key
value will be associated

with one and only one row.

Furthermore, a primary key can
be paired with a foreign key,

but it doesn't have to be.

It's not uncommon to
see data models where

tables have primary keys but not
necessarily have foreign keys.

Although without
the foreign key,

we're not enforcing the business
model and the business rules.

There are coding standards
in some companies

that just say simply that every
table must have a primary key.

So they may use a synthetic
key with a sequence, something

of that nature, but a
primary key on every table

so that that value can uniquely
identify every individual row

in the table.

A primary key can
also be composites.

So it can be a composite
like a composite index.

That is to say that it includes
more than one column in it.

So we think of a primary key
column, and that's very common.

But it can also be a composite
with two or more even columns.

And a unique key
is exactly the same

as a primary key,
the same rules,

except that null
values are allowed.

So the question comes
up every now and then,

can a unique key be
paired with a foreign key

to enforce data integrity?

And yes it can.

Although it's not always the
best choice because it does

allow null values.

So those unique
key values do not

match any foreign key
values if they are null.

We can create primary
and unique keys

a number of different ways.

So we can create them in line
which is within the column

definition themselves.

So when we use the
create table statement

and we define the columns
and their data types,

we can also define that
column as being a primary key.

We can do what's
called out-of-line,

which is at the end of a
create table statement.

So the entire create table
statement is written.

And then at the
end of it, there's

a clause that allows the
creation of a primary key.

And it allows us to give
the primary key value a name

and so forth.

And finally, we can do it after
creation using the altar table

command.

So we create the table without
a primary key or unique key.

And then we issue
another statement

after using altar table.

So which way that these
are done in an organization

usually depends on
their coding standards.

Let's say that there
is a script to create

a number of different tables.

Under this model, you would
have the create table statements

that run, and then they
would just create the table

with its columns.

After that, there
would be an altar table

that added all of the
individual constraints,

including primary keys, foreign
keys, or check constraints,

or whatever it would be.

That's one model, but not
every coding standard model

is the same.

So let's take a look
at how this works.

Connect with our
Scott user, and we're

going to create a table
with an inline constraint.

I call this doctor
il, doctor id column.

And here we put the
key word primary key.

So we're defining doctor
id as the primary key.

And then execute the statement.

And so the doctor il table
is created with the doctor id

as the primary key.

There's no foreign keys
associated to it at this point.

But it is structured
as the primary key.

So that's one way of adding
a primary key in line.

So let's look at an out-of-line.

I'm going to copy most of this.

I'm going to put a comma here.

So up to this point,
this is a creation

of a table without
a primary key.

So here at the end
out-of-line I put primary key.

And I define the column
for the primary key.

So I'm creating the
same structured table

only I'm doing it out-of-line.

It's a different name as well.

So let's execute that.

So that's an
out-of-line constraint.

Finally, the primary key could
be done after the create table

statement.

Say, ac for after creation.

So this is just creating
the table itself.

And then we use an altar table,
doctor ac, add primary key.

And of course, we have
to tell it the column.

So now the doctor ac table has
had a primary key added to it.

Now if there's already
data in the table,

keep in mind that the
addition of the primary key

is going to validate the data.

So it's going to go
through and make sure

that all the rules of a
primary key are conformed to.

It has no null values.

And it has all distinct
values, no duplicates.

But those are three
different ways

to add a primary key in a table.