Handling multiple primary keys
TLDR; firstly no it doesn't make sense to do that, secondly you can do that and the application wont care / notice either way

The main thing that you are fighting with confusing is human interaction, So as far as any application is concerned you should have the user_id, employee_id and a leave_request_id, any application when coded correctly will be able to identify the differences, 

Looking at it, would it not be easier to have a person table (I'm presuming here that users are external to your employee list.

From here you could have a person and all their details in that table, with the identity field on it, and then if they are a user that entry is added into that table, if they are an employee they are entered into that table, (if both then both tables) and finally if an employee leaves they can be added into that table.

Now you have one id across all of your tables which makes joining them together a lot easier if you need to look at any users who are employees who have left you have a single ID across all tables, it also means that from a human perspective the ID you know what it all is in relation to because its all the same.