php - MySQL ignores the NOT NULL constraint

Question

I have created a table with NOT NULL constraints on some columns in MySQL. Then in PHP I wrote a script to insert data, with an insert query. When I omit one of the NOT NULL columns in this insert statement I would expect an error message from MySQL, and I would expect my script to fail. Instead, MySQL inserts empty strings in the NOT NULL fields. In other omitted fields the data is NULL, which is fine. Could someone tell me what I did wrong here?

I'm using this table:

CREATE TABLE IF NOT EXISTS tblCustomers (
  cust_id int(11) NOT NULL AUTO_INCREMENT,
  custname varchar(50) NOT NULL,
  company varchar(50),
  phone varchar(50),
  email varchar(50) NOT NULL,
  country varchar(50) NOT NULL,
  ...
  date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (cust_id)
) ;

And this insert statement:

$sql = "INSERT INTO tblCustomers (custname,company) 
        VALUES ('".$customerName."','".$_POST["CustomerCompany"]."')";
$res = mysqli_query($mysqli, $sql);

Or using bind variables:

$stmt = mysqli_prepare($mysqli, "INSERT INTO tblCustomers (custname,company, email, country) VALUES (?, ?, ?, ?)");

mysqli_stmt_bind_param($stmt, 'ssss', $customerName, $_POST["CustomerCompany"], $_POST["CustomerEmail"], $_POST["AddressCountry"]);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

Answer 1

If you're sure you're not using explicit default values, then check your strict mode:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

MySQL Data Type Default Values

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

• If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

• If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

Server SQL Modes