Insert a constraint for two columns in sql

Question

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.

There is no id_client column in the table. Edit your question to explain better what you want. — forpas, May 23 '22 at 17:16

score 0 · Accepted Answer · answered May 23 '22 at 18:33

0

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.

answered May 23 '22 at 18:33

forpas

160,666
10
38
76

Thanks! So it was probably a problem of bad designing – giovanni sergi May 23 '22 at 19:58

Insert a constraint for two columns in sql

1 Answers1