Results 1 to 14 of 14

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

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

    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
    34,585

    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,905

    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.
    The best argument against democracy is a five minute conversation with the average voter - 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,493

    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
    4,541

    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?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE 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.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  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,905

    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.
    The best argument against democracy is a five minute conversation with the average voter - 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
    34,585

    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,905

    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.
    The best argument against democracy is a five minute conversation with the average voter - 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
    34,585

    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,905

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

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

  11. #11

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

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

    LOL, I am working with these tables again so it's bothering me that I wasn't sure I ever did any thing about preventing orphans...per the discussion above. I'm glad I found this old post but I am confused why I would ask a question then let you guys have all the fun. It's quitting time here (and I was on vacation away in North Carolina for the past 4 days, just back today, Friday and exhausted) so I am going to take a closer look at this on Monday. TBC and thanks so far for your input! Oh, until then I will say..."Control" is just the naming convention established here to mean it's the PK of the table. Like in table Jobs we have column Control. In table xtblProjects we have column ProjectControl. And the reason this came up again today almost four years later - I exposed a way in the interface for a user to delete a Project, that is delete a row from xtblProjects. But the P record in xtblJobProjectEmployeeRoles was allowed to remain behind; an orphan!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,541

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

    And Funky in post 3, solution 2, and Gary in post 4 gave the solution i would prefer.
    both FK-columns nullable
    constraint on the 2 FK-columns:
    both columns are not allowed to be both null at the same time
    and
    both columns are not allowed to be both not null at the same time.
    bottom line: it boils down to an XOR between both FK - either P-FK or J-FK is null and the other has a legal value
    then the usual ON CASCADE works as you’re used to

    in a nutshell: this concept is something i call a „castrated m:m-scenario (remember: m:m-tables have 2 or more FK per record)

    castrated, because the usual m:m-scenario never „connects“ the 2 parent tables

    kinda like children from a divorce: always with one parent, never with both, never without
    Last edited by Zvoni; May 17th, 2024 at 04:49 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE 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.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

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

    Quote Originally Posted by techgnome View Post
    There's not a lot of meat in that first post ...
    I was actually kind of proud of myself in that first post! Usually I am on the long-winded side.
    Also usually I am open to suggestions, however i feel kind of stubborn about this because I like my design. I think it's slick. That of course doesn't mean that it's any good.

    So to recap, here are a few rows in xtblJobProjectEmployeeRoles.

    Name:  vbForums P and J Control.jpg
Views: 113
Size:  13.4 KB

    Row #1 says that 96318 is a record that exists in the Jobs table.
    Row #2 says that 211 is a record that exists in the Projects table.
    Or we hope those records exist.
    (If you are curious, Employee is an actual employee id of someone that works here, and role is what they are responsible for on the Job or Project. Role = 1 means they're a project administrator.)

    So everyone here is in agreement that there is no sql server way to enforce that 96318 exist (Jobs.Control = 96318) and 211 exist (xtblProjects.ProjectControl = 211), so if you tried to delete the P record w/ ProjectControl 211, it wouldn't let you? (FWIW, we will *never* delete a record from the Jobs table. It's only recently come up in xtblProjects. Somebody created a project and made an employee assignment erroneously and they asked for a way to hard delete the Projects record, and therefore also the assignment record).

    Again, just being stubborn. xtblJobProjectEmployeeRoles was created a few years ago and it just seems like a lot of work to change it now.

    I will confess that there are 10 P orphans which I was trying to prevent when I first posted this. I am not even sure what happened to those parent records since I just recently exposed a way for users to delete a project. But apparently they've been creeping in, as I'm just now seeing from the query below!
    Code:
    select * 
    from xtblJobProjectEmployeeRoles role
    left join xtblProjects proj on proj.ProjectControl = role.Control
    where role.ControlType = 'P' 
    and proj.ProjectControl is null
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,541

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

    And as i wrote: what you have there is basically a „m:m“-scenario, with the difference that both parent-tables never „connect“

    add a second FK-column, the first one pointing to jobs, the second one to projects with the rules/constraints as described above
    then you can enforce ON CASCADE and „real“ FK-mechanisms, and no more orphans
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE 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.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

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