Allow changing the primary key from an signed integer type to an unsigned integer type

[dveeden]

Feature Request

Primary key columns are often defined as int or bigint, which by default are signed. As negative values are often not used and growing out of an integer type is common it would be good to allow the PK to change from int to int unsigned and from bigint to bigint unsigned.

Changing this for smaller integer types is less important ans rebuilding smaller tables is often less of an issue.

Currently the user has to rebuild the table by creating a new table, copying data and then switching tables. Doing this without downtime is often difficult.

If changing an int to bigint is also possible this would be very helpful as well.

Current behaviour:

sql> CREATE TABLE t1(id bigint PRIMARY KEY, c1 bigint);
Query OK, 0 rows affected (0.0153 sec)

sql> INSERT INTO t1 VALUES(1,1),(1000,1000);
Query OK, 2 rows affected (0.0022 sec)

Records: 2  Duplicates: 0  Warnings: 0

sql> ALTER TABLE t1 MODIFY COLUMN id bigint unsigned;
ERROR: 8200 (HY000): Unsupported modify column: this column has primary key flag

Suggested behaviour:

sql> CREATE TABLE t1(id bigint PRIMARY KEY, c1 bigint);
Query OK, 0 rows affected (0.0316 sec)

sql> INSERT INTO t1 VALUES(1,1),(1000,1000);
Query OK, 2 rows affected (0.0017 sec)

Records: 2  Duplicates: 0  Warnings: 0

sql> TABLE t1;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
| 1000 | 1000 |
+------+------+
2 rows in set (0.0018 sec)

sql> ALTER TABLE t1 MODIFY COLUMN id bigint unsigned;
Query OK, 0 rows affected (2.5810 sec)

sql> TABLE t1;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
| 1000 | 1000 |
+------+------+
2 rows in set (0.0020 sec)

sql> CREATE TABLE t2(id bigint PRIMARY KEY, c1 bigint);
Query OK, 0 rows affected (0.0154 sec)

sql> INSERT INTO t2 VALUES(1,1),(1000,1000),(-1,-1);
Query OK, 3 rows affected (0.0017 sec)

Records: 3  Duplicates: 0  Warnings: 0

sql> TABLE t2;
+------+------+
| id   | c1   |
+------+------+
|   -1 |   -1 |
|    1 |    1 |
| 1000 | 1000 |
+------+------+
3 rows in set (0.0017 sec)

sql> ALTER TABLE t2 MODIFY COLUMN id bigint unsigned;
ERROR: 1690 (22003): constant -1 overflows bigint

sql> CREATE TABLE t3(id int PRIMARY KEY, c1 bigint);
Query OK, 0 rows affected (0.0274 sec)

sql> INSERT INTO t3 VALUES(1,1),(1000,1000);
Query OK, 2 rows affected (0.0017 sec)

Records: 2  Duplicates: 0  Warnings: 0

sql> TABLE t3;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
| 1000 | 1000 |
+------+------+
2 rows in set (0.0017 sec)

sql> ALTER TABLE t3 MODIFY COLUMN id bigint unsigned;
Query OK, 0 rows affected (2.5665 sec)

sql> TABLE t3;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
| 1000 | 1000 |
+------+------+
2 rows in set (0.0019 sec)

The suggested behaviour works if I sabotage the check in TiDB. I only tried with the unistore and without testing indexes etc.

diff --git a/ddl/column.go b/ddl/column.go
index 6beba60a3..d1d73e262 100644
--- a/ddl/column.go
+++ b/ddl/column.go
@@ -552,11 +552,13 @@ func (w *worker) onModifyColumn(d *ddlCtx, t *meta.Meta, job *model.Job) (ver in
        changingCol := modifyInfo.changingCol
        if changingCol == nil {
                newColName := model.NewCIStr(genChangingColumnUniqueName(tblInfo, oldCol))
-               if mysql.HasPriKeyFlag(oldCol.GetFlag()) {
-                       job.State = model.JobStateCancelled
-                       msg := "this column has primary key flag"
-                       return ver, dbterror.ErrUnsupportedModifyColumn.GenWithStackByArgs(msg)
-               }
+               /*
+                       if mysql.HasPriKeyFlag(oldCol.GetFlag()) {
+                               job.State = model.JobStateCancelled
+                               msg := "this column has primary key flag"
+                               return ver, dbterror.ErrUnsupportedModifyColumn.GenWithStackByArgs(msg)
+                       }
+               */
 
                changingCol = modifyInfo.newCol.Clone()
                changingCol.Name = newColName
diff --git a/ddl/ddl_api.go b/ddl/ddl_api.go
index 153567e2c..4d8e59c6c 100644
--- a/ddl/ddl_api.go
+++ b/ddl/ddl_api.go
@@ -4238,10 +4238,12 @@ func checkModifyTypes(ctx sessionctx.Context, origin *types.FieldType, to *types
                if !canReorg {
                        return errors.Trace(dbterror.ErrUnsupportedModifyColumn.GenWithStackByArgs(err.Error()))
                }
-               if mysql.HasPriKeyFlag(origin.GetFlag()) {
-                       msg := "this column has primary key flag"
-                       return dbterror.ErrUnsupportedModifyColumn.GenWithStackByArgs(msg)
-               }
+               /*
+                       if mysql.HasPriKeyFlag(origin.GetFlag()) {
+                               msg := "this column has primary key flag"
+                               return dbterror.ErrUnsupportedModifyColumn.GenWithStackByArgs(msg)
+                       }
+               */
        }
 
        err = checkModifyCharsetAndCollation(to.GetCharset(), to.GetCollate(), origin.GetCharset(), origin.GetCollate(), needRewriteCollationData)