title: What causes MySQL not to enforce NOT NULL constraint?
body: <p>The following query surprised me and didn't fail, but inserted a record.</p>
  
  <pre><code>INSERT INTO buy_contacts(buys_id,contacts_id,buy_status,sites_id,record_status,date_modified,date_created)
  VALUES(863999180,1367026068,"buy",2675631760,"active",NOW(),NOW());
  </code></pre>
  
  <p>A SELECT query shows it as being inserted.</p>
  
  <pre><code>+-----------+-------------+-------+-----------------+-----------------+---------------+------------+------------+---------------+----------------+
  |  buys_id  | contacts_id | notes |  date_modified  |  date_created   | record_status |  sites_id  | buy_status | created_by_id | modified_by_id |
  +-----------+-------------+-------+-----------------+-----------------+---------------+------------+------------+---------------+----------------+
  | 863999180 |  1367026068 | NULL  | 1/10/2015 10:31 | 1/10/2015 10:31 | active        | 2675631760 | buy        |             0 |              0 |
  +-----------+-------------+-------+-----------------+-----------------+---------------+------------+------------+---------------+----------------+
  </code></pre>
  
  <p>My table schema (created by MySQL Workbench) is shown below.  As seen, <code>created_by_id</code> and <code>modified_by_id</code> are both <code>NOT NULL</code>, do not have a <code>DEFAULT</code> value, and the query that inserted the record did not include these columns.</p>
  
  <p>Why didn't MySQL reject the query due to a NOT NULL constraint and why did it insert values of zero for these columns?</p>
  
  <pre><code>SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
  
  // other tables are created...
  
  CREATE TABLE IF NOT EXISTS `buy_contacts` (
    `buys_id` INT UNSIGNED NOT NULL,
    `contacts_id` INT UNSIGNED NOT NULL,
    `notes` TEXT NULL,
    `date_modified` DATETIME NOT NULL,
    `date_created` DATETIME NOT NULL,
    `record_status` VARCHAR(8) NOT NULL,
    `sites_id` INT UNSIGNED NOT NULL,
    `buy_status` CHAR(3) NOT NULL,
    `created_by_id` INT UNSIGNED NOT NULL,
    `modified_by_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`buys_id`, `contacts_id`),
    INDEX `fk_buys_has_contacts_contacts1_idx` (`contacts_id` ASC),
    INDEX `fk_buys_has_contacts_buys1_idx` (`buys_id` ASC),
    INDEX `fk_buy_contacts_record_status2_idx` (`record_status` ASC),
    INDEX `fk_buy_contacts_sites2_idx` (`sites_id` ASC),
    INDEX `fk_buy_contacts_buy_status1_idx` (`buy_status` ASC),
    INDEX `fk_buy_contacts_users1_idx` (`created_by_id` ASC),
    INDEX `fk_buy_contacts_users2_idx` (`modified_by_id` ASC),
    CONSTRAINT `fk_buys_has_contacts_buys1`
      FOREIGN KEY (`buys_id`)
      REFERENCES `buys` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_buys_has_contacts_contacts1`
      FOREIGN KEY (`contacts_id`)
      REFERENCES `contacts` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_buy_contacts_record_status2`
      FOREIGN KEY (`record_status`)
      REFERENCES `record_status` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_buy_contacts_sites2`
      FOREIGN KEY (`sites_id`)
      REFERENCES `sites` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_buy_contacts_buy_status1`
      FOREIGN KEY (`buy_status`)
      REFERENCES `buy_status` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_buy_contacts_users1`
      FOREIGN KEY (`created_by_id`)
      REFERENCES `users` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_buy_contacts_users2`
      FOREIGN KEY (`modified_by_id`)
      REFERENCES `users` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  </code></pre>