Competition: What’s your favorite Redgate tool? Enter now.

Conditional Foreign Keys

Short: Does anyone know of how I would fill in a foreign key value only if the value of a different column is a certain value?

Long: I have an Equipment table. It has an Operator column that is a key reference to the Employee table. This column needs to be null if the Ownership type is set to Subcontracted. Pretty sure that this needs to be done with a Python script but I'm very new to this tool and am not sure of the best way to handle it.
Tagged:

Best Answer

  • way0utwestway0utwest CO, USAPosts: 307 Rose Gold 1
    Accepted Answer
    I don't see an easy way to do this. I know we could write a Python script that would read the table and optionally return a NULL, but that's an overly complex approach.

    What I'd do is:
    1. call SQL Data Generator from the command line, use a normal FK generator
    2. call a SQL script that looks at Equipment.Ownership and sets Equipment.Operator to null

    Really this is likely a quicker approach. I wish SDG included some pre/post processing, but that's not likely to come soon. Instead, I'd set this as a known process for provisioning data sets. Call SDG, then call

    update equipment
    set operator = null
    where ownershiptype = 'Subcontracted'

Answers

  • way0utwestway0utwest CO, USA Posts: 307 Rose Gold 1
    If I understand this, you want to populate the Equipment.Operator column with data. This data is a reference to another table, correct? Some Operators table?

    Then, you want a NULL in this column if Operator.OwnershipType = Subcontracted, otherwise some valid FK. Is that correct?
  • rlasker3rlasker3 Posts: 3 New member
    way0utwest wrote: »
    If I understand this, you want to populate the Equipment.Operator column with data. This data is a reference to another table, correct? Some Operators table?

    Then, you want a NULL in this column if Operator.OwnershipType = Subcontracted, otherwise some valid FK. Is that correct?

    Yes, this is correct. If the the equipment is subcontracted you do not assign an operator to it so it needs to be null. Otherwise, an operator can be assigned.
  • rlasker3rlasker3 Posts: 3 New member
    This is very disappointing. We have a very large database so there is likely to be many of these kinds of scenarios not to mention this currently fails due to check constraints. Having to store a large list of scripts that need to run at the end obscures the logic compared to each column doing the right approach.
  • way0utwestway0utwest CO, USA Posts: 307 Rose Gold 1
    I'll pass this along as something to add to the list. I don't think I've heard this, so I'm not sure this is a common scenario.
  • I would second this - when populating an FK column, to be able to filter the range of possible foreign key values through for example a SQL statement and then randomly pick from that filtered range.
  • PeterWPeterW Posts: 1 New member
    This is a very common scenario where certain columns (e.g. table1.col1) can only be populated with data depending on the value of another column (table1.col2), and the other column table1.col2 is a foreign key constraint to table2.col1.

    So are we saying that every time this situation occurs we have to 'park' the column update until after SDG completes and run a load of update scripts separate and outside of SDG?
Sign In or Register to comment.