Скачать или смотреть How to Properly Implement Foreign Key References in Oracle 11g

Learn how to create foreign key constraints in Oracle 11g to ensure data integrity in your FlightData and Airport tables.
---
This video is based on the question https://stackoverflow.com/q/65052838/ asked by the user 'user14658431' ( https://stackoverflow.com/u/14658431/ ) and on the answer https://stackoverflow.com/a/65052917/ provided by the user 'The Impaler' ( https://stackoverflow.com/u/6436191/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Foreign Key Reference

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Foreign Key References in Oracle 11g

When working with relational databases, ensuring data integrity is crucial. One common issue that arises is how to implement foreign key references between tables. In this post, we'll explore how to correctly set up foreign keys using the example of a FlightData table and an Airport table in Oracle 11g.

The Problem

In our setup, the FlightData table contains two columns: Depart_Code and Ariv_Code. These columns are intended to reference the Airport_Code in the Airport table, but initially, they lack the necessary constraints to maintain this relationship effectively. You might be wondering how to enforce such references to ensure that every flight's departure and arrival codes point to valid airports.

The Solution

Implementing foreign key references is simpler than it seems. We can break this process down into clear steps:

Step 1: Ensure Non-nullable Columns

To ensure that every entry in FlightData consistently refers to an existing airport, we need to make the Depart_Code and Ariv_Code columns NOT NULL. This restriction prevents any flight from being created without a specified departure or arrival airport.

Step 2: Match Data Types and Lengths

The Depart_Code and Ariv_Code columns must have the same data type and length as the Airport_Code in the Airport table. Since Airport_Code is defined as VARCHAR2(7), we will apply the same data type and length to our flight codes.

Step 3: Add Foreign Key Constraints

We will now introduce foreign key constraints for both Depart_Code and Ariv_Code, linking them to the Airport_Code in the Airport table. This ensures that values in these columns must correspond to valid entries in the airport table.

Example Implementation

Here’s how the FlightData table can be defined in SQL with the necessary changes:

[[See Video to Reveal this Text or Code Snippet]]

Summary of Changes

Data Type Matching: Both Depart_Code and Ariv_Code are set to VARCHAR2(7).

Not Null Constraint: Added NOT NULL to prevent empty values.

Foreign Key Constraints: Established constraints fk1 and fk2 to link both columns to the Airport_Code.

Conclusion

By following these steps, you will successfully enforce foreign key constraints in your Oracle 11g database. This not only maintains the integrity of your data but also helps prevent potential data anomalies.

If you’re new to databases or Oracle 11g, don’t hesitate to experiment with these concepts in your own projects. Setting up foreign key references might seem daunting at first, but with practice, you’ll find it becomes second nature!