Oracle

Chapter 8 - RDBMS Concepts

Foreign Key

In Oracle RDBMS, a FOREIGN KEY is a crucial concept used to maintain referential integrity between tables. It is a column or a set of columns in one table that uniquely identifies rows in another table. The FOREIGN KEY ensures that the values in this column or columns match values in the referenced table, thereby establishing a relationship between the tables.

Here’s a beginner-friendly guide to understanding and using FOREIGN KEYS:

Definition and Purpose
- A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
- It enforces referential integrity by ensuring that the value in the FOREIGN KEY column exists in the referenced table.
- This helps in maintaining consistent data across related tables.
Creating a FOREIGN KEY
- When defining a FOREIGN KEY, you specify the column(s) in the child table that will reference the PRIMARY KEY in the parent table.
- Example:
```
CREATE TABLE books (
    book_id NUMBER PRIMARY KEY,
    title VARCHAR2(100)
);

CREATE TABLE rentals (
    rental_id NUMBER PRIMARY KEY,
    book_id NUMBER,
    rental_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);
```
  - books table has a PRIMARY KEY book_id.
  - rentals table includes a FOREIGN KEY book_id that references the book_id in the books table.
Foreign Key Constraints
- Constraints help maintain the integrity of the data. If an attempt is made to insert a value into the FOREIGN KEY column that does not exist in the referenced table, Oracle will raise an error.
- You can define additional rules such as ON DELETE CASCADE which automatically removes rows in the child table when the corresponding row in the parent table is deleted.
Modifying FOREIGN KEY Constraints
- You can add or modify a FOREIGN KEY constraint using the ALTER TABLE statement.
- Example:
```
ALTER TABLE rentals
ADD CONSTRAINT fk_book
FOREIGN KEY (book_id)
REFERENCES books(book_id)
ON DELETE CASCADE;
```
Dropping a FOREIGN KEY Constraint
- To remove a FOREIGN KEY constraint, you use the ALTER TABLE statement with the DROP CONSTRAINT clause.
- Example:
```
ALTER TABLE rentals
DROP CONSTRAINT fk_book;
```

By understanding and using FOREIGN KEYS effectively, you can ensure that your relational database maintains data integrity and consistency across different tables.

Understanding Foreign Keys in Database Design

A foreign key in database design is a column (or set of columns) in one table that uniquely identifies a row of another table. It's a key used to link two tables together. This concept might seem a bit abstract, so let's use an analogy to simplify it, followed by a basic code example to illustrate how it works in practice.

Analogy: Cities and Countries

Imagine a world map with countries and their cities. Each country can have multiple cities. To represent this relationship in a database, you would have two tables: aCountriestable and aCitiestable.

TheCountriestable has a primary key calledCountryIDthat uniquely identifies each country.
TheCities table lists cities around the world, and each city is associated with a country.

In this analogy, theCountryIDfield in theCities table acts as a foreign key. It references theCountryID primary key in theCountries table. This setup ensures that each city in theCities table is linked to a specific country in theCountries table. Just as you would use a country's name to find its cities on a map, in a database, you use the foreign key to retrieve all cities belonging to a particular country.

Code Example

Let's translate this analogy into a simple SQL code example to demonstrate how foreign keys work in practice.

SQL Table Creation

-- Create Countries table
CREATE TABLE Countries (
    CountryID int NOT NULL,
    CountryName varchar(255) NOT NULL,
    PRIMARY KEY (CountryID)
);

-- Create Cities table
CREATE TABLE Cities (
    CityID int NOT NULL,
    CityName varchar(255) NOT NULL,
    CountryID int,
    PRIMARY KEY (CityID),
    FOREIGN KEY (CountryID) REFERENCES
Countries(CountryID)
);

In this example:

TheCountriestable is created withCountryIDas its primary key.
TheCitiestable is created withCityIDas its primary key andCountryIDas a foreign key.
TheFOREIGN KEY (CountryID) REFERENCES Countries(CountryID)line in theCitiestable definition establishesCountryIDas a foreign key that references theCountryIDprimary key in theCountriestable.

This setup allows the database to understand the relationship between countries and their cities. For instance, if you have a country withCountryID = 1, and you want to add cities to this country in theCitiestable, you would setCountryID = 1for these cities. The database then knows these cities belong to the country withCountryID = 1.

Conclusion

Foreign keys serve as crucial links between tables in relational databases, enabling the representation of real-world relationships like the one between countries and cities. They ensure data integrity by only allowing the insertion of records that have a corresponding value in the linked table. Through foreign keys, databases can maintain accurate and consistent relationships between data points across different tables.