Conditional Foreign Keys

rlasker3

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.

way0utwest

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'

way0utwest

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?

rlasker3

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.

rlasker3

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.

way0utwest

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.

jd_sequel

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.

PeterW

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?