Mandatory field mysql

How to make sure that field is mandatory ? Here is what I mean

I have the following mysql table str开发者_JAVA技巧ucture:

CREATE TABLE `new` (
  `id` int(11) DEFAULT NULL,
  `name` int(11) DEFAULT NULL,
  `phone` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is query with no data for phone

insert into new values(1, 'm', '');

But the query runs fine. What should be done so that mysql returns an error if there is no data for phone field? I can do that validation by php, but I'm curious how to do that in mysql.

Possibly setting the default value of the 'phone' column to NULL would make it fail insertion because it would end up null if you did not specify it.

Otherwise you're going to need to omit the phone column for the default to kick in, say in php you'd use empty($phone) ? null : $phone; or something along those lines.

INSERT INTO new VALUES(1,'m',NULL)

will cause error.
If you want to check whether is the phone number field is a blank string,
you can use a trigger in MySQL.

I haven't tested this, but I have a feeling the '' != null. What happens if you run

insert into new(id, name) values (1, 'test');

I bet you get an insert error...

Anyway, I think its probably better to be validating in PHP than waiting till you get to the database... inserts are expensive...

'' as the 3rd option doesnt make the value of phone null.. It is just equal to a blank string thats all. if you want to see an error, replace '' with NULL.