Unique constraint on tables for particular values of a field

user_2194375 0
Viewed 954

I have an invoice table. It has many fields, but the problem is around 2 main fields

  • InvoiceNo - alphanumeric values
  • Deleted - boolean 1 or 0 ,to represent a record is deleted or not.

Our business requires InvoiceNo to be unique. However if a row is deleted we can re-use the InvoiceNo

InvoiceNo    Deleted
123Er        1
123Er        0

Above is a valid use case. But i don't want to have another record with 123Er & 0.

Is it possible to create unique key on combination of 2 fields for certain values Unique (InvoiceNo, Deleted=0) or should we go for stored procedure or triggers ?

mariadb unique-key mysql sql
3 Answers

It is quite easy to achieve in other RDBMS systems with help of a function based index

As for now MySql doesn't have such a feature, but starting from version 5.7 it can be simulated with use of a virtual (or generated) column.

Simple working example: http://rextester.com/HGY68688

CREATE TABLE IF NOT EXISTS mytable1234(
  InvoiceNo varchar(10),
  Deleted int,
  xxx varchar(10) generated always as (case when deleted = 0 then InvoiceNo end) virtual
);

create unique index myindex12 on mytable1234( xxx );

INSERT INTO mytable1234( InvoiceNo, Deleted)  VALUES ('aaa1', 0 );
INSERT INTO mytable1234( InvoiceNo, Deleted)  VALUES ('aaa1', 1 );
INSERT INTO mytable1234( InvoiceNo, Deleted)  VALUES ('aaa1', 1 );

-- INSERT INTO mytable1234( InvoiceNo, Deleted)  VALUES ('aaa1', 0 );

If you uncomment the last INSERT in this snippet, and then try to run this snippet, then you will get: Duplicate entry 'aaa1' for key 'myindex12' error.
In this way, there may be multiple records in the table with the same InvoiceNo value for deleted = 1, but only one value for deleted = 0, because MySql will not allow this.

user_25683410

You could rename the field something like DeletedIfNull (or IsActive).

Then, the field would take the value of "1" or "true" or whatever if the column is active. It would be NULL for any other values. Then you can create:

create unique index unq_t_invoiceno_isactive on t(invoiceno, isactive);

MySQL (although not all databases) allows repeats when a unique index is defined. Hence, this will solve your immediate problem.

user_11440350

You can just create a unique key (InvoiceNo, Deleted), and its done. I dont understand why you need to make (InvoiceNo, Deleted=0), which is not supported. Having a unique key of those 2 columns provide you what you need.

UPDATE: I got your problem that you might have 2 deleted rows of same values. In this case, i would suggest you prepend something to InvoiceNo column of deleted values so that they won't be unique.

For example: You have 123er and then delete it, so it becomes 123er-1. When you delete another one, it becomes 123er-2. You can even do it basically in an update(before) trigger.

user_27566570
Related