May 16, 2011 at 2:17 am
I need to upgrade an old legacy FoxPro db to SQL Server.
I've managed to import the db structure and data. However, there are no foreign keys on the FoxPro database.
Is there any way to get SQL or any 3rd party software to suggest what the foreign keys might be? There are quite a number of table so to do it manually will be very inefficient.
Any help would be appreciated!
Thanks all!
May 16, 2011 at 4:15 am
How confident are you that a column in one table which has a name matching the pk column in another table is a foreign key?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 16, 2011 at 5:06 am
As confident as I can be I guess. We inherited this db and have been told that although no actual keys exist, they link tables based on the field names being the same, and certainly for the tables I have reviewed this seems to be the case.
May 16, 2011 at 5:26 am
You can guess all you want with the names which will give you false positive and incomplete data.
Plan B is to read all the queries and figure them out from the joins. Yes it's longer but it works.
May 16, 2011 at 5:37 am
Ninja's_RGR'us (5/16/2011)
You can guess all you want with the names which will give you false positive and incomplete data.Plan B is to read all the queries and figure them out from the joins. Yes it's longer but it works.
I know what you mean, but what I'm looking for is a tool or application I could use which would look at the db and based on names of fields suggest possible foreign keys. Then I could use these as a start point at least.
Do you know of any such tools out there?
Thanks.
May 16, 2011 at 5:55 am
I once built a script to make those but I don't have access to it anymore.
The idea is to scan sys.columns and sys.tables (linking with object_id).
Then search for patterns like columns name are exactly the same. Or ends with _id. Only you can tailor it to your need.
At some point you'll need a filter to only pick the pk columns on 1 side of the equation. So you might has well start by creating the PKs first.
May 16, 2011 at 5:59 am
P.S. It's also a good time to check for mismatch in datatypes. Sometimes you really need a relation but you need to edit 1 of the datatypes. That will save you some headaches in the future.
May 16, 2011 at 6:26 am
Ninja's answered the question you originally posted - it's straightforward enough to write a query which will do what you are asking.
Expanding upon his comments, you need an intimate knowledge of the Fox db in order to most effectively model it in SQL Server. It's not simply a case if guessing which columns might constitute the PK. Foxpro eventually implemented auto-incrementing columns but in my experience most Fox apps use natural keys (very often a set of columns). SQL Server has the robust implementation of surrogate keys expected in a RDBMS. Which will you use and how will you decide? Do you have the source code for the Fox app?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply