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

incorrect error message: Cannot delete rows from table which is parent in a foreign key constraint

    XMLWordPrintable

Details

    Description

      CREATE TABLE `Tab1` (
        `T1Id` int(11) NOT NULL,
        `T1ExtId` varchar(60) NOT NULL,
        PRIMARY KEY (`T1Id`),
        UNIQUE KEY `UIdxTab1T1ExtId` (`T1ExtId`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
       
      CREATE TABLE `Tab3` (
        `T3Id` int(11) NOT NULL,
        `T3Str` varchar(60) NOT NULL,
        `T3Num` int(11) NOT NULL,
        `T1Id` int(11) NOT NULL,
        PRIMARY KEY (`T3Id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
       
      CREATE TABLE `Tab4` (
        `T4Id` int(11) NOT NULL,
        `T4Str` varchar(60) NOT NULL,
        `T3Id` int(11) NOT NULL,
        PRIMARY KEY (`T4Id`),
        KEY `FkTab4T3Id` (`T3Id`),
        CONSTRAINT `FkTab4T3Id` FOREIGN KEY (`T3Id`) REFERENCES `Tab3` (`T3Id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

      insert some data

      insert into Tab1 (T1Id, T1ExtId) values (1, 'one'), (2, 'two');
      insert into Tab3 (T3Id, T3Str, T3Num, T1Id) values (1, 'one', 1, 1), (2, 'two', 11, 2), (3, 'three', 1111, 1), (4, 'four', 11111, 2);
       
      insert into Tab3 (T3Id, T3Str, T3Num, T1Id) values (5, 'one', 1, 1);

      adding foreign key constraint and unique index in one statement

      alter table Tab3 
      add constraint FkTab3T1Id foreign key (T1Id) references Tab1 (T1Id),
      add unique index UIdxTab3T3Str (T3Str);

      My last insert is a dupplicate value "one" in col T3Str

      But error message is

      Cannot delete rows from table which is parent in a foreign key constraint 'FkTab4T3Id' of table 'Tab4'

      alter statement seperates in two statements

      alter table Tab3 add constraint FkTab3T1Id foreign key (T1Id) references Tab1 (T1Id);
      alter table Tab3 add unique index UIdxTab3T3Str (T3Str);

      brings a correkt error message

      Duplicate entry 'one' for key 'UIdxTab3T3Str'

      If foreign key contraint Tab4 -> Tab3.T3Id is not present, the error message is correkt with combined alter statement with foreign key and unique index.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              svh Stefan van Hasselt
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.