Скачать или смотреть Solving the ORA-02270 SQL Error: A Guide to Proper Foreign Key References in Oracle

Encountering the `ORA-02270` error while creating SQL tables? Discover how to properly define foreign keys to resolve this issue in Oracle.
---
This video is based on the question https://stackoverflow.com/q/66429895/ asked by the user 'Isaac' ( https://stackoverflow.com/u/11942479/ ) and on the answer https://stackoverflow.com/a/66429941/ provided by the user 'Joe Stefanelli' ( https://stackoverflow.com/u/341251/ ) 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: (Error) ORA-02270: no matching unique or primary key for this column-list

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.
---
Solving the ORA-02270 SQL Error: A Guide to Proper Foreign Key References in Oracle

When working with SQL databases, it's common to establish relationships between tables using foreign keys. However, you might encounter the error ORA-02270: no matching unique or primary key for this column-list while trying to create these relationships. This error can be particularly frustrating if you're not sure how to correct the issue. In this guide, we'll break down the problem and provide an efficient solution.

Understanding the Problem

The error message ORA-02270 indicates that there's a mismatch between the foreign key definitions and the referenced primary or unique keys in other tables. In your case, you attempted to create foreign key relationships referencing a primary key in the registration table, but the registration table was defined incorrectly in relation to the other tables—namely, class and alumn.

Your Initial Code

Here’s a look at the code causing the issue:

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

What Went Wrong?

The cod_class and number_rgm columns in the registration table were declared as primary keys. However, these columns should instead serve as foreign keys referencing the primary keys in their respective tables—class and alumn.

This logic inversion is what leads to the ORA-02270 error message, as Oracle cannot find the appropriate unique or primary keys to match your foreign key declarations.

The Solution: Correcting Table Definitions

To resolve this issue, you need to adjust your table definitions so that the foreign keys correctly reference the primary keys of other tables. Here’s a suggested refactor of your SQL code:

Revised SQL Code

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

Key Changes Made:

Class Table: The class table now properly defines cod_class as its primary key.

Alumn Table: The primary key for the alumn table is now number_rgm.

Registration Table: The foreign key constraints in the registration table have been switched to correctly reference the primary keys in class and alumn.

Conclusion

By ensuring that foreign keys correctly reference existing primary keys in their respective tables, you can effectively eliminate the ORA-02270 error during table creation. Properly understanding and establishing these relationships is key to successfully constructing a relational database in Oracle SQL.

If you continue to face challenges or have specific questions regarding SQL and database design, feel free to reach out for more guidance. Happy coding!