asked
database constraints
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.
+1. I would tend toward answer #1, personally. That requirement indicates to me that the database design is probably missing an entity which mrad/participantid represents. A trigger would work, but making explicit that implicit entity would decrease the likelihood of errors and increase the ability to understand the database model.
I like option #1 too. It sure sounds like they need an FK. Anything else gets into a weird dance that is going to constantly be a source of pain. But, what data types are they?
+1 #1 definitely And as @Kevin Feasel points out, this probably shows a design flaw
Yeah, the new table and correct relationship is the right way.
