John O John O - 25 days ago 4
SQL Question

mySQL trigger? causing Error Code: 1048 Column cannot be null

I have seen similar questions asked but never seen an answer that works for me. I have the following table and trigger definitions...

DROP TRIGGER IF EXISTS c_consumption.newRateHistory;
DROP TABLE IF EXISTS c_consumption.myrate;
DROP TABLE IF EXISTS c_consumption.myratehistory;

USE c_consumption;
CREATE TABLE `myrate` (
`consumerId` varchar(255) DEFAULT NULL,
`durationType` varchar(50) NOT NULL DEFAULT 'DAY',
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`itemId` varchar(50) NOT NULL,
`quantity` double NOT NULL DEFAULT 1.0,
`quantityType` varchar(100) NOT NULL DEFAULT 'GALLON',
`timePeriod` double NOT NULL DEFAULT 1.0,
PRIMARY KEY (`id`),
UNIQUE INDEX `UNIQUE_RATE`
(`itemId` ASC, `consumerId` ASC)
) ENGINE=InnoDB AUTO_INCREMENT=314 DEFAULT CHARSET=utf8;


CREATE TABLE `myratehistory` (
`consumerId` varchar(255) DEFAULT NULL,
`durationType` varchar(50) DEFAULT NULL,
`itemId` varchar(50) NOT NULL,
`quantity` double DEFAULT NULL,
`quantityType` varchar(100) DEFAULT NULL,
`status` varchar(20) NOT NULL DEFAULT 'CREATED',
`timePeriod` double DEFAULT NULL,
`timestamp` DATETIME NULL,
PRIMARY KEY (`itemId`, `consumerId`, `timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=314 DEFAULT CHARSET=utf8;

CREATE TRIGGER `newRateToHistory`
AFTER INSERT
ON myrate
FOR EACH ROW

INSERT INTO myratehistory
(
consumerId,
durationType,
itemId,
quantity,
quantityType,
status,
timePeriod,
timestamp
)
VALUES(
new.consumerId,
new.durationType,
new.itemId,
new.quantity,
new.quantityType,
'CREATED',
new.timePeriod,
now());


Note that consumerId CAN be null.

Next I run this SQL statement:

INSERT INTO c_consumption.myrate (
consumerId,
durationType,
itemId,
quantity,
quantityType,
timePeriod)
VALUES(
null,
'DAY',
'MyItem',
1.0,
'GALLON',
1.0);


I get the following message:

Error Code: 1048 Column 'consumerId' cannot be null


Obviously I am doing something wrong but I do not know what it is. Any help would be greatly appreciated.

Thanks!

Answer Source

ConsumerId is part of the primary key. No part of the primary key can be null.