Description:
I tried to migrate some tables from MS SQL 2000 enterprise (with sp3 running on a remote machine) to MySQL 4.1.13 (running on my own machine). Some tables have more than one foreign key. However, only one foreign key for each table is generated by the migration toolkit.
How to repeat:
I created few tables to recreate the issue. Here is the sql script I used to create table on MS SQL 2000 server.
create table employee
(
idCode varchar(13) not null,
SSN varchar(9),
activeStatus varchar(1),
statusDate datetime,
firstName varchar(30),
lastName varchar(30),
add1 varchar(60),
city varchar(20),
state varchar(2),
zip varchar(5),
oPhone varchar(14),
hPhone varchar(14),
fax varchar(14),
primary key (idCode)
)
create table dept
(
dptName varchar(25) not null,
managerID varchar(13) not null,
primary key (dptName),
constraint managerLnk Foreign key (managerID) references employee(idCode)
)
create table dptLnk(
employeeId varchar(13) not null,
dptId varchar(25) not null,
primary key (employeeId, dptId),
constraint empLink Foreign key (employeeId) references employee(idCode),
constraint dptLink Foreign key (dptId) references dept(dptName)
)
After the migration. Only constraint "dptLink" was created for table dptLnk.
Here is the log from migration toolkit
--------------------------------------------------------------------------------
-- MySQL Migration Toolkit Report --
-- --
-- Title: Summary Of The Migration Process --
-- Date: 2005-08-03 15:23 --
--------------------------------------------------------------------------------
1. Schema Migration
-------------------
Number of migrated schemata: 1
Schema Name: webase_dbo
- Tables: 3
- Views: 0
- Routines: 0
- Structured Types: 0
Details:
- Tables
`webase_dbo`.`dept`
-------------------
`webase_dbo`.`dptLnk`
---------------------
`webase_dbo`.`employee`
-----------------------
- Views
- Routines
- Structured Types
2. Data Bulk Transfer
---------------------
`webase_dbo`.`dept`
-------------------
0 row(s) transfered.
`webase_dbo`.`dptLnk`
---------------------
0 row(s) transfered.
`webase_dbo`.`employee`
-----------------------
0 row(s) transfered.
End of report.
--------------------------------------------------------------------------------
Suggested fix:
N/A
Description: I tried to migrate some tables from MS SQL 2000 enterprise (with sp3 running on a remote machine) to MySQL 4.1.13 (running on my own machine). Some tables have more than one foreign key. However, only one foreign key for each table is generated by the migration toolkit. How to repeat: I created few tables to recreate the issue. Here is the sql script I used to create table on MS SQL 2000 server. create table employee ( idCode varchar(13) not null, SSN varchar(9), activeStatus varchar(1), statusDate datetime, firstName varchar(30), lastName varchar(30), add1 varchar(60), city varchar(20), state varchar(2), zip varchar(5), oPhone varchar(14), hPhone varchar(14), fax varchar(14), primary key (idCode) ) create table dept ( dptName varchar(25) not null, managerID varchar(13) not null, primary key (dptName), constraint managerLnk Foreign key (managerID) references employee(idCode) ) create table dptLnk( employeeId varchar(13) not null, dptId varchar(25) not null, primary key (employeeId, dptId), constraint empLink Foreign key (employeeId) references employee(idCode), constraint dptLink Foreign key (dptId) references dept(dptName) ) After the migration. Only constraint "dptLink" was created for table dptLnk. Here is the log from migration toolkit -------------------------------------------------------------------------------- -- MySQL Migration Toolkit Report -- -- -- -- Title: Summary Of The Migration Process -- -- Date: 2005-08-03 15:23 -- -------------------------------------------------------------------------------- 1. Schema Migration ------------------- Number of migrated schemata: 1 Schema Name: webase_dbo - Tables: 3 - Views: 0 - Routines: 0 - Structured Types: 0 Details: - Tables `webase_dbo`.`dept` ------------------- `webase_dbo`.`dptLnk` --------------------- `webase_dbo`.`employee` ----------------------- - Views - Routines - Structured Types 2. Data Bulk Transfer --------------------- `webase_dbo`.`dept` ------------------- 0 row(s) transfered. `webase_dbo`.`dptLnk` --------------------- 0 row(s) transfered. `webase_dbo`.`employee` ----------------------- 0 row(s) transfered. End of report. -------------------------------------------------------------------------------- Suggested fix: N/A