question

Katie 1 avatar image
Katie 1 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 ??
sql-server-2008constraint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+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.
2 Likes 2 ·
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?
0 Likes 0 ·
+1 #1 definitely And as @Kevin Feasel points out, this probably shows a design flaw
0 Likes 0 ·
Yeah, the new table and correct relationship is the right way.
0 Likes 0 ·
Thank you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.