PL/SQL Foreign Key

Maintaining data integrity is essential in relational database management systems. One essential concept in ensuring data consistency is the use of foreign keys. In PL/SQL, a foreign key creates relationships between tables, ensuring that the data in one table corresponds to the data in another.

This article will explore the concept of foreign keys in Oracle PL/SQL, provide examples using the CREATE TABLE and ALTER TABLE statements, and explain their syntax, output,

What is a Foreign Key in Oracle?

• A foreign key is a referential constraint that defines a table's column or columns relating to the column or columns of another table’s primary key.
  
• The main use of the foreign key is to change the data in the second table to match that of the first table.
  
• Foreign keys ensure that values in at least one of the column of a given table mirrors the values in at least one of the primary key fields of another table.

Key Points about Foreign Keys:

• Referential Integrity: The concept that guarantees integrity in database systems because it makes sure that a record in one table matches a record in another table.
  
• Cascading Actions: Complements features such as ON DELETE CASCADE or ON UPDATE CASCADE, which adjust linking currents to their subordinate tiers.
  
• Preventing Invalid Data: Ensures data entry can only be made from valid entries that have been identified by referencing the primary key.

Use of Foreign Keys?

Foreign keys are essential for maintaining referential integrity in your database. Referential integrity ensures that relationships between tables remain consistent.

When a table has a foreign key to another table, the data in the foreign key column must correspond to existing values in the primary key column of the referenced table. This prevents orphaned records and ensures that the database accurately reflects real-world relationships.

Creating a Foreign Key in SQL

To create a foreign key in SQL, you must first define two tables: a parent table and a child table. The parent table contains the primary key, and the child table contains the foreign key.Let's consider a database schema for a simple library system with two tables: Authors and Books.

1. Creating and Inserting Data with Foreign Keys in Authors Table :

In this example, AuthorID in the Books table is the foreign key that references AuthorID in the Authors table. This ensures that every book in the Books table is associated with a valid author in the Authors table

Query:

CREATE TABLE Authors (
    AuthorID NUMBER PRIMARY KEY,
    AuthorName VARCHAR2(100)
);

INSERT INTO Authors (AuthorID, AuthorName) VALUES (1, 'J.K. Rowling'); 
INSERT INTO Authors (AuthorID, AuthorName) VALUES (2, 'George R.R. Martin');

Output:

2.Creating and Inserting Data with Foreign Keys in Books Table:

The CREATE TABLE query defines the Books table with a foreign key (AuthorID) that references the Authors table's AuthorID to ensure referential integrity. The INSERT queries then add two books to the Books table, each linked to an author by AuthorID. This setup ensures that each book must have a valid author in the Authors table.

Query:

CREATE TABLE Books (
    BookID NUMBER PRIMARY KEY,
    Title VARCHAR2(100),
    AuthorID NUMBER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

INSERT INTO Books (BookID, Title, AuthorID) VALUES (1, 'Harry Potter and the Philosopher''s Stone', 1);
 INSERT INTO Books (BookID, Title, AuthorID) VALUES (2, 'A Game of Thrones', 2);

Output:

Handling Foreign Key Violations

If you attempt to insert a record into the Books table with an AuthorID that does not exist in the Authors table, the database will reject the insert and raise an error. For example:

Queries:

INSERT INTO Books (BookID, Title, AuthorID) VALUES (3, 'Wings of Fire', 3);

Output:

Cascading Actions with Foreign Keys

You can define cascading actions with foreign keys to automatically handle these referential integrity issues. There are two common options:

1. ON DELETE CASCADE: When a record in the parent table is deleted, all corresponding records in the child table are automatically deleted.

Query:

CREATE TABLE Books (
    BookID NUMBER PRIMARY KEY,
    Title VARCHAR2(100),
    AuthorID NUMBER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE
);

Now, if you delete an author from the Authors table, all books written by that author in the Books table will be automatically deleted.

2. ON DELETE SET NULL: When a record in the parent table is deleted, the foreign key in the child table is set to NULL.

CREATE TABLE Books (
    BookID NUMBER PRIMARY KEY,
    Title VARCHAR2(100),
    AuthorID NUMBER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE SET NULL
);

Now, if you delete an author from the Authors table, the AuthorID for that author's books in the Books table will be set to NULL.

Example with ON DELETE CASCADE

DELETE FROM Authors WHERE AuthorID = 2;
SELECT * FROM Books;

Output:

The deletion of the author with AuthorID = 2 successfully removed any references to that author from the Books table, leaving only books associated with other authors. In this case, "Harry Potter and the Philosopher's Stone" by AuthorID = 1 remains.

Example with ON DELETE SET NULL

The ALTER TABLE queries modify the Books table to use ON DELETE SET NULL for its foreign key, so when an author is deleted, any books associated with that author will have their AuthorID set to NULL instead of being deleted. After deleting the author with AuthorID = 1, querying the Books table shows that the AuthorID for affected books is now NULL

Query:

-- Modifying the Books table with ON DELETE SET NULL
ALTER TABLE Books DROP CONSTRAINT SYS_C0027603;

ALTER TABLE Books
ADD CONSTRAINT fk_books_authors
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE SET NULL;

-- Deleting an author
DELETE FROM Authors WHERE AuthorID = 1;

-- Querying the Books table after deletion
SELECT * FROM Books;

Output:

The AuthorID for "Harry Potter and the Philosopher's Stone" is now NULL because the corresponding author was deleted.

Additional Examples

Example 1: Inserting Data with a Valid Foreign Key

Here are more scenarios to help illustrate the concept of foreign keys. Let's add a new book to the Books table where the AuthorID exists in the Authors table.

Query:

-- Inserting a new book with a valid AuthorID
INSERT INTO Books (BookID, Title, AuthorID) VALUES (3, 'Harry Potter and the Chamber of Secrets', 1);

-- Querying the Books table to verify the insertion
SELECT * FROM Books;

Output:

In this example we are inserting data with a valid foreign key, the new book "Harry Potter and the Chamber of Secrets" is successfully inserted because AuthorID = 1 exists in the Authors table.

Example 2: Deleting Data with ON DELETE CASCADE

Suppose the Books table was created with the ON DELETE CASCADE option. Let's delete an author and observe the cascading effect on the Books table.

Query:

-- Deleting an author with ON DELETE CASCADE in effect
DELETE FROM Authors WHERE AuthorID = 1

Output:

The author with AuthorID = 1 is deleted from the Authors table.
All books in the Books table that have AuthorID = 1 are also delete.

With ON DELETE CASCADE in effect, deleting the author with AuthorID = 1 from the Authors table will automatically delete all books in the Books table that have AuthorID = 1. The result is that both the author and all their related books are removed from the database.

Conclusion

In PL/SQL, foreign keys are fundamental in enforcing referential integrity between related tables in a relational database. By defining foreign keys, you ensure that the relationships between tables remain consistent and that any changes to data in one table are accurately reflected in the related tables.

This not only prevents orphaned records and invalid data entries but also ensures that your database maintains a high level of data integrity and reliability, which is crucial for accurate data representation and dependable database operations.