Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.6.4, 10.4(EOL), 10.5, 10.6
-
Windows 10 Pro x64 20H2
Description
There are two tables and a trigger that generates the value of one of the columns of the second table before inserting a new row:
CREATE TABLE `table1` ( |
`Id` char(36) NOT NULL, |
PRIMARY KEY (`Id`), |
UNIQUE KEY `Id_UN` (`Id`) |
);
|
|
CREATE TABLE `table2` ( |
`Id` int(11) NOT NULL AUTO_INCREMENT, |
`Index` int(11) NOT NULL, |
`Value` varchar(1024) NOT NULL, |
`FirstTableId` char(36) NOT NULL, |
PRIMARY KEY (`Id`), |
UNIQUE KEY `Id_UN` (`Id`), |
UNIQUE KEY `Value_UN` (`Value`,`FirstTableId`) USING HASH, |
KEY `IX_FK_Table1Table2` (`FirstTableId`), |
CONSTRAINT `FK_Table1Table2` FOREIGN KEY (`FirstTableId`) REFERENCES `table1` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION |
);
|
|
DELIMITER ;;
|
|
CREATE TRIGGER `OnTable2BeforeInsert` |
BEFORE INSERT |
ON table2 FOR EACH ROW |
BEGIN
|
IF(SELECT COUNT(*) FROM `table2` WHERE `FirstTableId` = NEW.`FirstTableId`) = 0 THEN |
SET NEW.`Index` = 1; |
ELSE |
SET NEW.`Index` = (SELECT MAX(`Index`) FROM `table2` WHERE `FirstTableId` = NEW.`FirstTableId`)+1; |
END IF; |
END
|
|
;;
|
|
An error occurs when inserting a row into the second table:
|
INSERT INTO table1 (Id) VALUES ('7a4b8cc2-913b-43a9-ac3c-d9afb46fde98'); |
INSERT INTO table2 (Value, FirstTableId)VALUES ('123', '7a4b8cc2-913b-43a9-ac3c-d9afb46fde98'); |
|
Error: SQL Error [1364] [HY000]: (conn=37) Field 'DB_ROW_HASH_1' doesn't have a default value
Attachments
Issue Links
- duplicates
-
MDEV-22756 SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value
- Closed