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

Cannot drop column referenced by CHECK constraint

Details

    Description

      If a column check is defined as a separate constraint (not as a part of a column definition), the column cannot be dropped:

      MariaDB [test]> create table t1 (a int, b int, check(a>0));
      		 
      Query OK, 0 rows affected (0.63 sec)
      		 
       
      		 
      MariaDB [test]> show create table t1 \G
      		 
      *************************** 1. row ***************************
      		 
             Table: t1
      		 
      Create Table: CREATE TABLE `t1` (
      		 
        `a` int(11) DEFAULT NULL,
      		 
        `b` int(11) DEFAULT NULL,
      		 
        CONSTRAINT `CONSTRAINT_1` CHECK (a>0)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [test]> alter table t1 drop column a;
      		 
      ERROR 1054 (42S22): Unknown column 'a' in 'virtual column function'

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14738 Replication breaks when dropping a column of a combined primary key (with a confusing error message)

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Confirmed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13508 ALTER TABLE that renames columns and CHECK constraints

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-7563 Support CHECK constraint as in (or close to) SQL Standard

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13613 Dropping column from table that is part of a key fails in 10.2.8

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21097 Incorrect error message when trying to delete column which is part of unique key constraint

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24690 Dropping primary key column from versioned table always fails with 1072

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 4 older comments
            zzzeek Mike Bayer added a comment -

            downstream SQLAlchemy / Alembic issue: https://bitbucket.org/zzzeek/alembic/issues/440/cannot-drop-boolean-column-in-mysql

            zzzeek Mike Bayer added a comment - downstream SQLAlchemy / Alembic issue: https://bitbucket.org/zzzeek/alembic/issues/440/cannot-drop-boolean-column-in-mysql
            serg Sergei Golubchik added a comment -

            Okay. SQL Standard says:

            11.23 <drop column definition>
            ...
            7) If RESTRICT is specified, then C shall not be referenced in any of the following:
            ...
            b) The <search condition> of any constraint descriptor other than a table constraint descriptor that contains
            references to no other column and that is included in the table descriptor of T.

            Which means that DROP COLUMN xxx RESTRICT should automatically drop constraints that only refer to this one column, but should fail if there are constraints that refer to this and other columns. I suspect that DROP COLUMN xxx CASCADE should drop all constraints that refer to this column.

            Either way, dropping a column in the original test case should succeed.

            In the standard RESTRICT/CASCADE is not optional. We don't have that, so let's do RESTRICT.

            serg Sergei Golubchik added a comment - Okay. SQL Standard says: 11.23 <drop column definition> ... 7) If RESTRICT is specified, then C shall not be referenced in any of the following: ... b) The <search condition> of any constraint descriptor other than a table constraint descriptor that contains references to no other column and that is included in the table descriptor of T. Which means that DROP COLUMN xxx RESTRICT should automatically drop constraints that only refer to this one column, but should fail if there are constraints that refer to this and other columns. I suspect that DROP COLUMN xxx CASCADE should drop all constraints that refer to this column. Either way, dropping a column in the original test case should succeed. In the standard RESTRICT / CASCADE is not optional. We don't have that, so let's do RESTRICT .
            serg Sergei Golubchik added a comment - - edited

            Assuming 

            create table t1 (a int, b int, check(a>0));

            If one does

            alter table t1 drop column a;

            it's clear that the constraint should be dropped. What should be the effect of

            1

            		 
            alter table t1 drop column a, add column a bigint;

            Two possible outcomes here: drop the constraint, keep the constraint.

            2

            		 
            alter table t1 change column a c int;

            Three possible outcomes here: drop the constraint, change the constraint, error.

            3

            		 
            alter table t1 change column a c int, change column b a int;

            Two possible outcomes here: change the constraint, keep the constraint as it was.

            serg Sergei Golubchik added a comment - - edited Assuming create table t1 (a int , b int , check (a>0)); If one does alter table t1 drop column a; it's clear that the constraint should be dropped. What should be the effect of 1 alter table t1 drop column a, add column a bigint ; Two possible outcomes here: drop the constraint, keep the constraint. 2 alter table t1 change column a c int ; Three possible outcomes here: drop the constraint, change the constraint, error. 3 alter table t1 change column a c int , change column b a int ; Two possible outcomes here: change the constraint, keep the constraint as it was.
            serg Sergei Golubchik added a comment -

            let's do 1: drop, 2: change, 3: change.

            Comparing what the behavior for indexes, indexes are changed in 2 and 3, and in 1 the behavior is buggy and inconsistent, I'll fix it to drop.

            serg Sergei Golubchik added a comment - let's do 1: drop, 2: change, 3: change. Comparing what the behavior for indexes, indexes are changed in 2 and 3, and in 1 the behavior is buggy and inconsistent, I'll fix it to drop.
            serg Sergei Golubchik added a comment -

            The issue with renaming columns was moved to MDEV-13508

            serg Sergei Golubchik added a comment - The issue with renaming columns was moved to MDEV-13508

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.