How to add check constraint on one column using another column from same table

Question

My table structure is as follows:

create table Sale(
    id int primary key,
    product_ID int foreign key references ItemCategory.Item_Details(item_id),
    product_name varchar(10),
    unit_price int,
    quantity int,
    discount int,
    total_price int,
    profit int
)

Now I have to implement the following constraints in the sale table: The discount offered on a product should not be greater than 20% of the unit price of the product.

You should define a table constraint, not a column constraint. — Frazz
– Frazz, Commented Apr 26, 2014 at 9:28
@Frazz but the scenario is first the table is created after that i have to alter the table to add the constraint. How to do? — ravibagul91
– ravibagul91, Commented Apr 26, 2014 at 9:36
You must define discount as percent not as absolute value. In this case you can set check constraint on discount field only. Notice, that setting discount as absolute value creates functional dependency of on part of row to another part which breaks normalization rules. — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Apr 26, 2014 at 9:37

Frazz · Accepted Answer · 2014-04-26 09:40:20Z

1

ALTER TABLE Sale
  ADD CONSTRAINT ValidDiscount
  CHECK(discount <= unit_price *0.2);

This is ANSI 92, but IIRC not all RDBMS support it... MySQL doesn't.

answered Apr 26, 2014 at 9:40

Frazz

3,0432 gold badges22 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

How to add check constraint on one column using another column from same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related