Sql get or create


I need to save users with some data that may be same for multiple users (for the example – country).

Let’s assume that I don’t know all the countries at the beginning, so I will need to add them to the DB when user is create.

I can define my schema like this: CREATE TABLE users(id int, country text)
but the country can be the same for most of the users (schema is not normalize) so I want to define my schema like this:

CREATE TABLE users (id int, countryId int)
CREATE TABLE country (id int, country text)

but then I have other problem:

When user is trying to register he supply country and not the id
so to create the user I need to read the country id and if not exists create a new country.

The obvious issue is that if I have two process they both can insert the same country twice.

To fix this I wanted to add a unique constraint to country column on country table.
I searched to see if there are solutions that maybe better then this and I was sure that I will find this solution either but all the solutions were different for example:

Why adding the unique constraint is bad solution?

Best Answer

As mustaccio points out, this is one valid use case for a unique constraint.

You also probably should be using a hybrid solution in your application where the country field is a list of what's already in the country table, with an option for "Other" that allows manual text entry, otherwise you'll end up with multiple people specifying the same country in different ways, dirtying your database, such as "USA", "U.S.A", "United States", "America", etc, which would defeat the purpose of normalizing your users table.