Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.

Dropping the default value doesn't change the default

TheColorRed's profile image TheColorRed posted 2 weeks ago in General

Version 10.2.0.5599 (32 Bit)

It seems as if when a column is changed from NULL to No Default Value it runs a query to set it to NULL instead of just removing the default.

Here is what gets executed when trying to change it:

ALTER TABLE `game_store`
    ALTER `game_id` DROP DEFAULT;

ALTER TABLE `game_store`
    CHANGE COLUMN `game_id` `game_id` INT(10) UNSIGNED NULL AFTER `id`;
ansgar's profile image ansgar posted 1 week ago

Yes, that DROP DEFAULT clause is packed into an extra query, as MySQL does not support doing it in an ALTER TABLE ... CHANGE COLUMN ... query. Normally the latter one contains all changes you make on a column. Also, the latter one is fired on all modifications, and HeidiSQL is not able to determine whether there are more modifications or not.

Does that extra query produce some problem?

TheColorRed's profile image TheColorRed posted 1 week ago

There isn't a huge problem, just that I cannot drop the default value when modifying the table.



Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.
ansgar's profile image ansgar posted 1 week ago

You cannot drop the default value? The first query should exactly do that, and the second one does not contain a new default. That NULL in the second one means: this column allows NULL values.

TheColorRed's profile image TheColorRed posted 1 week ago

Here is a gif showing what is happening if this helps

1 attachment(s):
  • can-not-set-no-default
TheColorRed's profile image TheColorRed posted 1 week ago

Here are all the commands that were executed:

ALTER TABLE `users`
    ALTER `first` DROP DEFAULT;

ALTER TABLE `users`
    CHANGE COLUMN `first` `first` VARCHAR(50) NULL AFTER `id`;

SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test';

SHOW TABLE STATUS FROM `test`;

SHOW FUNCTION STATUS WHERE `Db`='test';

SHOW PROCEDURE STATUS WHERE `Db`='test';

SHOW TRIGGERS FROM `test`;

SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='test';

SHOW CREATE TABLE `test`.`users`;

/* Entering session "Localhost\Local" */
SHOW CREATE TABLE `test`.`users`;
TheColorRed's profile image TheColorRed posted 1 week ago

Also, this used to work in an older version.

ansgar's profile image ansgar posted 1 week ago

Indeed, the server adds that DEFAULT NULL again with the second ALTER query, although it does not contain such a clause.

But the documentation is quite clear here: default column value will be null anyway, even after dropping a default value with such a query:

ALTER TABLE `users` ALTER `first` DROP DEFAULT

https://mariadb.com/kb/en/library/create-table/#default-column-option

Specify a default value using the DEFAULT clause. If you don't specify DEFAULT then the following rules apply:

If the column is not defined with NOT NULL, AUTO_INCREMENT or TIMESTAMP, an explicit DEFAULT NULL will be added. Note that in MySQL and in MariaDB before 10.1.6, you may get an explicit DEFAULT for primary key parts, if not specified with NOT NULL.

TheColorRed's profile image TheColorRed posted 1 week ago

Okay, I guess I didn't know that no default is basically the same as NULL:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

Please login to leave a reply, or register at first.