How to have a concatenation of string columns as unique constraint in MySQL

Question

I have a table say employee The employee table has few fields such as

`salary`
`name`
`age`
`designation`

Out of which only designation is nullable. I can't make it NOT NULL as it is being used by existing code.

Is it possible to create a combined unique constraint for all the columns mentioned above ??

When I try creating a new unique constraint.

ALTER TABLE `employee` ADD CONSTRAINT `employee_constraint` 
UNIQUE key (`salary`,`name`, `age`, `designation`);

It creates the constraint successfully but when I try to insert the duplicated combination of these records, it inserts successfully. Is there a better way to create the constraint and discard the duplicate combination of these columns?

It inserts exactly same combination say (1000, 'xyz', 10, NULL) twice — user9920500, Jun 10 '18 at 08:33
Related [Does MySQL ignore null values on unique constraints?](https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints) — baao, Jun 10 '18 at 08:41
Right, changed it to related and retracted the vote @lad2025 — baao, Jun 10 '18 at 08:43
Thanks a lot for answering the question. But this is for MySQL version 5.7. But can we do it for version 5.6? If not what else can be done? — user9920500, Jun 14 '18 at 11:29
'generated column' is not supported in MySQL 5.6. Is there any alternative ? — user9920500, Jun 14 '18 at 11:44

Lukasz Szozda · Answer 1 · 2018-06-10T08:51:46.487

3

Normally MySQL allows multiple NULLs in UNIQUE constraint. More info: Does MySQL ignore null values on unique constraints?

You could use generated column to allow only one NULL value:

CREATE TABLE employee(salary INT, name VARCHAR(100)
                     ,age INT, designation VARCHAR(100)
                ,designation_virtual VARCHAR(100) AS (COALESCE(designation, '^'))
);

ALTER TABLE employee ADD CONSTRAINT employee_constraint 
UNIQUE key (salary,name, age, designation_virtual) ;

INSERT INTO employee(salary, name, age, designation)
VALUES(1000, 'xyz', 10, NULL);

INSERT INTO employee(salary, name, age, designation) 
VALUES(1000, 'xyz', 10, NULL);
-- Duplicate entry '1000-xyz-10-^' for key 'employee_constraint'

SELECT * FROM employee;

DBFiddle Demo

If you are using MariaDB 10.3.3 you could mark virtual column as INVISIBLE.

Columns can be given an INVISIBLE attribute. These columns will then not be listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement, unless INSERT explicitly mentions them by name.

ALTER TABLE employee MODIFY designation_virtual INVISIBLE;

edited Jun 10 '18 at 08:51

answered Jun 10 '18 at 08:43

Lukasz Szozda

162,964
23
234
275

Thank you very much, But wanted to know what is the effect of this on performance ?? Considering that I insert around 1000 records at once and update 5 records at once in the extreme case ?? – user9920500 Jun 14 '18 at 11:51
I would say that minimal. But you should never trust any words. Just do baseline test with your incorrect setup (measure time to do the job), then do the same for new correct setup(duplicates no allowed). – Lukasz Szozda Jun 14 '18 at 11:56
Looks like the above code does not work for MySQL 5.6 – user9920500 Jun 14 '18 at 12:58
@user9920500 I believe generated columns are available from 5.7 – Lukasz Szozda Jun 14 '18 at 17:06

How to have a concatenation of string columns as unique constraint in MySQL

1 Answers1