Скачать или смотреть Creating Tables with Foreign Keys in PostgreSQL

Disclaimer/Disclosure: Some of the content was synthetically produced using various Generative AI (artificial intelligence) tools; so, there may be inaccuracies or misleading information present in the video. Please consider this before relying on the content to make any decisions or take any actions etc. If you still have any concerns, please feel free to write them in a comment. Thank you.
---

Summary: Learn how to design and implement tables with foreign key constraints in PostgreSQL to establish relationships between entities within a relational database system.
---

When working with PostgreSQL, designing tables that incorporate foreign key constraints is crucial for establishing relationships between entities in a relational database. Foreign keys ensure referential integrity by enforcing relationships between tables. Let's delve into how you can create tables with foreign keys in PostgreSQL.

Step 1: Define the Parent Table

First, create the parent table that will contain the primary key referenced by the foreign key in another table. Here's an example:

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

In this example, the departments table has a primary key column department_id.

Step 2: Create the Child Table with Foreign Key

Next, create the child table that will include a foreign key referencing the primary key of the parent table. Use the REFERENCES keyword to establish this relationship:

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

In the employees table, department_id is a foreign key that references departments.department_id. This constraint ensures that every department_id in the employees table must exist in the departments table.

Step 3: Insert Data

Now, you can insert data into both tables while maintaining the integrity of the foreign key relationship:

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

Step 4: Verify Foreign Key Constraints

PostgreSQL will enforce the foreign key constraint, preventing actions that would violate referential integrity. For instance, you cannot insert a record into employees with a department_id that doesn't exist in departments.

Additional Notes

ON DELETE Actions: Specify actions to be taken on deletion of referenced rows. For example, ON DELETE CASCADE will delete all associated rows in the child table when a row in the parent table is deleted.

Using ALTER TABLE: If you want to add a foreign key constraint to an existing table, you can use ALTER TABLE.

By following these steps, you can effectively design and implement tables with foreign key constraints in PostgreSQL, ensuring a well-structured relational database model.