1

I'm working on a web application which has to do with restaurant menu. I'm using node for back-end and sqlite3 for database system. In my 'items' table i have a field called barcode which is of-course unique, but the user can input some "extra barcodes". What i need is a way to make barcode field and extra_barcodes fields unique between each others.

Dion Zac
  • 69
  • 7
  • What does "unique between each others" mean? Provide a mathematical definition, or show some examples. – CL. Apr 28 '17 at 13:48
  • In example : if item has barcode "a1" ALL items should not be able to have barcode OR extra_barcode "a1" . – Dion Zac Apr 28 '17 at 14:02
  • Could you use a table called barcodes that contains the barcodes? The items table barcode field would be a foreign key from barcode. If the user could add several 'extra barcodes' then a secondary table to implement a many-to-many relationship. An example is [here](http://stackoverflow.com/questions/14227468/in-sqlite-how-to-implement-a-many-to-many-relationship). – Neil Stoker Apr 28 '17 at 14:26
  • I wanted to avoid creating another table, although i think there is no other way. Thank you very much. – Dion Zac Apr 28 '17 at 14:35

1 Answers1

0

To prevent the same value occurring in multiple columns, you cannot use CHECK constraints (because subqueries are not allowed there), so you have to use triggers:

CREATE TRIGGER barcode_unique_insert
AFTER INSERT ON items
BEGIN
    SELECT RAISE(FAIL, "duplicate barcode")
    FROM items
    WHERE extra_barcode  = NEW.barcode
       OR extra2_barcode = NEW.barcode;
END;

It might be a better idea to store barcodes in a separate table with an 1:N relationship.

CL.
  • 173,858
  • 17
  • 217
  • 259