The following query surprised me and did not fail, but entered the record.
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());
A SELECT query shows it as inserted.
+-----------+-------------+-------+-----------------+-----------------+---------------+------------+------------+---------------+----------------+
| 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 |
+-----------+-------------+-------+-----------------+-----------------+---------------+------------+------------+---------------+----------------+
My table (created by MySQL Workbench) is shown below. As you can see, created_by_idthey modified_by_idare NOT NULLnot important DEFAULT, and the query that inserted the record did not include these columns.
Why didn't MySQL reject the query due to the NOT NULL constraint, and why did it insert zero values for these columns?
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;