[MDEV-28160] Create table accepts redundant UNIQUE and PRIMARY KEY syntax Created: 2022-03-23  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Trivial
Reporter: Alice Sherepa Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

MariaDB syntax allows any combination of UNIQUE and primary key.
And if both are present in that column definition, then only primary key index is created:

MariaDB [test]> CREATE or replace TABLE t1 ( i INT primary key UNIQUE UNIQUE  UNIQUE primary key unique );
 
Query OK, 0 rows affected (0.09 sec)
 
 
 
MariaDB [test]> show create table t1;
 
+-------+--------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                           |
 
+-------+--------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `i` int(11) NOT NULL,
 
  PRIMARY KEY (`i`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+--------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> CREATE or replace TABLE t1 ( i INT UNIQUE UNIQUE  UNIQUE);
 
Query OK, 0 rows affected (0.10 sec)
 
 
 
MariaDB [test]> show create table t1;
 
+-------+---------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                  |
 
+-------+---------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `i` int(11) DEFAULT NULL,
 
  UNIQUE KEY `i` (`i`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+---------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

mysql 5.6-8.0 creates in such case both unique + primary indexes:

CREATE TABLE `t1` ( `i` int(11) NOT NULL, PRIMARY KEY (`i`), UNIQUE KEY `i` (`i`) ) ENGINE=InnoDB



 Comments   
Comment by Sergei Golubchik [ 2022-03-30 ]

what about UNIQUE UNIQUE? The standard says that one can use either PRIMARY KEY or UNIQUE, and only once, it's how the syntax is defined. So, it should be a syntax error, I support

Comment by Sergei Golubchik [ 2022-03-31 ]

You cannot do a deprecated warning for a syntax error. At least I don't see how it's possible.
So, basically, either we do the standard syntax, and UNIQUE UNIQUE will be a syntax error,
or we declare it harmless and let it be.

Comment by Alexander Barkov [ 2022-04-01 ]

I think we can do a deprecated syntax warning if we do this way:

don't change the grammar, add a parse-time evaluation to catch redundant options.

Like we do in other places where ER_CONFLICTING_DECLARATIONS is issued.

Comment by Sergei Golubchik [ 2022-04-07 ]

what's the point of issuing a deprecated syntax warning if we aren't going to actually remove this syntax later?

it's not nice, but it doesn't cause any problems for anyone, so there isn't a good reason to remove the syntax and break existing applications, as far as I can see

Comment by Alexander Barkov [ 2022-04-08 ]

I meant, we could issue a warning in versions between 10.2 to 10.8, then remove this duplicate syntax in 10.9.

Generated at Thu Feb 08 09:58:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.