In Postgresql, force unique on combination of two columns

How to Force Unique Combination of Two Columns in PostgreSQL

Are you facing the challenge of ensuring that two columns in your PostgreSQL database must have a unique combination of values? Don't worry, we've got you covered! In this blog post, we will walk you through the common issues surrounding this problem and provide easy solutions to implement in your database schema.

The Problem

Suppose you have a requirement where you need to create a table in PostgreSQL with two columns, let's say col1 and col2. You want to ensure that the combination of values in these two columns is unique. In other words, if there is already a row with the values [1, 1], you should not be able to insert another row with the same values. However, it should be possible to have multiple rows with the same value in either col1 or col2, as long as the combination is unique.

The Solution

To enforce this unique combination, we can make use of PostgreSQL's UNIQUE constraint. We will create a composite key consisting of both col1 and col2. Here's how you can do it:

CREATE TABLE someTable (
    id SERIAL PRIMARY KEY,
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    CONSTRAINT unique_combination UNIQUE (col1, col2)
);

Let's break down the solution step by step:

1. We start by creating a table called someTable with columns id, col1, and col2.

2. The id column is defined with the SERIAL data type as the primary key. This will automatically assign a unique value to each row.

3. The col1 and col2 columns are defined with the INT data type, and the NOT NULL constraint ensures that they must have a value in every row.

4. Finally, we define a UNIQUE constraint named unique_combination that includes both col1 and col2. This will enforce the uniqueness of the combination of values in these two columns.

How it Works

When you try to insert a new row into the someTable table, PostgreSQL will automatically check if the combination of col1 and col2 already exists. If it does, the insert operation will fail, and an error will be thrown. This mechanism ensures the uniqueness of the combination and prevents the insertion of duplicate rows.

Let's take a look at a few examples to illustrate this:

Allowed Inserts:

1 1
1 2
2 1
2 2

Rejected Insert:

1 1
1 2
1 1 -- This insert is rejected for violating constraints

As you can see, the first three rows in the rejected insert example are the same as the allowed inserts. However, the fourth row violates the unique combination constraint and is rejected.

Call-to-Action

Now that you know how to force a unique combination of two columns in PostgreSQL, go ahead and give it a try in your own database schema! If you have any questions or faced any difficulties during the implementation, feel free to leave a comment below. We'd love to help you out!

Also, don't forget to share this blog post with your fellow developers who might find it useful. Happy coding! 👩‍💻👨‍💻