The following should do the trick:
CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO
ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK
(
( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
) = 1
)
GO
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 !!!
Best Answer
You want to enforce the implication:
This can be rewritten as:
Your constraint therefore becomes: