Lesson 5	PRIMARY KEY and UNIQUE constraints
Objective	Describe the advantages of using primary key constraints.

PRIMARY KEY | UNIQUE constraints

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^[2] 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:

Defining foreign keys that reference the primary key and are enforced automatically by the database
Improved data integrity because the primary key itself is automatically indexed and is enforced for uniqueness
Improved performance because an indexed primary key is one of the fastest methods of retrieving data from the database
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^[3]. Table constraints are used to define primary and 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. The first way is written within the column specification:

CREATE TABLE [user.]table
(  column_name datatype [DEFAULT expression]
       [CONSTRAINT constraintname ] PRIMARY KEY,
 column ... )

This only works if the key is exactly one column. If you want the system to supply a name for the constraint, you can omit the "CONSTRAINT constraintname" portion of the syntax.
Here is an example:

CREATE TABLE GROCERY_BAG
(BAG_NUMBER NUMBER(10) CONSTRAINT GB_PK PRIMARY KEY, 
PAPER_OR_PLASTIC_FLAG CHAR(5), ... )

The second technique uses a table constraint and is written at the end of the list of column specifications, just before the closing parenthesis.

CREATE TABLE [user.]table
(  column_name datatype [DEFAULT expression]
 [column constraint]
   , column ... 
   , [CONSTRAINT constraintname ] PRIMARY KEY 
 (column list))

Place the column or columns inside the parenthesis where you see "column list." Again, if you want the system to supply a name for the constraint, you can omit the "CONSTRAINT constraintname" portion of the syntax.
Here is another example:

CREATE TABLE GROCERY_STORE
(TERRITORY VARCHAR2(10), 
 STORE_NUMBER NUMBER(5), ...
CONSTRAINT GS_PK PRIMARY KEY 
(TERRITORY, STORE_NUMBER))

Defining primary key in ALTER TABLE

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

A table can have only one PRIMARY KEY constraint defined.

What is a constraint?

This lesson describes the PRIMARY KEY constraint. There are several other constraints available to you. Constraints that apply to one column are called COLUMN constraints. COLUMN constraints can tell the database to require data in the column (the NOT NULL constraint); or validate the value (the CHECK constraint).

Defining Unique key constraint

A unique key 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:

You cannot define FOREIGN KEY constraints that reference a UNIQUE KEY constraint.
You can define more than one UNIQUE KEY constraint on one table.

To define a UNIQUE KEY^[4] constraint, use exactly the same syntax as the PRIMARY KEY constraint, except substitute the word UNIQUE for the words PRIMARY KEY.

The next lesson covers other types of constraints you can define on a table.

[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] Primary key: One or more columns that define a unique row in a table. A table can contain only one primary key.

[3] Unique key: A second primary key in a table. A table can contain more than one unique key.

[4] Table constraint: Constraints that apply to more than one column.