Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6
-
None
Description
When a column has the NOT NULL attribute, you can't remove the default value for that column.
Example 1, default value, accepts null values. This works as expected:
show create table innodb_default_test; |
CREATE TABLE `innodb_default_test` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`start_time` time DEFAULT '00:00:00', |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
|
|
alter innodb_default_test alter column start_time drop default; |
Query OK, 0 rows affected (0.013 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
show create table innodb_default_test; |
CREATE TABLE `innodb_default_test` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`start_time` time, ## default is removed |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
Example 2, default values, not null. This fails silently:
MariaDB [test]> CREATE TABLE `t1` ( |
-> `col1` int(11) NOT NULL, |
-> `col2` datetime NOT NULL DEFAULT current_timestamp() |
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci; |
Query OK, 0 rows affected (0.001 sec) |
|
|
MariaDB [test]> desc t1; |
+-------+----------+------+-----+---------------------+-------+ |
| Field | Type | Null | Key | Default | Extra | |
+-------+----------+------+-----+---------------------+-------+ |
| col1 | int(11) | NO | | NULL | | |
| col2 | datetime | NO | | current_timestamp() | | |
+-------+----------+------+-----+---------------------+-------+ |
2 rows in set (0.001 sec) |
|
|
MariaDB [test]> alter table test.t1 alter column col2 drop default; |
Query OK, 0 rows affected (0.001 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> desc t1; |
+-------+----------+------+-----+---------------------+-------+ |
| Field | Type | Null | Key | Default | Extra | |
+-------+----------+------+-----+---------------------+-------+ |
| col1 | int(11) | NO | | NULL | | |
| col2 | datetime | NO | | current_timestamp() | | |
+-------+----------+------+-----+---------------------+-------+ |
2 rows in set (0.001 sec) |
Example 3: Remove NOT NULL, then remove default. This works:
show create table test1; |
CREATE TABLE `test1` ( |
`col1` int(11) NOT NULL, |
`col2` datetime NOT NULL DEFAULT current_timestamp() |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci |
|
|
ALTER TABLE `test1` MODIFY COLUMN `col2` DATETIME NULL; |
Query OK, 0 rows affected (0.018 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
show create table test1; |
CREATE TABLE `test1` ( |
`col1` int(11) NOT NULL, |
`col2` datetime DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci |
|
|
alter table test1 alter column col2 drop default; |
Query OK, 0 rows affected (0.012 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
show create table test1; |
CREATE TABLE `test1` ( |
`col1` int(11) NOT NULL, |
`col2` datetime
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci |