I'm trying to alter an existing columns default value from 'no default value at all' to 'tomorrow's date' on the DBMS-side.
More specific:
By inserting a data-row into my table, I wanna have by default the date of tomorrow in a column (at timestamp of the insert).
Used tools:
My general SQL-command for initiate altering of my column is:
ALTER TABLE test
CHANGE COLUMN tomorrow
tomorrow date not null default (EVIL-EXPRESSION);
'EVIL-EXPRESSION' in the code-sample above is just a placeholder for following possibilities:
default (date_add(curdate(), interval 1 day))
or
default (adddate(current_date(), 1))
or
default (now() + interval 1 day)
or
default (today + interval 1 day)
# today is a column declared before actual column 'tomorrow'
And some other variations/alias with the same error code result:
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version
for the right syntax to use near '(date_add(curdate(), interval 1 day))'
at line 1
Due to goolge this error number '1064 (42000)' indicates a parenthesis mismatch. I'm pretty sure, that this is not the case here. And when it is, then I need definitely holidays. ;)
Due to the official MariaDB documentation, expressions are allowed in the default statement since version 10.2+.
Also this article enthuses this feature - with a not working example for me (with the 'alter table'-statement). Scroll down until "The DEFAULT Clause" section.
Even evil characters can't be blamed for my error like this genius pointed out.
Maybe a bug of MariaDB?
And sure, I can and actually do a workaround on the server-site PHP script without any default value. But I'm still interested to outsource it to the database for more comfort - one-stop-service. ;)
I'm thankful for every input, so let the brainstorming begin - since my brain is smoking. ;)
Check CREATE TABLE::DEFAULT. Verify your version of MariaDB.
Test:
MariaDB [_]> SELECT VERSION();
+-------------------------+
| VERSION() |
+-------------------------+
| 10.3.8-MariaDB-1:10.3.8 |
+-------------------------+
1 row in set (0.000 sec)
MariaDB [_]> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected (0.001 sec)
MariaDB [_]> CREATE TABLE IF NOT EXISTS `test` (
-> `id` SERIAL,
-> `today` DATE NOT NULL DEFAULT CURRENT_DATE,
-> `tomorrow` DATE
-> );
Query OK, 0 rows affected (0.001 sec)
MariaDB [_]> DESC `test`\G
*************************** 1. row ***************************
Field: id
Type: bigint(20) unsigned
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: today
Type: date
Null: NO
Key:
Default: curdate()
Extra:
*************************** 3. row ***************************
Field: tomorrow
Type: date
Null: YES
Key:
Default: NULL
Extra:
3 rows in set (0.001 sec)
MariaDB [_]> ALTER TABLE `test`
-> CHANGE COLUMN `tomorrow`
-> `tomorrow` DATE NOT NULL DEFAULT (`today` + INTERVAL 1 DAY);
Query OK, 0 rows affected (0.004 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [_]> DESC `test`\G
*************************** 1. row ***************************
Field: id
Type: bigint(20) unsigned
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: today
Type: date
Null: NO
Key:
Default: curdate()
Extra:
*************************** 3. row ***************************
Field: tomorrow
Type: date
Null: NO
Key:
Default: (`today` + interval 1 day)
Extra:
3 rows in set (0.001 sec)
MariaDB [_]> INSERT INTO `test` (`id`) SELECT NULL;
Query OK, 1 row affected (0.000 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [_]> SELECT
-> `id`,
-> `today`,
-> `tomorrow`
-> FROM
-> `test`;
+----+------------+------------+
| id | today | tomorrow |
+----+------------+------------+
| 1 | 2000-01-01 | 2000-01-02 |
+----+------------+------------+
1 row in set (0.000 sec)