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);

focused_morningOP•10/31/23, 5:27 PM

its mysql btw

Angelelz•10/31/23, 5:27 PM

There are hacky ways to do generated columns, like I show here: How to set a default value for column based on another column?

Angelelz•10/31/23, 5:29 PM

Basically you put whatever you want the SQL to have after the column name in the method

focused_morningOP•10/31/23, 7:06 PM

seems to not like virtual keyword

Angelelz•10/31/23, 7:07 PM

isn't it virtual by default? At least in Mysql it is

focused_morningOP•10/31/23, 7:07 PM

so I can exclude that keyword, im not 100% sure on that.

Angelelz•10/31/23, 7:08 PM

The default is VIRTUAL if neither keyword is specified.

https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html

focused_morningOP•10/31/23, 7:17 PM

I keep getting this error:

code: 'ER_PARSE_ERROR',
errno: 1064,
sql: "ALTER TABLE ADD AS (CASE WHEN status='ACTIVE' THEN itemId ELSE NULL END);",

but the sql looks right to me

Angelelz•10/31/23, 7:21 PM

I think you're missing the type: https://dev.mysql.com/doc/refman/8.0/en/alter-table-generated-columns.html