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
– forpas, Commented May 23, 2022 at 17:16

forpas · Accepted Answer · 2022-05-23 18:33:49Z

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, 2022 at 18:33

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

giovanni sergi Over a year ago

Thanks! So it was probably a problem of bad designing

Collectives™ on Stack Overflow

Insert a constraint for two columns in sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related