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

Question

Is it possible to specify a column in mysql as "not empty" / "required". The requirement is to ensure that the field never remains blank when entering any entry.

Answer 1

I assume that you do not want the table to have valid empty values ​​(empty string, as opposed to NULL ).

Usually this is a CHECK constraint for. You do something like

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

However, MySQL analyzes the restriction, but does not apply it. You are still allowed to insert null values.

To get around this, create a BEFORE INSERT trigger and raise a signal when you try to insert an empty 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 also prohibit updating an empty value.

Answer 2

Use NOT NULL .

This leads to the need for data entry.
But the field, which should not be empty, needs an explicit script.

Example:

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

Refer to : Wiki in Null (SQL)

Answer 3

You can define a column as NOT NULL as indicated by @Ravinder

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

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

Here, if no value is received for these columns, NULL will be added by default.