The course Oracle SQL Extensions addresses primary keys. If you need a refresher, read through the following material.
What is a 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.
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:
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. Table constraints are used to define
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:
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.
A table can have only one PRIMARY KEY constraint defined.
Defining a 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 constraint, use exactly the same syntax as the PRIMARY KEY constraint, except substitute the word UNIQUE for the words PRIMARY KEY.
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.
Table constraint: Constraints that apply to more than one column.
Unique key: A second primary key in a table. A table can contain more than one unique key.