Primary Key Constraints
A primary key constraint designates a column as the primary key of a table or view. A composite primary key designates a combination of columns as the primary key.
A primary key can be natural or a surrogate. A natural key is a meaningful identifier made of existing attributes in a table. For example, a natural key could be a postal code in a lookup table. In contrast, a surrogate key is a system-generated incrementing identifier that ensures uniqueness within a table. Typically, a sequence generates surrogate keys.
The Oracle Database implementation of the primary key constraint guarantees that the following statements are true:
• No two rows have duplicate values in the specified column or set of columns.
• The primary key columns do not allow nulls.
A PRIMARY KEY constraint creates a primary key for the table.
The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value.
In a PRIMARY KEY constraint, the values in the group of one or more columns subject to the constraint uniquely identify the row.
Each table can have one PRIMARY KEY, which in effect names the row and ensures that no duplicate rows exist.
When you create a primary key constraint:
• Oracle Database uses an existing index if it contains a unique set of values before enforcing the primary key constraint. The existing index can be defined as unique or nonunique. When a DML operation is performed, the primary key constraint is enforced using this existing index.
• If no existing index can be used, then Oracle Database generates a unique index.
When you drop a primary key constraint:
• If the primary key was created using an existing index, then the index is not dropped.
• If the primary key was created using a system-generated index, then the index is dropped.
Restrictions on Primary Key Constraints
Primary constraints are subject to the following restrictions:
• A table or view can have only one primary key.
• None of the columns in the primary key can be LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the primary key can contain a column of TIMESTAMP WITH LOCAL TIME
ZONE.
• The size of the primary key cannot exceed approximately one database block.
• A composite primary key cannot have more than 32 columns.
• You cannot designate the same column or combination of columns as both a primary key and a unique key.
A typical situation calling for a primary key is the numeric identifier for an employee. Each employee must have a unique ID. An employee must be described by one and only one row in the employees table.
The example in Unique Constraints indicates that an existing employee has the employee ID of 202, where the employee ID is the primary key. The following example shows an attempt to add an employee with the same employee ID and an employee with no ID:
SQL> INSERT INTO employees (employee_id, last_name, email, hire_date,
job_id)
1 VALUES (202,'Chan','JCHAN',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated
SQL> INSERT INTO employees (last_name) VALUES ('Chan');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")
The database enforces primary key constraints with an index. Usually, a primary key constraint created for a column implicitly creates a unique index and a NOT NULL constraint.
