MySql NOT NULL Constraint doesnot work

Question

I am trying to implement NOT NULL constraint in the customer table which is created as:

CREATE TABLE CUSTOMER(
     cust_id INT(5) NOT NULL AUTO_INCREMENT, 
     PRIMARY KEY(cust_id),
     first_name VARCHAR(25) NOT NULL,
     last_name VARCHAR(25) NOT NULL,
     email VARCHAR(25) NOT NULL,
     password VARCHAR(25) NOT NULL,
     gender VARCHAR(1) NOT NULL,
     city VARCHAR(25) NOT NULL,
     dob DATE NOT NULL,
     pin INT NOT NULL);

After this from a form i am passing values and inserting it as :

$sql= "INSERT INTO customer(first_name,last_name,email,password,gender,city,dob,pin) VALUES('$first_name','$last_name','$email_add','$password','$gender','$city','$DOB','$pin')";

However if i am passing blank values of the fields,Mysql seems to insert them? What can be the possible problem??

Answer 1

I suspect that's because, in a real DBMS (as opposed to Oracle :-), there is a distinction between a blank value and a NULL value.

Blank strings are perfectly valid for NOT NULL fields. The only value you shouldn't be able to put in a NOT NULL field is, hang on, trying to remember, ... NULL. Yes, that's it. NULL. :-)

Aside: Oracle made a decision many moons ago to treat empty VARCHARs as NULLs and it still haunts them (or it would if I wasn't the only one that refused to use it because of that problem).

If you want your PHP code to work like Oracle (where blanks become NULLs), you're going to have to pre-process the string, something like (pseudocode):

if $first_name == "":
    $first_name = "NULL"
else:
    $first_name = "'" + $first_name + "'"
: : :
$sql= "INSERT INTO customer(" +
    "first_name," +
    "last_name," +
    "emailpassword," +
    "gender," +
    "city," +
    "dob," +
    "pin" +
    ") VALUES (" + 
        $first_name + "," +
        "'$last_name'" + "," +
        "'$email_add'" + "," +
        "'$password'" + "," +
        "'$gender'" +
        ",'$city'" + "," +
        "'$DOB'" + "," +
        "'$pin'" +
    ")";

and so on for the other fields which can be NULL as well. I've only shown how to do it for $first_name.

That will cause your first_name column in the INSERT statement to be either NULL or the value of $first_name surrounded by single quotes.

Keep in mind all these fields should be checked and/or modified to prevent SQL injection attacks.

Answer 2

$sql= "INSERT INTO customer(first_name,last_name,email,password,gender,city,dob,pin) VALUES('$first_name',".
($last_name?"'".$last_name."'":"NULL").
", '$email_add','$password','$gender','$city','$DOB','$pin')";

This will be:

VALUES('ahm',NULL,'[email protected]' ...

And because of NOT NULL nothing will be inserted.

Answer 3

Some additional information from the docs here:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html