all, i have two database tables and they are currently not related table a ( id,name, mrad) table b (id,name, participantid) in table A the mrad is not unique and also has null's. our requirement is to make sure that participantid from the table b is one of the existing mrad's in the table A. how can i achieve this. mrad does not fall under the category of unique or the candidate key. can i create a check constraint..referring to another table ??
There could be 2 approaches in this situation 1. Create a table with mrad which will contain unique values of mrad and create a foreign key from both tables to this one. This could be applicable if you are able to change the logic behind the tables (application or whatever uses them 2. Handle the relations using trigger. In trigger you can check whether the inserted/updated values of participantid exists in the mrad field of table A and if not, you can rollback the transaction or raise an error inside the trigger.