How can I accomplish this in a schema?
I'm trying to make it so only one record with this itemId can have the status "ACTIVE" at a time. Something like this:
ALTER TABLE ItemMintPermission
ADD COLUMN ActiveItemID AS (CASE WHEN status='ACTIVE' THEN itemId ELSE NULL END) VIRTUAL;
ALTER TABLE ItemMintPermission
ADD UNIQUE INDEX idx_unique_active_item(ActiveItemID);
8 Replies
its mysql btw
There are hacky ways to do generated columns, like I show here: https://discord.com/channels/1043890932593987624/1156712008893354084/1156716755125796934
Basically you put whatever you want the SQL to have after the column name in the
dataType methodseems to not like virtual keyword
isn't it virtual by default? At least in Mysql it is
so I can exclude that keyword, im not 100% sure on that.
The default is VIRTUAL if neither keyword is specified.https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html
I keep getting this error:
code: 'ER_PARSE_ERROR',
errno: 1064,
sql: "ALTER TABLE
ItemMintPermission ADD activeItemId AS (CASE WHEN status='ACTIVE' THEN itemId ELSE NULL END);",
but the sql looks right to meI think you're missing the type: https://dev.mysql.com/doc/refman/8.0/en/alter-table-generated-columns.html