April 16, 2009 at 2:17 pm
Hi,
May be there is a simple solution here but I am not seeing it... so any comments are appreciated.
We have 3 tables: 2 tables for store messages and 1 to track the status (..of those messages in 2 tables)
Message Tables
Table_1
Table_2
Status Table
Status
Structure of both the Message tables is almost the same
Id (Identity Column),
Message_Identifier (Business ID)
Message VARCHAR
...
...
Structure of status table is:
ID
Message_Identifier
Status
Error_Description
...
...
Message tables are connected to status table with Message_Identifier (Business ID).
Now, is there a way to create foreign key relation from Status table to both the message tables.
thanks,
_Uday
April 16, 2009 at 2:22 pm
Not really. Foreign keys references one other table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2009 at 2:28 pm
April 17, 2009 at 6:51 am
So you want to insure that the rows in the Status table have a valid Message_Identifier from one of the 2 message tables? While not ideal, this situation is one where a trigger can be used to enforce referential integrity. You might also be able to create a UDF that you could use in a check constraint on the Status.Message_Identifier column that verifies that it is a valid Message_Identifier.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 17, 2009 at 7:51 am
UDF...mmm.... did not know that was possible. I'll try it.
Update: I wrote a trigger to accomplish this, but I'll try UDF, just to test.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply