Sql— inserting only unique values in a UNIQUE table

SQL— inserting only unique values in a UNIQUE table

Imagine we are a restaurant that sells all kinds of dishes. Because we’re smart we keep track of all of our ingredients in a database. The ingredients table will contain only unique ingredients that each have their own id.

The goal of this article is to create a query that inserts unique ingredients in to the table that are not already in the table. Other tables can refer to the ingredient_id so that we can filter and join on integers, which is a lot faster in large datasets.

1. Creating the table

First we’ll create the table that’ll contain the ingredients. This article uses Postgres but the same technique applies to all relational databases. The ingredients table:

As you can see we have two columns:

• Id. Since this id is a serial primary key it auto increments on insertion. We don’t have to pass this id to the table, the table creates it for us
• ingredient. Ingredient names. This column has a uniqueconstraint; this means that it can only contain unique values.

2 Inserting ingredients

The next step is to write some code that will only insert new ingredients into the ingredients table:

Let’s walk through this code:

1. First we define the data that we want to input, this happens in the WITH clause. Think of this as a table called inputvalues with the column ingredient. We put 6 ingredients in inputvalues.
2. We are going to INSERT the inputvalues into the public.ingredients table
3. We’ll filter the insertion WHERE the ingredient in the inputvalues does not exist in the public.ingredients yet.

Executing this query will output the following table:

Let’s try the query out with a curry:

After executing, Postgres says INSERT 0 2 which is good news! This means we only inserted two records into the table, even though we passed six. We only inserted the two ingredients that aren’t already in the table: paksoy and rice. Let’s check out the content of the table to see that it works:

3. Save insertion

Using the query above we can only insert unique ingredients. If we try to insert an ingredient that’s already in Postgres gives us back an error.

INSERT INTO public.ingredients (ingredient) VALUES (‘paprika’);

will produce the following error:

ERROR: duplicate key value violates unique constraint “ingredients_ingredient_key” DETAIL: Key (ingredient)=(paprika) already exists. SQL state: 23505

So even if we forget to use our awesome new query, the unique constraint on our table prevents duplicate values. Awesome!

Conclusion

With this article, I hope to have shed some light on how to handle unique values and using the unique constraint. If you have suggestions/clarifications please comment so I can improve this article. In the meantime, check out my other articles on all kinds of programming-related topics like these:

• DELETE INTO another table
• UPDATE INTO another table
• Insert, delete and update in ONE statement
• UPDATE SELECT a batch of records
• Save upserting

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!