Representing SQL constraints

Question

I have this table:

CREATE  TABLE `sold` (
`part_ID` INT(11) NOT NULL ,
`date` DATE NOT NULL ,
 PRIMARY KEY (`part_ID`, `date`) ,
 FOREIGN KEY (`part_ID` )
 REFERENCES `part` (`part_ID` );

This table represent parts sold each day, constraint says number of sales should be at least 25 and at most 100. How can I implement this constraint?

I think it should start with something like this:

CHECK ( NOT EXISTS ...

@AshReva enforcing this constraint: number of sales should be at least 25 and at most 100. — hamid, Mar 11 '13 at 19:04
Please don't cross-post: http://dba.stackexchange.com/q/36413/1822 — a_horse_with_no_name, Mar 11 '13 at 22:24

score 1 · Accepted Answer · answered Mar 11 '13 at 19:07

1

try below

CONSTRAINT chk_sales CHECK (columnName>=25 AND columnName <=100)

Also refer this link

answered Mar 11 '13 at 19:07

DevelopmentIsMyPassion

3,441
4
32
55

There is no column that holds number of sale!! – hamid Mar 11 '13 at 19:17
Then cant you have it? How will you add constraint then? – DevelopmentIsMyPassion Mar 11 '13 at 19:19
If i knew it, I wouldn't ask! – hamid Mar 11 '13 at 19:21
then can you add column as i said? – DevelopmentIsMyPassion Mar 11 '13 at 19:25
No. Table can not be altered. – hamid Mar 11 '13 at 19:28
Which column represent parts sold? – DevelopmentIsMyPassion Mar 11 '13 at 19:28
`part_ID` is the part ID of sold item, and `date` is the day the part is sold. – hamid Mar 11 '13 at 19:29
When will you check if more than 25 items are sold? Is it during inserting records? – DevelopmentIsMyPassion Mar 11 '13 at 19:42
Anyway, has MySQL already added [full support to check constraints](http://stackoverflow.com/questions/14247655/mysql-check-constraint/14247690#14247690) yet? – Andriy M Mar 11 '13 at 21:07
That will not work. MySQL still doesn't support check constraints. – a_horse_with_no_name Mar 11 '13 at 22:24

Representing SQL constraints

1 Answers1