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

Atomic CREATE OR REPLACE silently breaks the foreign key

    XMLWordPrintable

Details

    • Not for Release Notes
    • Q3/2026 Server Development, Q4/2026 Server Maintenance

    Description

      Atomic create or replace allows dropping a table referenced by a foreign key. Orphan rows remain, the reference changes to the temporary table, so the foreign key becomes broken.

      --source include/have_innodb.inc
       
      create table t1 (pk int primary key, a int) engine=InnoDB;
      create table t2 (pk int primary key, b int, foreign key(b) references t1(pk)) engine=InnoDB;
      insert into t1 values (1,10),(2,20);
      insert into t2 values (1,1),(2,2);
       
      create or replace table t1 (pk int primary key, a int) engine=InnoDB;
      select * from t1;
      select * from t2;
      show create table t2;
       
      drop table if exists t2, t1;
      

      bb-main-atomic 34b9dc4f4d5539b83baf56d7fd8e8afca532cf59

      create table t1 (pk int primary key, a int) engine=InnoDB;
      create table t2 (pk int primary key, b int, foreign key(b) references t1(pk)) engine=InnoDB;
      insert into t1 values (1,10),(2,20);
      insert into t2 values (1,1),(2,2);
      create or replace table t1 (pk int primary key, a int) engine=InnoDB;
      select * from t1;
      pk      a
      select * from t2;
      pk      b
      1       1
      2       2
      show create table t2;
      Table   Create Table
      t2      CREATE TABLE `t2` (
        `pk` int(11) NOT NULL,
        `b` int(11) DEFAULT NULL,
        PRIMARY KEY (`pk`),
        KEY `b` (`b`),
        CONSTRAINT `1` FOREIGN KEY (`b`) REFERENCES `#sql-create-2e1de2-4-10` (`pk`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0.65d Original Estimate - 0.65d
                  0.65d
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 1d 1.75h
                  1d 1.75h

                  Git Integration

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