如何在员工维度（SCD Type2）表MySQL中创建唯一的employee_id

Question

i'm creating a employee dimension table. I need to generate unique employee id when new employee is created. but when I update the existing employee details, the emp_id should be retained but the previous records active_ind should be set to 'N' and insert the updated record (SCD Type2). each insert should have a emp_key which can be a Auto_Increment.

I expect output as below

emp_key | emp_id    | emp_name  | emp_DOB   | Dept_id   | create_dt | update_dt | curr_row_ind
1   | 101   | Robert    | 5/12/1987 | dept~01   | 2/12/2018 | 4/10/2018 | N
2   | 102   | Albert    | 6/12/1988 | dept~01   | 3/11/2018 | NULL  | Y
3   | 101   | Robert    | 5/12/1987 | dept~02   | 4/10/2018 | NULL  | Y