A customer recently asked about using FKs in SQL Data Generator, and I decided to write a short post showing how these work.
The Scenario
I’ve got a copy of the Northwind sample database on my system, named Westwind so I can experiment. As you can see below, the Order Details table has a few FKs in it, one to Orders and one to Products.
This is a very standard setup in many databases, where there are links between tables. I’m also going to add another table with no FKs, but I want data in here from another table.
CREATE TABLE OrderHistory (OrderID INT , OrderDate DATETIME , Complete BIT ) GO
This should have an FK declared to dbo.Orders.OrderID, but as I often see, this wasn’t set up.
Using SQL Data Generator
I’ll create a new project in SQL Data Generator that points to this database. When this configures itself, I’ll deselect all tables except for Orders, Order Details, and OrderHistory.
When I do this, if I look at Order Details, I can see that in the preview, both OrderID and ProductID are listed as generated data using the keys. In this case, this means I’d get data from both the Orders and Products tables.
If I check OrderHistory below, you can see that the OrderID is set as an integer to be generated. However, that’s not what I want.
Fortunately, I can set a manual key here. If I select the Generator drop down at the top, I see there is a SQL Type generator, and one of the subtypes is a FK. We’ll pick that.
This changes the configuration and I need to pick a table and column. I’ll choose orders.
Now I can generate data. Before I do this, I check Orders and there are 840 orders in there. I’ll just generate 10 more. I’ll also generate 10 OrderHistory rows and not delete existing data. Once I generate the data, let’s look at some results.
Below, we’ve added 10 orders, which makes sense. My OrderHistory table has 10 rows, but when I join with Orders on the OrderID, I get all 10 rows back. Data generator has respected a manual, non-DRI, FK.
SQL Data Generator should detect your declared FKs, but even if you don’t have them or it doesn’t, you can add them into your generated dataset.
SQL Data Generator is part of the SQL Toolbelt, and fantastic set of productivity tools for SQL Server developers. If you’ve never used these, download an eval and give them a try today.
Video Walkthrough
I also have a video walkthrough of this post, if you’d rather see this in action.