CODEWITHSUNDEEP
phpmysqlsqldatabaseconstraints
Kaz
Kaz

Reputation: 1258

MySQL char accepts values which are not fixed

I still can't able to figured out why is this. Below is my SQL syntax..

create table usertbl{
    id int auto_increment,
    name varchar(30) not null,
    cupon_code char(10) not null,
    primary key(id)
}

So according to this table, the cupon_code field has a fixed length, but when I insert data to that field without a fixed value, like we3434, it gets accepted.

I want the table to reject values for cupon_code field if the value is less than 10 or greater than 10. It always has to be fixed with 10 characters. So can I know why is this? and how can I avoid this issue?

Upvotes: 1

Views: 561

Answers (2)

Aravindhan R
Aravindhan R

Reputation: 269

You can use where condition while inserting data like

Insert into tablename (column name) values (values) where len(columnname) <= 10

Even though you got the answer may you can get some idea

Upvotes: 0

Blag
Blag

Reputation: 5894

You can't set a constraint on MIN with mysql and the CHAR(x) only force the max length.

Using MySQL VARCHAR like Data type with MIN and MAX character lengths you can make the following trigger :

DELIMITER $$

CREATE TRIGGER `tgi_usertbl_cupon_code_length` BEFORE INSERT ON `usertbl` 
FOR EACH ROW
BEGIN

    IF LENGTH(NEW.cupon_code) <> 10
    THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'ERROR - Invalid length of cupon_code';
    END IF;

END $$
DELIMITER ;

Upvotes: 1

Related Questions