SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Define A Conditional Forigion Key


Define A Conditional Forigion Key

Author
Message
SQL Editor
SQL Editor
Right there with Babe
Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)

Group: General Forum Members
Points: 758 Visits: 363
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??
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5649 Visits: 4639
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.
wim.buyens
wim.buyens
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 188
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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5649 Visits: 4639
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.
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3412 Visits: 4350
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5649 Visits: 4639
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.
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3412 Visits: 4350
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5649 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search