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.
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.