Mysql Column constraint as "not empty" / "required"

Question

Can we specify a column in mysql as "not empty" / "required". The requirement is to ensure that the field never remains blank on any record insertion.

Answer 1

I assume you don't want blank (empty string, as opposed to NULL) values to be allowed in the table either.

Normally, that's what a CHECK constraint for. You do something like

CREATE TABLE
        mytable
        (
        myfield NOT NULL VARCHAR(200),
        CHECK(myfield > '')
        )

However, MySQL parses the constraint but does not enforce it. You are still allowed to insert empty values.

To work around that, create a BEFORE INSERT trigger and raise a signal on an attempt to insert a blank value:

CREATE TRIGGER
        tr_mytable_bi
BEFORE INSERT
ON      mytable
FOR EACH ROW
BEGIN
        IF NEW.myfield = '' THEN
                SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Blank value on mytable.myfield';
        END IF;
END;

Do the same on BEFORE UPDATE if you want to forbid updates to a blank value as well.