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

What are PRIMARY KEY and UNIQUE Constraints

Ensures Data Integrity: A primary key constraint guarantees that each row in a table has a unique identifier. This helps in preventing duplicate entries and ensures data integrity.
Faster Data Retrieval: With a primary key constraint, Oracle can use an index to quickly locate a specific row based on its primary key value. This significantly improves the performance of queries that use the primary key as a search condition.
Referential Integrity: Primary key constraints play a crucial role in enforcing referential integrity between tables. When a foreign key in one table references a primary key in another table, the primary key constraint ensures that the referenced row exists, maintaining data consistency.
Data Validation: Primary key constraints can be used to validate data entered into a table. By defining a primary key constraint, you can specify the data type, format, and other validation rules for the primary key column. This helps in preventing invalid or incorrect data from being inserted into the table.
Database Normalization: Primary key constraints are essential for achieving database normalization. Normalization involves organizing data into multiple related tables to eliminate data redundancy and ensure data integrity. Primary keys are used to establish relationships between tables and ensure that each table has a unique identifier.
Data Manipulation Performance: Primary key constraints can improve the performance of data manipulation operations like inserts, updates, and deletes. By using a primary key, Oracle can quickly identify the affected row and make the necessary changes efficiently.
Partitioning and Clustering: Primary keys can be used to partition tables, allowing for better data management and scalability. Additionally, primary key columns can be used as clustering keys to improve data locality and query performance.
Data Security: Primary key constraints can be used to enforce data security by restricting access to specific rows or groups of rows based on the primary key values. This helps in protecting sensitive data from unauthorized access.

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.

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.