Add column in my sql , with values that are not allowed

Question

How to add column,where values in that column are not allowed Example ... column age , values that are not allowed are 1,2,3,6 (other values are allowed) I tried to use check constraint but mySql ignored it ...

Can you help me with this question or at least send me a material about this question .

Thanks a lot!

Learn how to ask question https://stackoverflow.com/help/how-to-ask It will help people help you answer your question. — Eric, Oct 16 '17 at 17:41
Write the code in mysql that add column color (varchar(20)) in table boats, default value is 'pink' , values in that column that are not allowed are : 'white','black', 'red'( other values are allowed !! ) ALTER TABLE Boats add column colors varchar(20) DEFAULT 'white' ... ? check constraint is ignored in mysql! — Gent 32, Oct 16 '17 at 17:41
Possible duplicate of [CHECK constraint in MySQL is not working](https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working) — Lukasz Szozda, Oct 16 '17 at 17:44
You can store the blacklist in a separate table if you like. — Strawberry, Oct 16 '17 at 20:47

score 0 · Answer 1 · answered Oct 16 '17 at 17:53

Check constraint won't work in MySql.

Alternatives:

Add two triggers on your table:

Before Insert
Before Update

Example Query:

CREATE TRIGGER checkage_bi BEFORE INSERT ON mytable FOR EACH ROW
    -> BEGIN
    ->     DECLARE dummy,baddata INT;
    ->     SET baddata = 0;
    ->     IF NEW.color = 'white' or NEW.color = 'black' or NEW.color = 'red' THEN
    ->         SET baddata = 1;
    ->     END IF;         
    ->     IF baddata = 1 THEN
    ->         SELECT CONCAT('Cannot Insert This Because Color ',NEW.color,' is Invalid')
    ->         INTO dummy FROM information_schema.tables;
    ->     END IF;
    -> END; $$

You can similarly create Before Update trigger too.

For more info click here

Hope it helps!

Add column in my sql , with values that are not allowed

1 Answers1