3

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 ?

Mohan
  • 172
  • 1
  • 11

3 Answers3

3

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.

krokodilko
  • 35,300
  • 7
  • 55
  • 79
2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

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.

abeyaz
  • 3,034
  • 1
  • 16
  • 20
  • We need to maintain history, so modifying existing data is a violation. Also if the choice is to use triggers, i can validate my values `before insert` instead of updating existing records. – Mohan Nov 25 '17 at 09:02
  • Then instead of unique constraint, you can define a standard index on those columns, and you can query the table on that index to see if there is a duplicate record before doing your insert. This way you programmatically solve the problem, and it is pretty straightforward. – abeyaz Nov 25 '17 at 09:05
  • Programmaticaly this solves, but i wish to establish constraint at database level. Thanks – Mohan Nov 25 '17 at 09:11
  • Or set `deleted` to the next higher value, not always `1`. – Rick James Nov 25 '17 at 14:19