Original subtitles

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.