CODEWITHSUNDEEP
mysqlsql
georgevich
georgevich

Reputation: 2495

Mandatory field mysql

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

I have the following mysql table structure:

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.

Upvotes: 1

Views: 13843

Answers (4)

Prasanth Madhavan
Prasanth Madhavan

Reputation: 13347

'' 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.

Upvotes: 0

Xedecimal
Xedecimal

Reputation: 3223

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.

Upvotes: 1

smcphill
smcphill

Reputation: 826

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...

Upvotes: 0

YeJiabin
YeJiabin

Reputation: 1038

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.

Upvotes: 0

Related Questions