Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Define A Conditional Forigion Key Expand / Collapse
Author
Message
Posted Monday, October 26, 2009 3:50 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:43 AM
Points: 697, Visits: 344
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
Posted Saturday, December 05, 2009 5:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Friday, March 05, 2010 3:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:28 AM
Points: 237, Visits: 121
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
Posted Friday, May 07, 2010 2:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Friday, May 07, 2010 3:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:31 AM
Points: 2,285, Visits: 4,222
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
Posted Saturday, May 08, 2010 8:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Saturday, May 08, 2010 9:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:31 AM
Points: 2,285, Visits: 4,222
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
Posted Friday, May 14, 2010 9:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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 »

Add to briefcase

Permissions Expand / Collapse