Data Generation with Compute columns which function as a FK

F.B. ten Kate

We're going to be upgrading our package to the SQL Development Bundle where we're currently only using SQL Compare.

Now i've downloaded the trail to start playing around with some things and one of the things I find very interesting is the Data Generator.

Sadly i'm trying to generate some data on one of our databases and i'm getting no joy at the moment because the way our databases are designed. For example we have the following design for addresses.

Address (base table, which holds an IsPostBoxOffice boolean, defining the type)
PostOfficeBoxAddress (holding only the PO box number)
GeographicalAddress (holding a house number, street)

Now, in order to make sure a PO only references a Address which has IsPostBoxOffice as true, this field combined with the ID is added to a Unique Constraint. This means in the PO table we have a IsPostBoxOffice field which is computed (always 1) and both the ID and IsPostBoxOffice field are referencing the Address table.

This ensures anyrecord entered in the specific subtables (Geographical & PostOfficeBox) will ALWAYS reference a correct record in the Address table.

This design might not be hugely common but it is (by my knowledge) the only way to design this efficiently.

Sadly the generator get's nothing but FK reference exceptions

Naturally I could edit the design and hope no one is dumb enough to make mistakes, but I kinda like having constraints to stop some of the stupidity in the world.

Is there a way to fix this with a custom generator, is this something that might need to be fixed in the Data Generator tool completely? What could I do to make this tool as usefull for us as I believe it could be!

Brian Donahue

Hi,

I think what you need to do is create some data as it was a computed column? I have a question because I don't think you included the whole schema (the ID column, for a start). But what I think you need is to "mimic" a real computed column - ie derive a value using a combination of the ID and IsPostBox, assuming that a value is supplied in the PO Box column, from the generated data.

This is possible only using the Ruby extension from here: ftp://support.red-gate.com/patches/SQL_ ... .0.3.1.zip

Unfortunately this is not part of the product so there is no "official" support for it except this article the original author wrote:
https://www.simple-talk.com/dotnet/.net ... generator/

F.B. ten Kate

Hi,

I'm not sure what this Ruby Extension is but i'll look into it. But no, I do not need to create data as it was a computed column.

Basically I've got a table called Addres filled with the following values;

ID IsPostalBox ZIPCode
1 1 123456
2 1 234567
3 0 123456
4 0 234567

I'd like the following tables called PostBoxOfficeAddres & GeographicalAddress to be filled with the values in the Address table which mean that in the PostBoxOffice Address i'd expect;

ID IsPostalBox PostBoxNumber
1 1 5000
2 1 5001

And geographical;

ID IsPostalBox HouseNumber
3 0 5
4 0 6

I've left some columns out of this picture but I hope it explains what I'm trying to generate.

In the two later table (Postbox & Geographical) the IsPostBox is a computed column, it doesn't actually compute anything, it's a fixed value of 0 or 1. It allows for a FK relationship between the two "sub tables" and the "base table" and it ensures value 1 1 123456 can never be referenced in the Geographical Table.

Also, since it's the FK i cannot change the generator, as it's fixed to "Foreign Key Generator" which posses a problem aswell.

Brian Donahue

Also, since it's the FK i cannot change the generator, as it's fixed to "Foreign Key Generator" which posses a problem aswell.

yes, if SDG won't let you change the type of generator, that may be a show-stopper for you. But presumably this just means the data in the other side of the relationship should be consistent. Still not sure how to solve this, based on limited information.

F.B. ten Kate

Brian Donahue wrote:

Still not sure how to solve this, based on limited information.

Well what information would you like?

I've written the relationship, I could also update the actual create scripts from these tables if that helps? I don't think it'll be fixable in the current version of SQL Data Generator, if anything it might be something that get's added in a future version of SQL Data Generator as I simply don't think that the current generator is able to do this specific job.

If the create script or anything else would help, just ask for specific information and I'll see what I can do.

Brian Donahue

I'd probably need the whole schema and some consultancy time to work on this. But I figured if you cannot change the FK generator and you need to do that, it's probably going to be a show-stopper anyway.

We support people can do workarounds but if the operational capability needs to be changed, all we can do is suggest changes.

Brian Donahue

I tried to create a schema based on this but I can't do the computed column spec. AFAIK a computed column is derived from values in the same table but you are saying IsPostalBox is derived from the Address table?

If I was designing the database, I would make the PostOfficeBoxAddress and GeographicalAddress views on the address table. That would save data duplication and allow you to compute the value of IsPostalBox.