How do I get the mysql field?

Question

I just found that NOT NULL does not make the required field.

When creating a mysql table, how do I create a field that cannot contain null or empty (must have something in it)?

Answer 1

By default, MySQL accepts invalid values. You can set MySQL to strict mode for forced values. This will reject the query, which does not provide the value of the NOT NULL column, and also ensures the integrity of all column types.

http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sql-mode-important

http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_strict_all_tables

Edit: @Barranka and @RocketHazmat made good comments in the comments. '' is not the same as null, so MySQL will allow this in the NOT NULL column. In this case, you will have to resort to your code or trigger.

In code (e.g. PHP), this can be quite simple by running something like:

 if (!strlen($value)) { // Exclude value or use NULL in query } 

Answer 2

You can set a default value for this field: City varchar (40) DEFAULT "Sandnes"

Answer 3

I think you should do two things:

• Set the column to NOT NULL to force a value
• Use a trigger to check values.

  Inside the trigger, you can cancel the operation if the desired column does not meet the desired condition (for example, with zero length).

This question and its answers reflect this second thing, and here is an example:

 delimiter $$ CREATE TRIGGER `cancel_insert_if_empty` BEFORE INSERT ON `your_table` FOR EACH ROW BEGIN declare msg varchar(255); if NEW.your_column is null or length(NEW.your_column) = 0 then set msg = "You're doing something wrong! Now suffer the consequences"; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; end if; END$$ delimiter ; 

In this example, if you try to insert a null value or a zero-length string in your_column , the error will increase and the insert will be canceled. Quote from the reference guide :

MySQL handles errors during the start of a trigger as follows:

• If the BEFORE trigger does not work, the operation on the corresponding line is not performed.
• The BEFORE trigger is activated by an attempt to insert or change a row, regardless of whether an attempt is subsequently performed.
• An error during a BEFORE or AFTER trigger causes the entire statement to fail, which triggers the trigger.

Of course, you can write a trigger to check for updates.

Hope this helps.