Performance Tuning  «Prev 

Learning bridge to Primary Keys

The course Oracle SQL Extensions addresses primary keys. If you need a refresher, read through the following material.

What is a constraint?

A constraint [1] is a database rule that you define within the database to enforce certain conditions regarding the data that is stored in the database tables. Once a constraint is defined, all data that is added to the database must comply with the constraint. If there is existing data that does not comply, the constraint cannot be created. You can, however, disable a constraint once it has been created. You might do this in cases where you have intermediate data that is placed into a table and then manipulated until it does comply with the constraint.

What is a primary key?

The primary key is a basic component of a relational database table. The primary key is one or more columns that define a unique row. While it is not required within Oracle to define a primary key, it is recommended that you do so on most of your tables.
The advantages of creating a primary key are:
  1. Defining foreign keys that reference the primary key and are enforced automatically by the database
  2. Improved data integrity because the primary key itself is automatically indexed and is enforced for uniqueness
  3. Improved performance because an indexed primary key is one of the fastest methods of retrieving data from the database
  4. Defining a primary key in the CREATE TABLE command. This is an advantage because it saves a step when creating a table and it allows the database to preserve and recreate the primary key more easily when you use export and import.

Constraints that apply to the more than one column are called table constraints [2]. Table constraints are used to define
  1. primary and
  2. foreign keys
that contain more than one column (the PRIMARY KEY and FOREIGN KEY constraints). Another use for table constraints is any validation rule that involves more than one column (a CHECK constraint).
To define a primary key, you use Oracle's constraint command when creating a table. There are two ways to write the code, shown below:

Crate Primary Key
  1. If you want the system to supply a name for the constraint, you can omit the "CONSTRAINT constraintname" portion of the syntax.
  2. This first technique is to write the primary key constraint within the column specification. This only works if the key is exactly one column.
  3. If you have more than one column in the table, place a comma in front of the second column definition and all the following columns. Enclose the entire set of columns with parentheses.
  4. The second way uses a table constraint and is written at the end of the list of column specifications, just before the closing parenthesis. If you want the system to supply a name for the constraint, you can omit the "CONSTRAINT constraintname" portion of the syntax.
  5. Place the column or columns inside the parenthesis where you see "column list".

Defining a primary key in the ALTER TABLE command

You can also add the primary key after a table is created using the ALTER TABLE command. The syntax of this command is shown in the graphic below.

ALTER TABLE command in Oracle
ALTER TABLE command in Oracle

A table can have only one PRIMARY KEY constraint defined.

Defining a unique key constraint

A unique key[3] is a second primary key. A unique key is unusual in relational database design, and I have seen it used most often when converting an older system over to a newer design that uses a different primary key. To aid in converting from one system to the other, a UNIQUE KEY constraint can be used to preserve the old key without interfering with the new primary key's relationships to other tables.
There are only two differences between a PRIMARY KEY and a UNIQUE KEY constraint:

  1. You cannot define FOREIGN KEY constraints that reference a UNIQUE KEY constraint.
  2. You can define more than one UNIQUE KEY constraint on one table.
To define a UNIQUE KEY constraint, use exactly the same syntax as the PRIMARY KEY constraint, except substitute the word UNIQUE for the words PRIMARY KEY.
[1]Constraint: A constraint is a database rule that you define within the database to enforce certain conditions regarding the data that is stored in the database tables.
[2]Table constraint: Constraints that apply to more than one column.
[3]Unique key: A second primary key in a table. A table can contain more than one unique key.