Sql-server – UDF in check constraint — downside

What you need are two triggers to catch the invalid age condition

• BEFORE INSERT
• BEFORE UPDATE

The following is based on a jerry-rigged error trapping method for MySQL Triggers from Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers':

drop table mytable; 
create table mytable ( 
    id smallint unsigned AUTO_INCREMENT, 
    age tinyint not null, 
    primary key(id) 
); 
DELIMITER $$  
CREATE TRIGGER checkage_bi BEFORE INSERT ON mytable FOR EACH ROW  
BEGIN  
    DECLARE dummy,baddata INT;  
    SET baddata = 0;  
    IF NEW.age > 20 THEN  
        SET baddata = 1;  
    END IF;  
    IF NEW.age < 1 THEN  
        SET baddata = 1;  
    END IF;  
    IF baddata = 1 THEN  
        SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid')  
        INTO dummy FROM information_schema.tables;
    END IF;  
END; $$  
CREATE TRIGGER checkage_bu BEFORE UPDATE ON mytable FOR EACH ROW  
BEGIN  
    DECLARE dummy,baddata INT;  
    SET baddata = 0;  
    IF NEW.age > 20 THEN  
        SET baddata = 1;  
    END IF;  
    IF NEW.age < 1 THEN  
        SET baddata = 1;  
    END IF;  
    IF baddata = 1 THEN  
        SELECT CONCAT('Cannot Update This Because Age ',NEW.age,' is Invalid')  
        INTO dummy FROM information_schema.tables;
    END IF;  
END; $$  
DELIMITER ;  
insert into mytable (age) values (10);
insert into mytable (age) values (15);
insert into mytable (age) values (20);
insert into mytable (age) values (25);
insert into mytable (age) values (35);
select * from mytable;
insert into mytable (age) values (5);
select * from mytable;

Here is the result:

mysql> drop table mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable (
    ->     id smallint unsigned AUTO_INCREMENT,
    ->     age tinyint not null,
    ->     primary key(id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER checkage_bi BEFORE INSERT ON mytable FOR EACH ROW
    -> BEGIN
    ->     DECLARE dummy,baddata INT;
    ->     SET baddata = 0;
    ->     IF NEW.age > 20 THEN
    ->         SET baddata = 1;
    ->     END IF;
    ->     IF NEW.age < 1 THEN
    ->         SET baddata = 1;
    ->     END IF;
    ->     IF baddata = 1 THEN
    ->         SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid')
    ->         INTO dummy FROM information_schema.tables;
    ->     END IF;
    -> END; $$
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TRIGGER checkage_bu BEFORE UPDATE ON mytable FOR EACH ROW
    -> BEGIN
    ->     DECLARE dummy,baddata INT;
    ->     SET baddata = 0;
    ->     IF NEW.age > 20 THEN
    ->         SET baddata = 1;
    ->     END IF;
    ->     IF NEW.age < 1 THEN
    ->         SET baddata = 1;
    ->     END IF;
    ->     IF baddata = 1 THEN
    ->         SELECT CONCAT('Cannot Update This Because Age ',NEW.age,' is Invalid')
    ->         INTO dummy FROM information_schema.tables;
    ->     END IF;
    -> END; $$
Query OK, 0 rows affected (0.07 sec)

mysql> DELIMITER ;
mysql> insert into mytable (age) values (10);
Query OK, 1 row affected (0.06 sec)

mysql> insert into mytable (age) values (15);
Query OK, 1 row affected (0.05 sec)

mysql> insert into mytable (age) values (20);
Query OK, 1 row affected (0.04 sec)

mysql> insert into mytable (age) values (25);
ERROR 1172 (42000): Result consisted of more than one row
mysql> insert into mytable (age) values (35);
ERROR 1172 (42000): Result consisted of more than one row
mysql> select * from mytable;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  2 |  15 |
|  3 |  20 |
+----+-----+
3 rows in set (0.00 sec)

mysql> insert into mytable (age) values (5);
Query OK, 1 row affected (0.07 sec)

mysql> select * from mytable;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  2 |  15 |
|  3 |  20 |
|  4 |   5 |
+----+-----+
4 rows in set (0.00 sec)

mysql>

Please also notice that auto increment values are not wasted or lost.

Give it a Try !!!

I would create another table.

This allows for several possibilities:

1. that one day some other part number such as 9 would be acceptable. You can quickly and transparently add it to the table, rather than changing a bunch of constraints on different tables.
2. that you might want to store some extra data alongside each part number, for example the name of the part. You can add a column to the table which you already have.

The only situation where I would not do it this way, was if 1 to 8 is something which could never ever change. For example, you might be sure that there will only ever by 7 days of the week (but who knows?).

In this case I would create a TYPE with this constraint. Then you can use the type in multiple tables. If the days of the week (or whatever) DOES change, you can change the TYPE.