Error should be "function m5hash does not exists" not "function m5hash on column id is not valid for usage in a default value

[subhra78]

Hi, is there any way to auto generate row ID like using m5hash or anything? So, per entry I will keep the id column blank and it should auto generate when the row is inserted.
Like I tried this query but it gives error

CREATE TABLE your_table_name (
    id VARCHAR(37) NOT NULL PRIMARY KEY DEFAULT CONCAT('MS001', m5hash(name)),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
); 

[timsehn]

This should work.

Reproed

$ dolt init --fun
Successfully initialized dolt data repository.
$ dolt sql -q "CREATE TABLE your_table_name (
    id VARCHAR(37) NOT NULL PRIMARY KEY DEFAULT CONCAT('MS001', m5hash(name)),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)"
Error parsing SQL
syntax error at position 86 near 'CONCAT'
CREATE TABLE your_table_name (
    id VARCHAR(37) NOT NULL PRIMARY KEY DEFAULT CONCAT('MS001', m5hash(name)),
                                                       ^
syntax error at position 86 near 'CONCAT'

So you need parentheses around default expressions. So the proper query is

CREATE TABLE your_table_name (
    id VARCHAR(37) NOT NULL PRIMARY KEY DEFAULT(CONCAT('MS001', m5hash(name))),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

But that also fails:

$ dolt sql -q "CREATE TABLE your_table_name (
    id VARCHAR(37) NOT NULL PRIMARY KEY DEFAULT(CONCAT('MS001', m5hash(name))),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)"
error on line 1 for query CREATE TABLE your_table_name (
    id VARCHAR(37) NOT NULL PRIMARY KEY DEFAULT(CONCAT('MS001', m5hash(name))),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
): function `m5hash` on column `id` is not valid for usage in a default value
function `m5hash` on column `id` is not valid for usage in a default value

[fulghum]

@subhra78 – the md5 hash function in mysql is md5. I swapped that function name in the query below and confirmed that it works on Dolt:

CREATE TABLE your_table_name ( 
    id VARCHAR(37) NOT NULL PRIMARY KEY DEFAULT(CONCAT('MS001', md5(name))),
    name VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

That should give you what you're looking for. Let us know if you hit any other snags and we're always happy to help.

[timsehn]

I re-opened because the error message is bad. We should fix that.

But @subhra78 you should be good to go now.

[subhra78]

Thank you so much. It worked. Really apprentice the help.