Tag: Redgate

This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today.

One of the things that people often want to do is generate data, but limit the generation to some data in another column. Here’s a good example. Let’s suppose I have some data that represents a balance in an account. That’s in a column we’ll call Balance. In another column, I have a status that is either OK or Overdrawn, depending on whether the Balance column is positive or negative.

If I perform a random generation on these columns, I’ll get some strange data. Sometimes the data below matches up, sometimes it doesn’t. I have positive numbers as Overdrawn and negatives as OK

Let’s fix that.

In Data Generator, I have a variety of choices for the generators. Let’s look at what I can do for the Status column.

Certainly there are RegEx and Python scripts, but there’s a nice “Cross Column” section with some examples. In this case, let’s look at the Age in Years generator. The definition is:

In this case, it’s a simple .NET date function and some math. I can do that. Most importantly, I can see the “Insert Column Name”, which lets me pick another column in my table.

Python

The language of choice in SQL Data Generator is Python, specifically Iron Python. Outside of C# Datatime values, Python is needed. If you examine any of the other cross column items, you’ll see we need a main() function that returns something.

In this case, it’s a simple expression. I’ll use an If statement to check if the Balance is >= 0. Here’s a Python construct.

def main(config):
     if Balance >= 0:
         return “OK”
     else:
         return “Overdrawn”

Whitespace matters, as does indentation. If I put this in like so:

I’ll get this. Notice that the status is correct.

In a real project, you may have more complicated logic, or more likely, status values. One way to handle those is to use a Python function and return the appropriate values for your system.

You can build some complex and interesting data generation projects with SQL Data Generator. Give it a try today.

This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today.

I have SQL Data Generator, and use it regularly to build quick  test data in non-trivial scenarios. One of the things I ran into recently was a minor bug, but one that’s been logged. Hopefully you won’t need this, but in case you do.

I was trying to use a Python script and return a random value from a list. My code was:

return random.choice(mylist)

This gave me an error.

No biggie, I’ll add “import random” to the top of the script. That didn’t help. Apparently the distro with SQL Data Generator is missing the random module for some reason.

Fortunately, I have Python 2.7 on my system. I clicked “Tools” and “Application Options” in SQL Data Generator.

This gave me a dialog. On the General tab, there’s a “Python” section. I added the path to my Python lib folder in here.

That worked fine. I didn’t need to import random; it was available. My script now worked.

A person was wondering about how data is generated with SQL Data Generator and foreign keys. In this case, the person was having an issue with a compound foreign key.

Here’s a quick look at how this works. I’ve got two tables

CREATE TABLE Product
(   ProductCode VARCHAR(30) PRIMARY KEY
   , ProductDesc VARCHAR(100))
;
CREATE TABLE SubProduct
(   SubProductCode VARCHAR(30)
   , ProductCode    VARCHAR(30)
   , SubProductDesc VARCHAR(100)
   , CONSTRAINT SubProductCodePK PRIMARY KEY (ProductCode, SubProductCode)
);
ALTER TABLE dbo.SubProduct
ADD
     CONSTRAINT fk_SubProduct_ProductCode FOREIGN KEY (ProductCode)
                      REFERENCES dbo.Product (ProductCode)
;
GO

These two tables are related with a FK. In this case, SubProduct relates to Product, but there is a constraint that says I can’t have duplicate product/subproduct combinations. That’s fine, and my data looks like this:

Now, I have a new table, this one contains a FK back to the other tables. In fact, I have two FKs, though I could deal with one.

ALTER TABLE dbo.SubProduct
ADD
     CONSTRAINT fk_OrderItems_ProductCode FOREIGN KEY (ProductCode) REFERENCES dbo.Product
                                          (   ProductCode)
;
ALTER TABLE OrderItems
ADD
     CONSTRAINT fk_OrderItems_SubProductCode_within_ProductCode FOREIGN KEY
                                                             (
                                                                 ProductCode
                                                               , SubProductCode) REFERENCES dbo.SubProduct
                                                             (
                                                                 ProductCode
                                                               , SubProductCode)
;

This FK is compound, consistenting of both ProductCode and SubProductCode. What does this mean? It means I can’t have an entry in OrderItems for a SubProductCode unless that SubProductCode has a matching ProductCode in the SubProduct table.

Or, in better English, If I have a Product Code of “RXZP”, of which I have 2 above, I can’t have a SubProductCode of “BA”, which isn’t in the table. I’ve constrained my SubProductCodes to those values that are available for a particular ProductCode. In this case, my only SubProductCode choices are “TJ” and “TW”.

A good data consistency model.

In Data Generator, this DRI is picked up. If I look at the generation for the OrderItems table, I see that both ProductCode and SubProductCode are listed as FK values.

That’s good, but I have two FKs on OrderItems. Which one is important? In this case, I can click the column and see. For ProductCode, it’s using the compound FK.

The same thing appears for the SubProductCode

If I generate data, I then get something like this:

As you can see, my product “RXZP” will all have “TJ” or “TW”. I know this because the FK will prevent anything else. This is handled and enforced by SQL Server, but Data Generator doesn’t create any errors when it generates the data since it’s using the source tables as the domain of possible values.

The User’s Problem

The user in this case didn’t have any DRI declared in this way. Their schema actually had the primary key in Subproduct as only the SubProductCode, not as a compound primary key. Their FK from OrderItems had the same issue, with a FK only to SubProductCode. As a result, Data Generator, and really any script, would assume that all values in SubProductCode are valid, regardless of ProductCode.

The lesson here is that DRI matters, and if you have business rules such as these, use a FK to enforce them.