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

ALTER TABLE DROP CONSTRAINT does not work for unique constraints

Details

    Description

      The syntax is supported in MariaDB 10.2.1 (https://mariadb.com/kb/en/library/alter-table/#drop-constraint), but it doesn't work for unique constraints:

      create table t (
      		 
        a int,
      		 
        b int,
      		 
        
        constraint x unique (a),
      		 
        constraint y check (b = 1)
      		 
      );
      		 
       
      		 
      -- Does not work:
      		 
      alter table t drop constraint x;
      		 
       
      		 
      -- These work:
      		 
      alter table t drop key x;
      		 
      alter table t drop constraint y;

      Note, this looks like a duplicate of MDEV-10038, but at the time of MDEV-10038, the syntax wasn't yet supported by MariaDB. Now it is. Feel free to close this as a duplicate of the other (and reopen the other), if you think that's the better approach.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10038 ALTER TABLE DROP CONSTRAINT not working for unique constraints

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            As discussed, alter table t drop constraint x should work for a unique key x.

            elenst Elena Stepanova added a comment - As discussed, alter table t drop constraint x should work for a unique key x .
            rucha174 Rucha Deodhar added a comment - - edited

            Works on 10.2:

            MariaDB [test]> create table t (
            		 
                ->   a int,
            		 
                ->   b int,
            		 
                ->   
                ->   constraint x unique (a),
            		 
                ->   constraint y check (b = 1)
            		 
                -> );
            		 
            Query OK, 0 rows affected (0.00 sec)
            		 
             
            		 
            MariaDB [test]> INSERT INTO t VALUES(1,1);
            		 
            Query OK, 1 row affected (0.00 sec)
            		 
             
            		 
            MariaDB [test]> INSERT INTO t VALUES(1,2);
            		 
            ERROR 4025 (23000): CONSTRAINT `y` failed for `test`.`t`
            		 
            MariaDB [test]> INSERT INTO t VALUES(2,1);
            		 
            Query OK, 1 row affected (0.00 sec)
            		 
             
            		 
            MariaDB [test]> INSERT INTO t VALUES(2,1);
            		 
            ERROR 1062 (23000): Duplicate entry '2' for key 'x'
            		 
            MariaDB [test]> ALTER TABLE t DROP CONSTRAINT x;
            		 
            Query OK, 2 rows affected (0.01 sec)               
            Records: 2  Duplicates: 0  Warnings: 0
            		 
             
            		 
            MariaDB [test]> INSERT INTO t VALUES(2,1);
            		 
            Query OK, 1 row affected (0.00 sec)
            		 
             
            		 
            MariaDB [test]> SELECT * FROM t;
            		 
            +------+------+
            		 
            | a    | b    |
            		 
            +------+------+
            		 
            |    1 |    1 |
            		 
            |    2 |    1 |
            		 
            |    2 |    1 |
            		 
            +------+------+
            		 
            3 rows in set (0.00 sec)

            rucha174 Rucha Deodhar added a comment - - edited Works on 10.2: MariaDB [test]> create table t ( -> a int, -> b int, -> -> constraint x unique (a), -> constraint y check (b = 1) -> ); Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> INSERT INTO t VALUES(1,1); Query OK, 1 row affected (0.00 sec)   MariaDB [test]> INSERT INTO t VALUES(1,2); ERROR 4025 (23000): CONSTRAINT `y` failed for `test`.`t` MariaDB [test]> INSERT INTO t VALUES(2,1); Query OK, 1 row affected (0.00 sec)   MariaDB [test]> INSERT INTO t VALUES(2,1); ERROR 1062 (23000): Duplicate entry '2' for key 'x' MariaDB [test]> ALTER TABLE t DROP CONSTRAINT x; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0   MariaDB [test]> INSERT INTO t VALUES(2,1); Query OK, 1 row affected (0.00 sec)   MariaDB [test]> SELECT * FROM t; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 1 | | 2 | 1 | +------+------+ 3 rows in set (0.00 sec)
            rucha174 Rucha Deodhar added a comment -

            Looks like this is already fixed.

            rucha174 Rucha Deodhar added a comment - Looks like this is already fixed.

            People

              rucha174 Rucha Deodhar
              lukas.eder Lukas Eder
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.