MariaDB – Why INSERT Fails with NOT NULL but UPDATE Works

insertmariadbnullupdate

I have this table definition:

CREATE TABLE `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brands_id` int(11) DEFAULT NULL,
  `media_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8_unicode_ci NOT NULL,
  `thumbnail_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `media_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `media_code` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `inactive` tinyint(1) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `titleHtml` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `descriptionHtml` longtext COLLATE utf8_unicode_ci NOT NULL,
  `useHtml` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_6A2CA10CBF396750` (`id`),
  KEY `IDX_6A2CA10CE9EEC0C7` (`brands_id`),
  CONSTRAINT `FK_6A2CA10CE9EEC0C7` FOREIGN KEY (`brands_id`) REFERENCES `brands` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

Each time I try to INSERT a new record I get an error due to NOT NULL constraints, that is OK, now if I try to UPDATE the same record and I leave title and/or description or any other required field empty the UPDATE is executed and the column is set to blank, why? How I can avoid this? I am using MariaDB 5.5.46.

UPDATE

As requested by moderator below is the INSERT and UPDATE queries I have tried:

INSERT INTO media (media_type, title, titleHtml, description, descriptionHtml, thumbnail_url, media_url, media_code, inactive, useHtml, createdAt, updatedAt, brands_id) VALUES (null,"Media Title Test",null,null,null,"https://someurl",null,null,false,false,"2015-10-22 22:07:54","2015-10-22 22:07:54",null)

The INSERT above fails because the CONSTRAINTS in the DB

UPDATE media SET description = null, updatedAt = "2015-10-22 22:12:18" WHERE id = 13

The UPDATE above pass and this is the problem because description column isn't allowed to have NULL values

Best Answer

The problem is in your database configuration. MariaDB by default SQL_MODE isn't STRICT and UPDATEs even if it's set to NOT NULL.

But this is a pointer to the answer:

A mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled is called strict mode. With strict mode not set (the default), MariaDB will automatically adjust invalid values, for example, truncating strings that are too long, or adjusting numeric values that are out of range, and produce a warning.

With strict mode set, the statements will fail, and an error will be returned instead. The IGNORE keyword can be used when strict mode is set to convert the error to a warning.

So you need to set the appropriate SQL_MOD as described at: https://mariadb.com/kb/en/mariadb/sql_mode/

Related Question