Define A Conditional Forigion Key

  • SQL Editor

    SSCommitted

    Points: 1920

    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

    SSC Guru

    Points: 66773

    :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

    Ten Centuries

    Points: 1129

    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

    SSC Guru

    Points: 66773

    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

    One Orange Chip

    Points: 25324

    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

    SSC Guru

    Points: 66773

    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

    One Orange Chip

    Points: 25324

    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

    SSC Guru

    Points: 66773

    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.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply