Sql Relationship Basics

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 "[email protected]" is the creator of the post.

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.

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 "[email protected]" 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:

• Kubernetes Documentation
• Docker Hub
• Terraform Registry

Happy coding!