Insert a constraint for two columns in sql-CodePudding

Hi everyone I created this table for a database

CREATE TABLE IF NOT EXISTS compositions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        id_product INTEGER REFERENCES products(id),
        id_receipt INTEGER REFERENCES receipt(id),
        quantity INTEGER NOT NULL,
        price FLOAT NOT NULL,
        id_user VARCHAR REFERENCES users(id),
        CONSTRAINT CHK_price_quantity CHECK (price > 0 AND quantity > 0)
    );

I would like to insert a constraint that, for a given id_receipt, the id_user must be always the same but I don't know how to implement it. Could someone give a help?

Thank you in advance.

CodePudding user response：

For this requirement the column id_user should not be defined in the table compositions.

It makes more sense to store it in the table receipt, since each id_receipt is related to 1 and only 1 id_user.

This way you have id_receipt as a foreign key in compositions and through this column you can get (by a join) all the info that you want from receipt, like the id_user.