InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 19:34:03 GMT20http://www.sqlservercentral.com/Forums/Topic808556-49-1.aspx[quote][b]Carl Federl (5/8/2010)[/b][hr]Paul Berzukov posted[quote] 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.[/quote]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.[/quote]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.Fri, 14 May 2010 09:02:54 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic808556-49-1.aspxPaul Berzukov posted[quote] 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.[/quote]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.Sat, 08 May 2010 09:45:06 GMTCarl Federlhttp://www.sqlservercentral.com/Forums/Topic808556-49-1.aspx[quote][b]Carl Federl (5/7/2010)[/b][hr]Don Peterson has written an article on why you should [b]not[/b] use the type of design that you are proposing: "Lookup Table Madness" at [url]http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/[/url][/quote]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.Sat, 08 May 2010 08:05:11 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic808556-49-1.aspxDon Peterson has written an article on why you should [b]not[/b] use the type of design that you are proposing: "Lookup Table Madness" at [url]http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/[/url]Fri, 07 May 2010 15:42:34 GMTCarl Federlhttp://www.sqlservercentral.com/Forums/Topic808556-49-1.aspx[quote][b]wim.buyens (3/5/2010)[/b][hr]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.[/quote]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 [url]http://msdn.microsoft.com/en-us/library/aa902684(SQL.80).aspx#sql_refintegrity_topic03[/url]Fri, 07 May 2010 14:16:33 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic808556-49-1.aspxThis 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.Fri, 05 Mar 2010 03:15:04 GMTwim.buyenshttp://www.sqlservercentral.com/Forums/Topic808556-49-1.aspx:pinch: that's too confusing, I could understand neither the scenario nor the question.Could you clarify? pretty please?Sat, 05 Dec 2009 17:31:19 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic808556-49-1.aspxHi allCan Be Define A conditional foreign key on a table for exampleStatus Master---------------------Status ID Char(1)Description nVARCHAR(20)Table Name nVARCHAR(20)Status Master Data------------------------------Status ID description Table NameA Activate Client MasterD Deactivate Client MasterN New Order MasterI In process Order MasterC Complete Order MasterClient Master-------------------Client ID INTNAME nVARCHAR(20)Status ID Char(1)Client Master Data-----------------------------Client ID Name Status ID1 ABC A2 XYZ D3 PQR AOrder Master-------------------Order ID INTClient ID INTStatus ID Char(1)Order Master DATA------------------------------Order ID Client ID Status ID101 1 I102 2 C103 3 N104 1 INow 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??Mon, 26 Oct 2009 03:50:55 GMTSQL Writer