Foreign Key

In Oracle, foreign keys are a key element of relational database design, helping to establish relationships between tables. A foreign key in one table points to the primary key or a unique key in another table, creating a logical link between the two tables. This relationship helps ensure the integrity and consistency of data across tables.

Here’s a detailed breakdown of foreign keys in Oracle:

1. Definition of a Foreign Key

A foreign key is a column or a set of columns in a table that is used to establish a link between the data in two tables. It is a constraint that ensures the values in the foreign key column(s) match those in the referenced table’s primary key or unique key.

For example:

• Primary Table: departments (with department_id as the primary key)
• Foreign Table: employees (with department_id as a foreign key)

2. Foreign Key Syntax

To define a foreign key constraint in Oracle, you use the FOREIGN KEY constraint.

Here’s the basic syntax to define a foreign key:

CREATE TABLE child_table (

  column1 datatype,

  column2 datatype,

  ...

  CONSTRAINT fk_name FOREIGN KEY (foreign_column)

  REFERENCES parent_table (primary_column)

  ON DELETE CASCADE -- or other actions (optional)

);

• foreign_column: The column(s) in the child table that refers to the parent table’s primary or unique key.
• parent_table: The table that contains the primary or unique key.
• primary_column: The column in the parent table that the foreign key references.
• CASCADE, SET NULL, RESTRICT, NO ACTION: These are actions that dictate what happens to child rows when the parent row is deleted or updated.

3. Types of Foreign Key Relationships

• One-to-Many Relationship: Most common, where a row in the parent table can relate to many rows in the child table. For example, one department can have many employees.
• Many-to-Many Relationship: This is achieved by using a junction (or associative) table that contains foreign keys to both parent tables.

4. Actions on Delete or Update

When you define a foreign key, you can also specify how the database should behave when a referenced row in the parent table is updated or deleted. Oracle provides several options:

• CASCADE: When a referenced row is deleted or updated, the corresponding rows in the child table will automatically be deleted or updated.

·        ON DELETE CASCADE

• SET NULL: When a referenced row is deleted or updated, the foreign key column in the child table is set to NULL (provided the foreign key column allows nulls).

·        ON DELETE SET NULL

• RESTRICT: Prevents the deletion or update of a referenced row if any rows in the child table reference it. This is the default behavior if no action is specified.

·        ON DELETE RESTRICT

• NO ACTION: This is similar to RESTRICT, but it checks constraints after the SQL statement is executed. Typically, it prevents actions on the parent table if there are corresponding child rows.

·        ON DELETE NO ACTION

5. Referential Integrity

The primary purpose of a foreign key is to enforce referential integrity, which ensures that the relationships between tables remain valid. The key aspects of referential integrity are:

• No Orphan Records: You cannot have a foreign key in the child table that doesn’t exist in the parent table.
• Validating Data Consistency: Any changes in the parent table (insert, update, delete) are propagated in a controlled manner to maintain data consistency.

6. Foreign Key Constraints

• UNIQUE and PRIMARY KEY Constraints: A foreign key can only reference a column or a set of columns that are defined as a primary key or unique in the parent table.
• Deferring Constraint Enforcement: You can set the foreign key constraint to be deferred until the end of the transaction, meaning Oracle will enforce the constraint at the commit time rather than after every modification. This is useful in cases where temporary violations might occur during the transaction.

·        ALTER TABLE child_table ADD CONSTRAINT fk_name

·        FOREIGN KEY (column)

·        REFERENCES parent_table (column)

·        DEFERRABLE INITIALLY DEFERRED;

7. Cascading Operations

Cascading allows you to manage the lifecycle of related records:

• ON DELETE CASCADE: If you delete a row from the parent table, all corresponding rows in the child table will be deleted. For example, if you delete a department, all employees associated with that department are automatically removed.
• ON UPDATE CASCADE: If you update the value of the primary key in the parent table, the foreign key values in the child table are automatically updated.

Example:

CREATE TABLE orders (

  order_id INT PRIMARY KEY,

  customer_id INT,

  CONSTRAINT fk_customer FOREIGN KEY (customer_id)

  REFERENCES customers (customer_id)

  ON UPDATE CASCADE

);

8. Creating Foreign Keys After Table Creation

You can also add a foreign key constraint to an existing table using the ALTER TABLE statement.

ALTER TABLE child_table

ADD CONSTRAINT fk_name FOREIGN KEY (foreign_column)

REFERENCES parent_table (primary_column)

ON DELETE CASCADE;

9. Composite Foreign Key

A composite foreign key consists of two or more columns that together form the foreign key. This is common in junction tables (many-to-many relationships).

Example:

CREATE TABLE student_courses (

  student_id INT,

  course_id INT,

  PRIMARY KEY (student_id, course_id),

  CONSTRAINT fk_student FOREIGN KEY (student_id)

  REFERENCES students (student_id),

  CONSTRAINT fk_course FOREIGN KEY (course_id)

  REFERENCES courses (course_id)

);

10. Foreign Key Constraints with NULLs

Foreign keys can reference columns that allow NULL values. If a child record’s foreign key is NULL, it means there is no relationship between that child record and any record in the parent table. In this case, referential integrity isn’t enforced on the NULL foreign key value.

11. Common Errors

• ORA-02291: Foreign key constraint violated – this error occurs when you try to insert a value in the child table that does not exist in the parent table.
• ORA-02292: Integrity constraint violated – this error occurs when you try to delete or update a parent row that is referenced by a foreign key in the child table and the foreign key constraint is RESTRICT or NO ACTION.

12. Performance Considerations

Foreign key constraints are useful for maintaining data integrity but can sometimes affect performance, especially with large tables. Here are a few performance tips:

• Indexing: Ensure foreign key columns are indexed, as this can significantly improve performance when inserting, updating, or deleting data.
• Batch Operations: When inserting large datasets, it might be efficient to temporarily disable foreign key constraints, but this should be done carefully, ensuring that the data integrity is not compromised.

 

Example Use Cases

Case 1: One-to-Many Relationship

CREATE TABLE departments (

  department_id INT PRIMARY KEY,

  department_name VARCHAR2(50)

);

 

CREATE TABLE employees (

  employee_id INT PRIMARY KEY,

  employee_name VARCHAR2(50),

  department_id INT,

  CONSTRAINT fk_department FOREIGN KEY (department_id)

  REFERENCES departments (department_id)

  ON DELETE CASCADE

);

Case 2: Many-to-Many Relationship (Using a Junction Table)

CREATE TABLE students (

  student_id INT PRIMARY KEY,

  student_name VARCHAR2(50)

);

 

CREATE TABLE courses (

  course_id INT PRIMARY KEY,

  course_name VARCHAR2(50)

);

 

CREATE TABLE student_courses (

  student_id INT,

  course_id INT,

  PRIMARY KEY (student_id, course_id),

  CONSTRAINT fk_student FOREIGN KEY (student_id)

  REFERENCES students (student_id),

  CONSTRAINT fk_course FOREIGN KEY (course_id)

  REFERENCES courses (course_id)

);