Adding constraints in the MySQL database to guarantee uniqueness

Question

My SQL table has the following DDL

CREATE TABLE `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `family_id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
  )

I want to hold the family names in this simple table. In order to do this I have a microservice where the caller sends via JSON the family details:

{
  "family_id" : 1,
  "names": ["name1", "name2"]
}

The id is generated via auto increment from MySQL.

So the above JSON will finally trigger two insert statements:

insert (family_id, name) values ( 1, name1)
insert (family_id, name) values ( 1, name2)

The problem arises when a new request comes with a family_id that exists in the table. This should not be allowed, and I am doing a query in order to search if the family_id exists or not. If it exists an exception is raised. How can I avoid this query? The table schema can be altered if needed. Would it be OK if a could add something like a "request id", or a guid to establish uniqueness per request?

All data should be on the same table.

Below an example of the table with some data

(from Comment) I cannot create a second table. Eveything should be kept in one table.

You wrote you can't split this into two tables. Can you use triggers? There's a neat solution for this. — N.B.
– N.B., Commented Jul 11, 2018 at 15:39

johannespartin · Accepted Answer · 2018-07-03 09:17:46Z

6

You should normalize your schema and use two tables.

Family and (I assume) Person. Then you can use a UNIQUE constraint for the family_id and add the family_id as foreign key into the Person table.

answered Jul 3, 2018 at 9:17

johannespartin

5113 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

cateof Over a year ago

I cannot create a second table. Eveything should be kept in one table. Sorry I have to mention this on the question. However I got your point.

Shadow Over a year ago

@cateof in this case you cannot use a traditional constraint and cannot avoid doing a check before the insert if the family id already exists.

Shadow Over a year ago

@cateof also you mention that the family id is generated by autoincrement within MySQL. This means that you effectively have the family table that johannesp suggested - this is the table that generates the auto increment. Also, if the request uses autoincrement generated by MySQL, how come that you can have another request with the same family id value?

cateof Over a year ago

@Shadow the family id is not auto generated.

johannespartin Over a year ago

@cateof You should definitely normalize it into two tables. If not you are just working around symptoms of bad database design.

|

Rick James · Accepted Answer · 2018-07-17 17:57:47Z

3

+25

You need two tables.

CREATE TABLE Families (
    family_id MEDIUMINT UNSIGNED  AUTO_INCREMENT,
    ...
    PRIMARY KEY(family_id)
    );

CREATE TABLE FamilyNames (
    family_id MEDIUMINT UNSIGNED,   -- not auto-inc here
    name VARCHAR(66) NOT NULL,
    ...
    PRIMARY KEY(family_id, name)    -- note "composite"
    );

A PRIMARY KEY is a UNIQUE KEY is a KEY.

You say you cannot add a second table. But why? You mention needing to generate a particular JSON? Can't that simply be done via a JOIN of the two tables if necessary?

edited Jul 17, 2018 at 17:57

answered Jul 11, 2018 at 15:36

Rick James

144k15 gold badges144 silver badges254 bronze badges

Comments

Bill Karwin · Accepted Answer · 2018-07-11 15:50:13Z

If you can't create a second table to model the constraint properly, then you will have to resort to serializing inserts:

LOCK TABLES new_table WRITE;
Use a SELECT to check if the family id exists in the table.
If the family id is not present, INSERT your new data.
UNLOCK TABLES;

It's necessary to lock the table because otherwise you will have a race condition. Two sessions could check if the family id exists, both find that it does not exist, and then both proceed with their INSERT. If you lock the table, then one session will acquire the lock and do its work, while the other session must wait for the lock, and by the time it acquires the lock, its check will find that the family id has been inserted by the first session.

This method is usually considered bad for concurrency, which can limit your throughput if you have many requests. But if you have infrequent requests, the impact to throughput will be minimal.

reaanb · Accepted Answer · 2018-07-11 16:53:19Z

0

This is a workaround for a design problem, but I figured I might as well post it. You can generate a query such as the following:

INSERT INTO `new_table` (`family_id`, `name`)
SELECT * FROM (
    SELECT 1, 'name1'
    UNION ALL
    SELECT 1, 'name2'
) x
LEFT JOIN `new_table` n ON n.family_id = 1
WHERE n.family_id IS NULL

Then check the number of rows affected to determine if it was successful or not.

answered Jul 11, 2018 at 16:53

reaanb

10.1k2 gold badges27 silver badges40 bronze badges

Collectives™ on Stack Overflow

Adding constraints in the MySQL database to guarantee uniqueness

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related