<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Strategies
»
Define A Conditional Forigion Key
Define A Conditional Forigion Key
Rate Topic
Display Mode
Topic Options
Author
Message
SQL Writer
SQL Writer
Posted Monday, October 26, 2009 3:50 AM
Say Hey Kid
Group: General Forum Members
Last Login: Tuesday, August 28, 2012 4:29 AM
Points: 688,
Visits: 311
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??
Post #808556
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Posted Saturday, December 05, 2009 5:31 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 2,982,
Visits: 4,397
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.
Post #829459
wim.buyens
wim.buyens
Posted Friday, March 05, 2010 3:15 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, December 14, 2012 8:31 AM
Points: 237,
Visits: 109
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.
Post #877497
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Posted Friday, May 07, 2010 2:16 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 2,982,
Visits: 4,397
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.
Post #918347
Carl Federl
Carl Federl
Posted Friday, May 07, 2010 3:42 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 2,224,
Visits: 4,082
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
Post #918396
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Posted Saturday, May 08, 2010 8:05 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 2,982,
Visits: 4,397
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.
Post #918500
Carl Federl
Carl Federl
Posted Saturday, May 08, 2010 9:45 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 2,224,
Visits: 4,082
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
Post #918515
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Posted Friday, May 14, 2010 9:02 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 2,982,
Visits: 4,397
Carl Federl (5/8/2010)
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.
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.
Post #922116
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
