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

Error while removing the constraint

Details

    Description

      CREATE TABLE A (
      		 
        id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
      		 
      );
      		 
       
      		 
      CREATE TABLE B (
      		 
        what tinyint NOT NULL,
      		 
        a bigint NOT NULL,
      		 
        CONSTRAINT x FOREIGN KEY (a) REFERENCES A(id),
      		 
        CONSTRAINT y CHECK (what > 0)
      		 
      );
      		 
       
      		 
      -- Uncommenting the next line "fixes" an error
      		 
      -- INSERT INTO И VALUES (1,1);
      		 
       
      		 
      SELECT b.a FROM B b;
      		 
       
      		 
      -- the next statement produces an error
      		 
      -- Unknown column '`db_name`.`b`.`what`' in 'CHECK'
      		 
      ALTER TABLE B DROP CONSTRAINT x;

      online fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.3&rdbms2=mariadb_10.5&fiddle=eefa7abc9c4eab79dcef5e0c79e1e985

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25672 table alias from previous statement interferes later commands

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            Akina Владислав Сокол created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Description CREATE TABLE A (
              id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
            );

            CREATE TABLE B (
              what tinyint NOT NULL,
              a bigint NOT NULL,
              CONSTRAINT x FOREIGN KEY (a) REFERENCES A(id),
              CONSTRAINT y CHECK (what > 0)
            );

            -- Uncommenting the next line "fixes" an error
            -- INSERT INTO И VALUES (1,1);

            SELECT b.a FROM B b;

            -- the next statement produces an error
            -- Unknown column '`db_name`.`b`.`what`' in 'CHECK'
            ALTER TABLE B DROP CONSTRAINT x;

            -- online fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.3&rdbms2=mariadb_10.5&fiddle=eefa7abc9c4eab79dcef5e0c79e1e985             		{code:sql}
            CREATE TABLE A (
              id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
            );

            CREATE TABLE B (
              what tinyint NOT NULL,
              a bigint NOT NULL,
              CONSTRAINT x FOREIGN KEY (a) REFERENCES A(id),
              CONSTRAINT y CHECK (what > 0)
            );

            -- Uncommenting the next line "fixes" an error
            -- INSERT INTO И VALUES (1,1);

            SELECT b.a FROM B b;

            -- the next statement produces an error
            -- Unknown column '`db_name`.`b`.`what`' in 'CHECK'
            ALTER TABLE B DROP CONSTRAINT x;
            {code}
            online fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.3&rdbms2=mariadb_10.5&fiddle=eefa7abc9c4eab79dcef5e0c79e1e985
            serg Sergei Golubchik made changes -
            Issue Type Task [ 3 ] Bug [ 1 ]
            serg Sergei Golubchik made changes -
            Priority Minor [ 4 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s N/A [ 14700 ]
            Resolution Duplicate [ 3 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 122007 ] MariaDB v4 [ 159311 ]

            People

              Unassigned Unassigned
              Akina Владислав Сокол
              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.