Multi tool use

Adding constraints in the MySQL database to guarantee uniqueness

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:

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

This question has not received enough attention.

5 Answers
5

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.

UNIQUE

family_id

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.

PRIMARY KEY

UNIQUE KEY

KEY

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;

SELECT

INSERT

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.

INSERT

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.

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.

The below would remove the need for the select. If inserts can happen simultaneously, this may fail for race conditions, although it is not obvious from the question that this presents an issue.
The request_id should be unique for each request (as per OP)

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

delimiter //
CREATE TRIGGER VERIFYENTRY
BEFORE INSERT
ON new_table FOR EACH ROW
BEGIN
SET @counter = ( SELECT COUNT(*) FROM new_table T WHERE T.family_id=NEW.family_id and T.request_id!=NEW.request_id );
IF (@counter > 0) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001;
END IF;
END;//
delimiter ;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.