Скачать или смотреть Understanding Foreign Key Constraints with Composite Keys in PostgreSQL

A comprehensive guide on why you can't add a foreign key referencing a composite primary key in PostgreSQL and how to handle it effectively.
---
This video is based on the question https://stackoverflow.com/q/62796424/ asked by the user 'codex' ( https://stackoverflow.com/u/7151999/ ) and on the answer https://stackoverflow.com/a/62796762/ provided by the user 'Bergi' ( https://stackoverflow.com/u/1048572/ ) 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: Why can't I add a foreign key that references from a composite key from the parent 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 Foreign Key Constraints with Composite Keys in PostgreSQL

When working with relational databases like PostgreSQL, understanding how to manage relationships between tables is crucial. A common challenge arises when you attempt to add a foreign key reference from a composite key in a parent table. In this guide, we will explore this issue and provide clear solutions to effectively design your database schema.

The Problem

Imagine you have a table named restaurantProduct set up with a composite primary key consisting of two columns: restaurantId and productId. This setup allows for the storage of products offered by various restaurants while avoiding duplicate entries for the same restaurant-product combination.

Here is the SQL code for creating this table:

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

Now, you want to create another table called custOrders that references restaurantProduct.restaurantId as a foreign key. However, when you attempt this, you encounter the error message:

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

This error message indicates a fundamental misunderstanding of how composite keys work in PostgreSQL.

Understanding Composite Keys

A primary key in a database table uniquely identifies each record in that table. When you use a composite primary key like in restaurantProduct, the combination of both restaurantId and productId serves as the unique identifier. Notably:

The restaurantId column is not a standalone primary key. Thus, multiple entries with the same restaurantId are permitted as long as the productId differs.

Therefore, referencing restaurantProduct.restaurantId alone is problematic since it can have duplicate values across different products in the table.

The Solution

To successfully create a foreign key relationship from the custOrders table to the restaurantProduct table, the following solutions are available:

1. Reference restaurantId from restaurant Table

Instead of trying to reference the composite key directly, you can simply reference the restaurantId from the restaurant parent table. This approach maintains a logical relationship while circumventing the issue with duplicates in the restaurantProduct table.

2. Use Composite Foreign Keys

If you need your custOrders table to reference a specific product from a restaurant, then you need to create a foreign key that includes both restaurantId and productId. Here’s how you could structure your custOrders table:

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

Key Takeaways

Understand composite keys: They consist of more than one column, which together act as a unique identifier. You cannot reference just one column if it’s not unique on its own.

For foreign keys referencing composite primary keys, you need to have all parts of the composite key in your referencing table.

Always ensure that foreign key constraints correspond precisely with the structure of your primary keys.

Conclusion

Creating effective and functional database relationships is essential for maintaining data integrity and ease of access. Understanding how to work with composite keys is pivotal in this process. By implementing the solutions discussed, you can help ensure that your database schema is both efficient and logical.

If you have any questions or comments about managing foreign key relationships within PostgreSQL, feel free to reach out!