Skip to main content

Documentation Index

Fetch the complete documentation index at: https://notes.kodekloud.com/llms.txt

Use this file to discover all available pages before exploring further.

In this article, we explore how to establish relationships within a relational database to connect different tables—in our case, a “users” table and a “posts” table. In many applications, such as social media platforms, a post is always associated with its creator. However, our current setup lacks this connection: users and posts are managed independently without any inherent link between a post and the user account that created it. When examining the posts table, you’ll notice columns such as ID, title, content, published status, and a created_at timestamp (not shown here). Yet, there is no column that indicates which user created a particular post.

​
Establishing Relationships with Foreign Keys

Relational databases excel at forming connections between tables. To associate each post with a specific user, we need to add a new column—commonly called “user_id”—to the posts table. This column will serve as a foreign key that references the ID column in the users table. Below is an example SQL statement illustrating how to alter the posts table to add this relationship: 
ALTER TABLE posts
ADD COLUMN user_id INTEGER,
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id)
REFERENCES users(id);
The foreign key constraint instructs SQL to link the “user_id” column in the posts table with the “id” column in the users table. By embedding the ID of the user who created the post, the database maintains a clear association between the two tables. For example, if a post with the ID 621 has a user_id of 212, you can look up the corresponding user in the users table and determine that “Clay@gmail.com” is the creator of the post.
While the primary key (ID) is typically used as the foreign key target, some advanced database designs might involve referencing columns other than the primary key, tailored to specific business logic and relationship requirements.

​
Visualizing the Relationship

The diagram below illustrates the one-to-many relationship between the “users” and “posts” tables. In this relationship, one user can create many posts, but each post is linked to only one user.
The image illustrates a one-to-many relationship between two database tables: "User" and "Posts." The "User" table contains user IDs, emails, and passwords, while the "Posts" table includes post IDs, user IDs, titles, content, and publication status.
When a new post is created, the creator’s user ID is embedded in the “user_id” column. For example, if another post shows a user_id of 378, referring to the users table will reveal that “Mike@gmail.com” created that post.

​
Next Steps

In the following section, we will demonstrate how to connect to a PostgreSQL database, create the necessary additional column, enforce the foreign key constraint, and work with these relationships practically. For further insights, check out: Happy coding!

Watch Video