how do i increment an id based on an enum value

Question

I have a table for users that includes a default user_id column. Additionally, there is a donator_id column that is null by default, but I want it to auto-increment in case the user_type is set to "donator". Can someone help me achieve this? **this is the expected results, I wanted a way to make "donator_id" only count users of type donator if else it should be null.

| user_id | name     | user_type | donator_id|
| ------- | -------- | --------- | ----------|
| 1       | user     | user      | null      |
| 2       | donator  | donator   | 1         |
| 3       | user2    | user      | null      |
| 4       | donator2 | donator   | 2         |

Answer 1

as barmar said a restructring of your table, will helpo you keeptrack, ou can update all tabales and add row when needed

CREATE TABLE user_type 
    (`user_type` varchar(9), `usertype_id` int PRIMARY KEY )
;
    
INSERT INTO user_type 
    (`user_type`, `usertype_id`)
VALUES
    ('user', '1'),
    ('donator', '2')
;

CREATE TABLE users
    (`user_id` varchar(7), `name` varchar(8), `id_user_type` int,
    FOREIGN KEY (id_user_type)
        REFERENCES user_type(usertype_id)
        ON Update CASCADE)
;
    
INSERT INTO users
    (`user_id`, `name`,`id_user_type`)
VALUES

    ('1', 'user',1),
    ('2', 'donator',2),
    ('3', 'user2',1),
    ('4', 'donator2',2)
;

SELECT
  u.`user_id`, u.`name`, ut.user_type
FROM users u JOIN user_type ut  ON ut.usertype_id = u.id_user_type

user_id	name	user_type
1	user	user
3	user2	user
2	donator	donator
4	donator2	donator

fiddle

Much more intresting gets it, when a user can have multiple usertypes then you would also add a bridge, that will need to be inserted as well but has many benefits.

CREATE TABLE user_type 
    (`user_type` varchar(9), `usertype_id` int PRIMARY KEY )
;
    
INSERT INTO user_type 
    (`user_type`, `usertype_id`)
VALUES
    ('user', '1'),
    ('donator', '2')
;

Records: 2  Duplicates: 0  Warnings: 0

CREATE TABLE users
    (`user_id` varchar(7) Primary key , `name` varchar(8))
;
    
INSERT INTO users
    (`user_id`, `name`)
VALUES

    ('1', 'user'),
    ('2', 'donator'),
    ('3', 'user2'),
    ('4', 'donator2')
;

Records: 4  Duplicates: 0  Warnings: 0

CREATE TABLE users_user_type(
`user_id` varchar(7)
  , `id_user_type` int,
    FOREIGN KEY (id_user_type)
        REFERENCES user_type(usertype_id),  
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        )

INSERt INTO users_user_type VALUEs (1,1),(2,2),(3,1),(3,2),( 4,2)

Records: 5  Duplicates: 0  Warnings: 0

SELECT
  u.`user_id`, u.`name`, ut.user_type
FROM users u 
  JOIN users_user_type uu ON uu.user_id = u.user_id
  JOIN user_type ut  ON ut.usertype_id = uu.id_user_type
ORDER BY u.user_id

user_id	name	user_type
1	user	user
2	donator	donator
3	user2	user
3	user2	donator
4	donator2	donator

fiddle