Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26846

SQL Error: Field 'DB_ROW_HASH_1' doesn't have a default value

    XMLWordPrintable

Details

    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

          Activity

            People

              Unassigned Unassigned
              ExLuzZziVo Sergey Khatin
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.