Скачать или смотреть Understanding Oracle SQL Foreign Key Constraints in the Same Table

Learn how to set a foreign key constraint in Oracle SQL that references the same table. Discover best practices and examples to enhance your database design skills.
---
This video is based on the question https://stackoverflow.com/q/77214507/ asked by the user 'Grandmarkkk' ( https://stackoverflow.com/u/15341753/ ) and on the answer https://stackoverflow.com/a/77214617/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) 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: Oracle SQL foreign key from the same table

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 Oracle SQL Foreign Key Constraints in the Same Table

When working with relational databases, you may encounter situations where you want to set a foreign key constraint that references the same table instead of another one. This particular scenario can be confusing and lead to errors if not approached correctly. In this guide, we will breakdown how you can effectively set a foreign key constraint against the same table in Oracle SQL and clarify why this is important.

The Challenge

You might ask yourself, “How can I set a foreign key constraint if the attribute to be set to a foreign key is from the current table?”

In Oracle SQL, when you attempt to create a foreign key constraint and get an error like:

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

this typically indicates that you are trying to reference a column that is not defined as either a primary key or a unique key.

Defining Foreign Keys in a Single Table

To illustrate this, let’s take a common example: the employees table. Here’s how the structure of this table looks:

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

Key Components of the Table

Primary Key (emp_id): This column represents the unique identifier for each employee.

Foreign Key (mgr_id): This column is meant to hold the manager's ID, which should correspond to an existing employee’s emp_id.

It's important to note that a manager must be one of the employees to maintain referential integrity.

Setting the Foreign Key Constraint

To establish the foreign key relationship where mgr_id references the emp_id, you would execute:

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

Running this command tells the database that mgr_id is linked to emp_id, meaning the manager must be an existing employee.

Handling Errors from Incorrect Foreign Key References

Oracle SQL requires that the foreign key must reference a unique or primary key in the table. Therefore, it won’t let you create a foreign key that doesn’t point to either:

A primary key.

A unique key.

Can You Reference the Same Column?

You may also wonder if it’s possible to create a foreign key that references the same column. The answer is yes, as long as that column is constrained as unique or primary key.

Example:

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

You can then define the foreign key on the same column:

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

This creates a self-referential foreign key where an employee can reference themselves, although this use case might be rare in practical applications.

Conclusion

Setting up foreign key constraints referencing the same table in Oracle SQL adds layers of data integrity to your databases. By allowing relationships within the same entity, you can model real-world scenarios more accurately.

Summary Points

Always ensure foreign key references a primary or unique key.

Use proper syntax to link foreign keys to primary keys.

Self-referential keys are valid and can be useful in specific situations.

Now that you know how to implement foreign key constraints referencing the same table, consider how this knowledge can be applied in designing elegant database schemas.