Results 1 to 10 of 10

Thread: Foreign Key, but to which table it depends (SQL Server)

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Working from home, social distancing, just like you
    Posts
    4,166

    Foreign Key, but to which table it depends (SQL Server)

    I have a table and in it are two columns Control and ControlType. If Control Type is P, Control has to exist in xtlbProjects and if ControlType is J, Control has to exist in Jobs. Can I enforce that with a FK?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Foreign Key, but to which table it depends (SQL Server)

    In short, no I don't think so. But, you could impose a constraint ... but it wouldn't work as good as an FKey in that it wouldn't prevent deletes. I suppose you could have a trigger or a couple of triggers that might get the job done.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: Foreign Key, but to which table it depends (SQL Server)

    What's happened here is that your design doesn't meet third normal form. A constraint will get the job done but, if you have the luxury, I'd consider a redesign. There are several alternative designs that do meet TNF but I'd suggest one of the following two:-
    1. You should have two separate tables, one containing P Controls and one containing J Controls. That way each table only has a foreign key to a single table.
    2. You should have two separate foreign key columns in your single table. One to Projects and one to Jobs.

    Neither of these is a perfect solution but they should be an improvement on your current design.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,404

    Re: Foreign Key, but to which table it depends (SQL Server)

    I would tend to slightly disagree with Funky on not having more than 1 FK to the table. I have had to do this before... I created 2 columns P_ContorlsId NULLABLE with FK to the P Controls table and J_ControlsID also NULLABLE with FK to the J Controls table. If the object refers to the P control that column would be filled in and the J control column would be null.

    Joins to the table become a little more interesting as you can't just use an INNER JOIN between the tables, you would join to both P and J tables using LEFT or RIGHT Outer Joins
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,191

    Re: Foreign Key, but to which table it depends (SQL Server)

    Quote Originally Posted by GaryMazzone View Post
    Joins to the table become a little more interesting as you can't just use an INNER JOIN between the tables, you would join to both P and J tables using LEFT or RIGHT Outer Joins
    Yeah, but wouldn't you be able to "simulate" an INNER JOIN with a LEFT JOIN with a NOT ISNULL right hand side?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: Foreign Key, but to which table it depends (SQL Server)

    Joins to the table become a little more interesting as you can't just use an INNER JOIN between the tables, you would join to both P and J tables using LEFT or RIGHT Outer Joins
    I think that problem already exists with the current design. The only difference being that, at present, you need two outer joins from the same column. I don't think you'll be able to escape that issue no matter what approach you use.

    Stepping back, what you're looking at here is inheritance. You have 2 types of Control, a P and a J control, and they both inherit from control. There are already established patterns for dealing with it. I found a good article here. I gave the single and two table approaches but there's also a three table approach worth considering. Also, just have a google for "Database Inheritance" and you'll find plenty of discussion.

    edit> correction, I actually gave the 2 and 3 table approaches.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Foreign Key, but to which table it depends (SQL Server)

    and they both inherit from control.
    Actually there's no indication that they do, other than the field name. In all fairness, the fields could have just as easily been called "Parent" and "ParentType" or "Link" and "LinkType". Or heck, Reference and ReferenceType... It's just that it's either going to reference a Job in the jobs table, or a Project in the projects table. I'm not sure there is any kind of inheritance going on. It's not like Job and Project are of a common object type with varying attributes, they are two distinct objects in this case. I think things got off the rails once they got referred to as P controls and J controls... the nomenclature lead people down a certain road that wasn't the correct one.

    In this case the Control isn't a control object like a command button but more likely a Control Operation, or some other Domain Object that has a different meaning.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: Foreign Key, but to which table it depends (SQL Server)

    It's not like Job and Project are of a common object type with varying attributes
    It's not the Job and Project that inherit, it's the Control. There's the "Control which belongs to a Project" and the "Control which belongs to a Job". They're both sub types of a Control super type.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,829

    Re: Foreign Key, but to which table it depends (SQL Server)

    I think we're missing a lot of info here and simply filling in the gaps. I don't even know what table we're talking about. I know there's three... some mysterious tableX, Jobs, and Projects.
    two columns Control and ControlType. If Control Type is P, Control has to exist in xtlbProjects and if ControlType is J, Control has to exist in Jobs
    I take that to read that TableX can belong to Jobs, OR it can belong to Projects... but not both. What it belongs to depends on the ControlType (J or P).
    Now I don't know if TableX is a Control table or not... There's not a lot of meat in that first post and NMock hasn't been back since to clear anything up. So everything after that is supposition.
    Bottom line: I don't think I know what the hell I'm talking about any more.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: Foreign Key, but to which table it depends (SQL Server)

    I don't think I know what the hell I'm talking about any more
    That's just your age

    I'm working under the assumption that TableX is Control but it doesn't really matter. TableX contains two similar but different entities.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width