October 26, 2009 at 3:50 am
Hi all
Can Be Define A conditional foreign key on a table for example
Status Master
---------------------
Status ID Char(1)
Description nVARCHAR(20)
Table Name nVARCHAR(20)
Status Master Data
------------------------------
Status ID description Table Name
A Activate Client Master
D Deactivate Client Master
N New Order Master
I In process Order Master
C Complete Order Master
Client Master
-------------------
Client ID INT
NAME nVARCHAR(20)
Status ID Char(1)
Client Master Data
-----------------------------
Client ID Name Status ID
1 ABC A
2 XYZ D
3 PQR A
Order Master
-------------------
Order ID INT
Client ID INT
Status ID Char(1)
Order Master DATA
------------------------------
Order ID Client ID Status ID
101 1 I
102 2 C
103 3 N
104 1 I
Now we can implement a foreign key on status id field of both table client master and order master table .
we wand that data in status id field in both table must be validate according table name given in status master.
it means client master table may use only two status 'A' and 'D'.
it can be possible??
December 5, 2009 at 5:31 pm
:pinch: that's too confusing, I could understand neither the scenario nor the question.
Could you clarify? pretty please?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 5, 2010 at 3:15 am
This can be done with trigger and procedures I think but it won't be easy.
You can't use a foreign key based on some condition.
A trigger in which you use a procedure with dynamic statements maybe can do the trick.
Haven't tried this yet, if a have some time I'll try next week.
May 7, 2010 at 2:16 pm
wim.buyens (3/5/2010)
This can be done with trigger and procedures I think but it won't be easy.You can't use a foreign key based on some condition.
A trigger in which you use a procedure with dynamic statements maybe can do the trick.
Haven't tried this yet, if a have some time I'll try next week.
On a second read you are absolutely correct - triggers would do the trick.
Microsoft calls it "Procedural Referential Integrity" as opposed to "Declarative Referential Integrity" which is based in FK/PK pairs.
More information is available at http://msdn.microsoft.com/en-us/library/aa902684(SQL.80).aspx#sql_refintegrity_topic03
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 7, 2010 at 3:42 pm
Don Peterson has written an article on why you should not use the type of design that you are proposing: "Lookup Table Madness" at http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/
SQL = Scarcely Qualifies as a Language
May 8, 2010 at 8:05 am
Carl Federl (5/7/2010)
Don Peterson has written an article on why you should not use the type of design that you are proposing: "Lookup Table Madness" at http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/
I totally agree with Don Peterson's position on the matter but please note neither original poster nor anybody else in this thread is suggesting the creation of a general purpose lookup code table.
I think this is the typical case where a great answer is posted... for the wrong question.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 8, 2010 at 9:45 am
Paul Berzukov posted
totally agree with Don Peterson's position on the matter but please note neither original poster nor anybody else in this thread is suggesting the creation of a general purpose lookup code table.
Paul, take a look at the example data provided under "Status Master Data" and you will see "table name" as one of the columns, hence Don Peterson's article on a flawed design is applicable.
SQL = Scarcely Qualifies as a Language
May 14, 2010 at 9:02 am
Carl Federl (5/8/2010)
Paul Berzukov postedtotally agree with Don Peterson's position on the matter but please note neither original poster nor anybody else in this thread is suggesting the creation of a general purpose lookup code table.
Paul, take a look at the example data provided under "Status Master Data" and you will see "table name" as one of the columns, hence Don Peterson's article on a flawed design is applicable.
You are right Carl.
I was focusing in answering poster's specific question.
I appreciate you going deeper and looking at what poster was actually doing.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply