I need to add constraint Order such that the unique combination of fields exist within another table.

Table: Product
productId PRIMARY KEY
productNumber

and:

Table: Order
clientId FOREIGN KEY
productId FOREIGN KEY
productNumber
dateBorrowed
dateReturned

I have tables Product and Order. productId and productNumber create a unique combination to specify a product as multiple versions. When inserting into Order the unique combination of productId and productNumber must exist within the Product table. Duplicates are allowed as items can be returned and borrowed again. How to create the constraint when creating Order table?

Product :

Order :

CodePudding user response：

The unique combination of fields you describe is in fact the primary key :

CREATE TABLE Product(
    productId INTEGER,
    productNumber INTEGER,
    ...
    PRIMARY KEY (productId, productNumber)
);

You can reference it in the Order table :

CREATE TABLE "Order"(
    ...
    productId INTEGER,
    productNumber INTEGER,
    ...
    FOREIGN KEY(productId, productNumber) REFERENCES Product(productId, productNumber)
);

Please note the Order table name must be quoted, ORDER is a SQLite keyword. Also foreign keys must be enabled :

PRAGMA foreign_keys = ON;