PRIMARY KEY

The PRIMARY KEY constraint in Oracle is used to uniquely identify each record in a database table. It is one of the most important constraints used to ensure data integrity and uniqueness in a table. The PRIMARY KEY consists of one or more columns in a table, where the combination of values in these columns must be unique across all rows.

Here’s a detailed explanation of the PRIMARY KEY constraint in Oracle:

 

1. What is a PRIMARY KEY?

The PRIMARY KEY constraint is used to uniquely identify each row in a database table. It ensures that:

• The values in the column(s) defined as the primary key are unique.
• No column in the primary key can have NULL values.

It is crucial for maintaining data integrity and defining relationships between tables, especially when used with foreign keys.

 

2. Key Characteristics of PRIMARY KEY

• Uniqueness: The values in the column(s) with a primary key constraint must be unique across all rows in the table.
• Not NULL: A PRIMARY KEY cannot contain NULL values. Every row must have a valid, unique value in the primary key column(s).
• Single or Composite: The primary key can consist of a single column or multiple columns (composite primary key). If it's made of multiple columns, the combination of values in those columns must be unique across rows.
• Single Primary Key per Table: A table can only have one PRIMARY KEY constraint.
• Automatic Index: When a primary key is defined, Oracle automatically creates a unique index to enforce uniqueness, which also helps in fast searches.

 

3. Syntax to Define a PRIMARY KEY

Defining PRIMARY KEY during Table Creation

You can define a PRIMARY KEY constraint during the table creation using the following syntax:

• Single Column Primary Key:

CREATE TABLE employees (

    employee_id NUMBER,

    name VARCHAR2(100),

    CONSTRAINT pk_employee_id PRIMARY KEY (employee_id)

);

• Composite Primary Key (Multiple Columns):

CREATE TABLE orders (

    order_id NUMBER,

    customer_id NUMBER,

    order_date DATE,

    CONSTRAINT pk_orders PRIMARY KEY (order_id, customer_id)

);

Defining PRIMARY KEY after Table Creation (Using ALTER TABLE)

You can also add a primary key constraint to an existing table:

ALTER TABLE employees

ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

 

4. PRIMARY KEY Constraint and NULL Values

• No NULL Values: The primary key column cannot have NULL values because the key is supposed to uniquely identify each record.
• If you try to insert a NULL value into a column that is part of a primary key, Oracle will raise an error.
• NULL Values Not Allowed in Composite Primary Key: If you define a primary key on multiple columns (a composite primary key), the combination of the values in all columns must be unique. Also, none of the columns in the composite key can have NULL values.

 

5. Indexing with PRIMARY KEY

When a PRIMARY KEY is defined on a column, Oracle automatically creates a unique index on that column (or combination of columns). This index is used to enforce the uniqueness constraint and improve the performance of search queries.

• Performance Benefit: The index speeds up lookups, inserts, and updates by providing fast access to records based on the primary key.
• Automatic Index: You don’t need to manually create an index on a primary key column; Oracle handles this automatically.

 

6. Dropping a PRIMARY KEY

To remove a PRIMARY KEY constraint from a table, you can use the following ALTER TABLE statement:

ALTER TABLE employees

DROP CONSTRAINT pk_employee_id;

This will remove the primary key constraint and the associated unique index created by it.

 

7. PRIMARY KEY and Foreign Key Relationships

The PRIMARY KEY is often used in conjunction with foreign keys to define relationships between tables:

• Foreign Key: A foreign key in one table references the primary key of another table, ensuring referential integrity.
• Enforcing Referential Integrity: The foreign key ensures that the value in the foreign key column matches a value in the primary key column of the referenced table.

Example:

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    name VARCHAR2(100)

);

 

CREATE TABLE orders (

    order_id NUMBER PRIMARY KEY,

    employee_id NUMBER,

    CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees (employee_id)

);

In this case, employee_id in the orders table is a foreign key that references the primary key employee_id in the employees table.

 

8. Differences Between PRIMARY KEY and UNIQUE Constraint

Although both the PRIMARY KEY and UNIQUE constraints enforce uniqueness, they have important differences:

• NULL Values:
• PRIMARY KEY: Does not allow NULL values.
• UNIQUE: Allows multiple NULL values.
• Number of Constraints:
• A table can have only one PRIMARY KEY constraint.
• A table can have multiple UNIQUE constraints.
• Implicit Index:
• Both the PRIMARY KEY and UNIQUE constraints automatically create an index, but the primary key enforces both uniqueness and non-nullability.

 

9. Example Use Cases of PRIMARY KEY

• Employee Table: Ensuring that each employee has a unique identifier (e.g., employee_id) in the employees table.

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50)

);

• Order Table: Ensuring that each order has a unique order ID, and using a composite primary key to uniquely identify the combination of order_id and customer_id.

CREATE TABLE orders (

    order_id NUMBER,

    customer_id NUMBER,

    order_date DATE,

    CONSTRAINT pk_orders PRIMARY KEY (order_id, customer_id)

);

• Product Table: Ensuring that product codes are unique within a products table.

CREATE TABLE products (

    product_code VARCHAR2(10) PRIMARY KEY,

    product_name VARCHAR2(100)

);

 

10. Performance Considerations

• Indexing: The unique index created for the primary key improves the performance of queries that filter based on the primary key column(s).
• Inserts and Updates: While indexing improves query performance, it can slightly degrade the performance of INSERT, UPDATE, and DELETE operations because the index must also be updated.
• Storage: The automatic creation of the index will require additional storage space, especially for tables with large datasets.

 

11. Best Practices for Using PRIMARY KEY

• Choose a Meaningful Primary Key: When choosing a column to be the primary key, it’s a good practice to use a field that inherently has a unique value, such as a product code or employee number, instead of a value that may change over time.
• Avoid Large Primary Key: Avoid using large data types for the primary key, such as large text fields. Using smaller, more efficient data types like integers (e.g., NUMBER or INTEGER) for primary keys is recommended.
• Composite Primary Key: Use a composite primary key when necessary, such as when two or more columns together uniquely identify a record (e.g., order ID + customer ID in an orders table). However, keep the number of columns in a composite key minimal for efficiency.

 

12. Troubleshooting PRIMARY KEY Issues

• Violation of Uniqueness: If you attempt to insert a duplicate value into a column with a PRIMARY KEY constraint, Oracle will raise an error indicating that the value violates the uniqueness constraint.
• Null Values in Primary Key: If you try to insert a row with a NULL value in the column(s) defined as part of the PRIMARY KEY, Oracle will raise an error.
• Error: ORA-00001: This error indicates a violation of a unique constraint, such as trying to insert a duplicate value in a PRIMARY KEY column.

 

Conclusion

The PRIMARY KEY constraint is essential in ensuring data integrity in a table by enforcing uniqueness and non-nullability on one or more columns. It plays a critical role in defining relationships between tables using foreign keys and helps maintain consistency across a database.

It is vital to choose primary keys wisely and understand their implications on performance and storage, as well as how they affect relationships between tables.