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.

Answer 2

MySQL8 now has value constraints, which allows you to implement value restrictions without using triggers

For example:

CREATE TABLE my_table (
    myfield VARCHAR(255) NOT NULL
        CONSTRAINT myfield_not_empty CHECK(
            LENGTH(myfield) > 0 
        )
);

Will ensure the values in myfield will never be empty

Answer 3

Use NOT NULL.

It causes input to be required.
But a field to be not empty you need validation script explicitly.

Example:

create table example(
  login_name varchar(16) not null
  pass_word varchar(32) not null
);

Refer to: Wiki on Null (SQL)

Answer 4

You can define the column as NOT NULL as answered by @Ravinder

In addition to it you can set a default value to a column. If we take the example of previous answer, we can do it as below:

create table example( login_name varchar(16) = null pass_word varchar(32) = null );

Here if no value is received for these columns then NULL will be inserted by default.