Indexes in Oracle - An Introduction

Speaker Notes

Slide 1:

This presentation will introduce the concepts of indexes and constraints.

Be sure to read the notes Indexes in Oracle - An Introduction.

There is definitely some difference in the information presented.

Slide 2:

Please read the notes:

Indexes in Oracle - An Introduction

for additional information

Slide 3:

I created a new table to use with the indexes and constraints. It is called new_first_pay and was creating by taking all of the data from first_pay.

Slide 4:

NOTE: This command is case sensitive. For example, you could not use 'new_first_pay' - the name is enclosed in quotes and is stored as upper case so upper case must be used in the select.

Looking at the constraint information you can see the name given to the primary key.

Slide 5:

Note the NOT NULL - you have seen this in other examples where the primary index was in place but I wasn't using it. As you can see from the listing, there is nothing that indicates a primary key when you simply select.

Note that column settings were still in place when I did this listing so you see salary formatted. CLEAR COLUMNS will return it to normal and so the next time you see this listing you will not see the formatted salary.

Slide 6:

As promised, the clear columns returned salary to its unformatted state.

Note that the unique constraint violation says SCOTT and then the name of the primary key that was defined in the ALTER. SCOTT is used here because I logged on as scott/tiger.

Slide 7:

In the first example, I listed pay_id as a column/field and tried to insert a null value with the word null.

In the second example, I did not list pay_id as a field and therefore tried to insert nothing in the field. This would have made it null.

Slide 8:

Primary keys must be unique and not null.

Slide 9:

When the description of the file is viewed, not null appears for jobcode which is the primary key and joblevel which was specified as not null.

Clearly what we see here is that while NOT NULL frequently gives us a clue as to what the primary key is, it is not totally reliable.

Slide 10:

This is the information about the three fields in FIRST_PAY_DEPT.

Slide 11:

I did three inserts and put these three records into the table. Notice there is a match to three of the four jobcodes used in new_first_pay. When I tried to establish a link between the jobcode on the new_first_pay table and the jobcode which is the primary key on the first_pay_dept table, the fact that one code was missing on the first_pay_dept table was detected and the alter was not allowed. After I added the AP record to the first_pay_dept table, the alter was allowed because there was now a match for all jobcodes in new_first_pay to a job_code in first_pay_dept.

Slide 12:

If a table contains existing data, you can not alter it and put in constraints that would make any of the existing data invalid. If the table contains no data when the constraints are applied then there is no concern about violation of data.

Slide 13:

Once a foreign key has been linked to the prime key in first_pay_dept, I cannot add a record to new_first_pay that does not have a corresponding jobcode in first_pay_dept.

To have this record accepted, I would first go into first_pay_dept and add a record with jobcode AA. Then I could go back to new_first_pay and add the record shown.

Note that the error message refers to the name you gave the constraint. While naming a constraint is not required, it is helpful as illustrated in these examples.

Slide 14:

I have created three indexes on the table test_index. An index gives you better access to data then having Oracle check each record.

Slide 15:

Note that when the primary key was created the NOT NULL appeared for IDNO when the table was described.

Slide 16:

As you can see, a primary key can be made up of more than one column/field.

Notice in the description, both receive not null.

Slide 17:

Remember primary keys must be unique. Entering the same last name and first name combination violated this principle.