CREATE TABLE Products (
Products_Id integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
ItemNumber integer NOT NULL UNIQUE,
Description nvarchar(255) NOT NULL
CREATE TABLE Sales (
Sales_Id integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
ItemNumber integer NOT NULL,
TheDate datetime NOT NULL,
Volume real NOT NULL,
/* Foreign key */
FOREIGN KEY (ItemNumber)
ON DELETE NO ACTION
ON UPDATE NO ACTION
In the Sales table I would like to create a constraint to limit (ItemNumber + TheDate) to be UNIQUE when combined, but can be duplicated providing either value is different.
Also, more importantly, is a constraint like this going to slow data entry down? I intend of having up to 2-3 million rows within this table. Approximately 5,000 entries per day adds almost 2 million rows per year. Would it be better to have more thorough code to prevent entering duplicates?