Foreign Key FAQS

1. What is a foreign key in Oracle?

A foreign key is a column or a set of columns in one table that establishes a link to the primary key or unique key of another table. It is used to enforce referential integrity between the two tables, ensuring that the foreign key column(s) in the child table must correspond to a valid value in the parent table.

 

2. What is the purpose of using a foreign key?

The main purposes of foreign keys are:

• Referential Integrity: Ensures that relationships between tables remain valid, i.e., a foreign key in the child table must correspond to an existing record in the parent table.
• Data Consistency: Prevents data anomalies, such as orphaned rows in child tables.
• Enforcement of Business Logic: By using foreign keys, you enforce logical relationships between data entities (e.g., an order must reference an existing customer).

 

3. How do I define a foreign key in Oracle?

To define a foreign key in Oracle, you can use the FOREIGN KEY constraint when creating a table or altering an existing table.

Example:

CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR2(100),

    department_id INT,

    CONSTRAINT fk_department FOREIGN KEY (department_id)

    REFERENCES departments (department_id)

    ON DELETE CASCADE

);

This creates a foreign key fk_department in the employees table that references the department_id column in the departments table.

 

4. What is the difference between a primary key and a foreign key?

• Primary Key: A primary key is a column (or combination of columns) in a table that uniquely identifies each row in that table. It must be unique and NOT NULL.
• Foreign Key: A foreign key is a column (or set of columns) in a child table that refers to the primary key or unique key of another (parent) table. It enforces a relationship between the two tables.

In short, the primary key ensures uniqueness within a table, while the foreign key creates a reference between tables.

 

5. What are the possible actions when deleting or updating rows in the parent table?

When a referenced row in the parent table is deleted or updated, the foreign key can specify how the corresponding rows in the child table should be handled. The possible actions are:

• ON DELETE CASCADE: Automatically delete corresponding rows in the child table when a row in the parent table is deleted.
• ON DELETE SET NULL: Set the foreign key column in the child table to NULL when the referenced parent row is deleted (only if the column allows NULL values).
• ON DELETE RESTRICT: Prevent deletion of a row in the parent table if it is referenced by any row in the child table.
• ON DELETE NO ACTION: Similar to RESTRICT, but enforcement is done at the end of the transaction.
• ON UPDATE CASCADE: Automatically update the foreign key values in the child table when the primary key value in the parent table is updated.

 

6. Can I create a foreign key after a table has been created?

Yes, you can add a foreign key to an existing table using the ALTER TABLE statement.

Example:

ALTER TABLE employees

ADD CONSTRAINT fk_department FOREIGN KEY (department_id)

REFERENCES departments (department_id)

ON DELETE CASCADE;

This adds a foreign key fk_department to the employees table referencing the department_id column in the departments table.

 

7. Can a foreign key reference multiple columns?

Yes, a foreign key can reference multiple columns, which is known as a composite foreign key. This is often used when the parent table’s primary key consists of multiple columns.

Example:

CREATE TABLE order_items (

    order_id INT,

    item_id INT,

    quantity INT,

    PRIMARY KEY (order_id, item_id),

    CONSTRAINT fk_order FOREIGN KEY (order_id, item_id)

    REFERENCES orders (order_id, item_id)

);

In this example, the composite foreign key references the order_id and item_id columns in the orders table.

 

8. What happens if a foreign key constraint is violated?

If a foreign key constraint is violated (e.g., inserting a value into the child table that does not exist in the parent table), Oracle will raise an error:

• ORA-02291: integrity constraint violated - parent key not found: This error occurs if a value is inserted into the foreign key column in the child table, but that value doesn’t exist in the parent table.

 

9. Can I define a foreign key with NULL values?

Yes, a foreign key can reference NULL values in the child table. If a foreign key column is NULL, the database will not enforce any referential integrity for that particular row. It means that the row does not have any relationship to a row in the parent table.

 

10. What is a deferred foreign key constraint?

A deferred foreign key constraint allows you to defer the enforcement of the foreign key constraint until the commit time, rather than immediately after each DML operation (INSERT, UPDATE, DELETE). This can be useful in complex transactions where temporary violations of the constraint might occur.

Example:

ALTER TABLE employees

ADD CONSTRAINT fk_department FOREIGN KEY (department_id)

REFERENCES departments (department_id)

DEFERRABLE INITIALLY DEFERRED;

This allows foreign key checks to be postponed until the end of the transaction.

 

11. How do I delete or update rows in the parent table when there is a foreign key reference?

To delete or update a parent row when there are child rows referencing it, you can define cascading actions in the foreign key constraint, such as ON DELETE CASCADE or ON UPDATE CASCADE. This will automatically handle the related rows in the child table.

Example:

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    customer_id INT,

    CONSTRAINT fk_customer FOREIGN KEY (customer_id)

    REFERENCES customers (customer_id)

    ON DELETE CASCADE

);

In this example, deleting a customer will automatically delete all orders related to that customer.

 

12. Can foreign keys improve performance?

While foreign keys enforce data integrity and relationships, they can impact performance, especially in large tables with frequent DML operations. Foreign key checks can slow down insert, update, and delete operations due to the extra validation. However, properly indexing the foreign key columns can help improve performance.

 

13. How do I troubleshoot foreign key issues in Oracle?

To troubleshoot foreign key issues:

1. Check for ORA errors: Errors like ORA-02291 or ORA-02292 indicate issues with foreign key integrity.
2. Verify data consistency: Ensure that the child table values correspond to valid parent table values.
3. Check foreign key constraints: Use the USER_CONS_COLUMNS and USER_CONSTRAINTS views to examine the foreign key constraints on a table.

Example:

SELECT constraint_name, table_name

FROM user_constraints

WHERE constraint_type = 'R';

This query will show you all the foreign key constraints (type 'R') in your schema.

 

14. Can foreign keys be used in many-to-many relationships?

Yes, in many-to-many relationships, foreign keys are used in a junction table (also called an associative table) that references the primary keys of both related tables. This way, the relationship between the two tables can be maintained.

Example of a many-to-many relationship:

CREATE TABLE students (

    student_id INT PRIMARY KEY,

    student_name VARCHAR2(100)

);

 

CREATE TABLE courses (

    course_id INT PRIMARY KEY,

    course_name VARCHAR2(100)

);

 

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)

);