Default null in column is not allowed by Mariadb database.

VRC1's profile image VRC1 posted 3 years ago in General Permalink

When I create a virtual column in the mariadb database, Heidisql only allows it to be created if I deselect the nullable option. However, in the CREATE CODE of the table it includes the default null for the column, which is not allowed by Mariadb. This creates a problem when exporting the SQL script or tries to change the table.

Description

ansgar's profile image ansgar posted 3 years ago Permalink

I cannot reproduce here. The table editor allows me to set the virtuality and expression settings for a column, regardless of the "allow null" setting.

Probably send a screenshot?

VRC1's profile image VRC1 posted 3 years ago Permalink

Thank You Ansgar for the answer.

So, when I was creating this table I had a problem. Heidisql always sets "allow null", including in the create table script.

Description

Description

If I trim the row and add it again by changing the expression, and I try to run the generated script, it has an error.

Description

Description

if I remove the "allow null" attribute, it works perfectly, but then it goes back to the script. Description

Description

Default null again Description

ansgar's profile image ansgar posted 3 years ago Permalink

Now I could reproduce that. And I found the "NOT NULL" or "NULL" clause is suppressed by HeidiSQL, since I implemented virtual column in 2011: https://sourceforge.net/p/heidisql/tickets/2451/#4d07

Related: commit:98c872909cf2f0e601d44ed771e214195ca28d3a

I'm nearly sure I did that intentionally. Probably virtual columns do not support "NOT NULL"? We will need to consult the documentation.

VRC1's profile image VRC1 posted 3 years ago Permalink

Yes, virtual columns do not support "DEFAULT NULL" in "CREATE TABLE" scripts. See this screenshot of Mariadb docs,

Description

ansgar's profile image ansgar posted 3 years ago Permalink

Ah, yes that's it. So HeidiSQL should probably disallow checking the "Allow null" checkbox, or?

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