Representing SQL constraints on a table 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.

I think it should start with something like this:

CHECK ( NOT EXISTS ...
Sorry, to tell you: Check Constraints are not implemented in MySQL.

You are probably better off writing a BEFORE INSERT,BEFORE UPDATE trigger that would check the count using something like

SELECT COUNT(1) into UnitSold
FROM sold WHERE partId = OLD.partId


SCENARIO : Implement a check constraint by means of a trigger

We have a simple sold table that look like this:

use test
DROP TABLE IF EXISTS `sold`;
CREATE  TABLE `sold` (
`part_ID` INT(11) NOT NULL ,
`date` DATE NOT NULL ,
`name` VARCHAR(20),
 PRIMARY KEY (`part_ID`, `date`)
);


Let create it:

mysql&gt; use test
Database changed
mysql&gt; DROP TABLE IF EXISTS `sold`;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; CREATE  TABLE `sold` (
    -&gt; `part_ID` INT(11) NOT NULL ,
    -&gt; `date` DATE NOT NULL ,
    -&gt; `name` VARCHAR(20),
    -&gt;  PRIMARY KEY (`part_ID`, `date`)
    -&gt; );
Query OK, 0 rows affected (0.06 sec)

mysql&gt;


Here is the trigger to only allow 3 sales per part_ID

DELIMITER $$   
CREATE TRIGGER sold_beforeinsert_trigger
BEFORE INSERT ON sold FOR EACH ROW   
BEGIN   
    DECLARE UnitsSold INT;

    SELECT COUNT(1) INTO UnitsSold FROM sold WHERE part_ID = NEW.part_ID;
    IF UnitsSold &gt;= 3 THEN
        SELECT COUNT(gummybears) INTO UnitsSold
        FROM information_schema.schemata;
    END IF;

END; $$ 
DELIMITER ;


Let's create it:

mysql&gt; DELIMITER $$
mysql&gt; CREATE TRIGGER sold_beforeinsert_trigger
    -&gt; BEFORE INSERT ON sold FOR EACH ROW
    -&gt; BEGIN
    -&gt;     DECLARE UnitsSold INT;
    -&gt;
    -&gt;     SELECT COUNT(1) INTO UnitsSold FROM sold WHERE part_ID = NEW.part_ID;
    -&gt;     IF UnitsSold &gt;= 3 THEN
    -&gt;         SELECT COUNT(gummybears) INTO UnitsSold
    -&gt;         FROM information_schema.schemata;
    -&gt;     END IF;
    -&gt;
    -&gt; END; $$
Query OK, 0 rows affected (0.08 sec)

mysql&gt; DELIMITER ;
mysql&gt;


Here is some sample data to load and display:

SET @DaysBack = 0;
SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
SELECT * FROM sold;


Let's run this:

mysql&gt; SET @DaysBack = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
+---------+------------+-----------+
4 rows in set (0.00 sec)

mysql&gt;


Let run it again without the first line

mysql&gt; SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
+---------+------------+-----------+
8 rows in set (0.00 sec)

mysql&gt;


and again

mysql&gt; SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
|       1 | 2013-03-08 | rolando   |
|       2 | 2013-03-08 | pamela    |
|       3 | 2013-03-08 | dominique |
|       4 | 2013-03-08 | diamond   |
+---------+------------+-----------+
12 rows in set (0.00 sec)

mysql&gt;


and again

mysql&gt; SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql&gt; INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql&gt; INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql&gt; INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql&gt; INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql&gt; SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
|       1 | 2013-03-08 | rolando   |
|       2 | 2013-03-08 | pamela    |
|       3 | 2013-03-08 | dominique |
|       4 | 2013-03-08 | diamond   |
+---------+------------+-----------+
12 rows in set (0.00 sec)

mysql&gt;


AH HA !!! The trigger stopped the INSERT cold in its tracks where a part_ID had three rows in the sold table. MySQL Triggers are not the greatest. Notice I had to SELECT the count of a column that does not exist in information_schema.schemata to cause the trigger to abort.