Make primary key and unique id as foriegn key
The first table, user_profile has two unique constraints, the PRIMARY KEY on (id) and the UNIQUE on the non-nullable (user_id).

That makes it possible to reference either of them in FOREIGN KEY constraints from other tables. You can use what you have, to reference the PK:

CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (id) 


or, to reference the UNIQUE column:

CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (user_id) 


There is no need to reference both of them.