MySQL Bugs: #13286: Tables in SQL Server having composite foreign keys not migrated

Bug #13286	Tables in SQL Server having composite foreign keys not migrated
Submitted:	16 Sep 2005 22:01	Modified:	26 Oct 2005 22:12
Reporter:	Krishnan Ganapathy	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S1 (Critical)
Version:	Release 5.0.X	OS:	Microsoft Windows (Windows, Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi,
There is a bug (Bug # 12358) that looks similar to what I am mentioning below. 

We have a table in SQL Server that has a foreign key which is referrring to a composite primary key. 

The table definition is :

CREATE TABLE [work_item_attribute] (
	[work_item_id] [int] NOT NULL ,
	[attribute_number] [int] NOT NULL ,
	[repeatable_series_index] [int] NOT NULL CONSTRAINT [DF_work_item_attribute_repeatable_series_index] DEFAULT (1),
	[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[routable_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_routable_flag] DEFAULT (0),
	[string_value] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
	[work_item_type_id] [int] NOT NULL ,
	[work_item_attribute_guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_work_item_attribute_work_item_attribute_guid] DEFAULT (newid()),
	CONSTRAINT [PK_work_item_attribute] PRIMARY KEY  NONCLUSTERED 
	(
		[work_item_attribute_guid]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	CONSTRAINT [FK_work_item_attribute_work_item] FOREIGN KEY 
	(
		[work_item_id]
	) REFERENCES [work_item] (
		[work_item_id]
	),
	CONSTRAINT [FK_work_item_attribute_work_item_attribute_type] FOREIGN KEY 
	(
		[work_item_type_id],
		[attribute_number]
	) REFERENCES [work_item_attribute_type] (
		[work_item_type_id],
		[attribute_number]
	)
) ON [PRIMARY]

I have removed some of the columns that are not relevant. When migrating to MySQL the create table script that gets generated is : 

CREATE TABLE `etrace_jaguar_local_dbo`.`work_item_attribute` (
  `work_item_id` INT(10) NOT NULL,
  `attribute_number` INT(10) NOT NULL,
  `repeatable_series_index` INT(10) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `routable_flag` TINYINT(1) NOT NULL,
  `string_value` VARCHAR(256) NULL,
  `work_item_type_id` INT(10) NOT NULL,
  `work_item_attribute_guid` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`work_item_attribute_guid`),
  INDEX `idx_wia_work_item_type_id` (`work_item_type_id`),
  UNIQUE INDEX `IX_work_item_attribute` (`work_item_id`, `attribute_number`, `repeatable_series_index`),
  CONSTRAINT `FK_work_item_attribute_work_item` FOREIGN KEY `FK_work_item_attribute_work_item` (`work_item_id`)
    REFERENCES `etrace_jaguar_local_dbo`.`work_item` (`work_item_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_work_item_attribute_work_item_attribute_type` 
  FOREIGN KEY `FK_work_item_attribute_work_item_attribute_type` (attribute_number)
    REFERENCES `etrace_jaguar_local_dbo`.`work_item_attribute_type` (attribute_number)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
 CONSTRAINT `FK_work_item_attribute_work_item_attribute_type`
  FOREIGN KEY `FK_work_item_attribute_work_item_attribute_type` (work_item_type_id)
    REFERENCES `etrace_jaguar_local_dbo`.`work_item_attribute_type` (work_item_type_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = INNODB;

Here we can see that there are two foreign key constraints with name 'FK_work_item_attribute_work_item_attribute_type'. Hence the table creation failed. We had to modify the table creation script (in the Migration UI) to the following :

CREATE TABLE `etrace_jaguar_local_dbo`.`work_item_attribute` (
  `work_item_id` INT(10) NOT NULL,
  `attribute_number` INT(10) NOT NULL,
  `repeatable_series_index` INT(10) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `routable_flag` TINYINT(1) NOT NULL,
  `string_value` VARCHAR(256) NULL,
  `work_item_type_id` INT(10) NOT NULL,
  `work_item_attribute_guid` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`work_item_attribute_guid`),
  INDEX `idx_wia_work_item_type_id` (`work_item_type_id`),
  UNIQUE INDEX `IX_work_item_attribute` (`work_item_id`, `attribute_number`, `repeatable_series_index`),
  CONSTRAINT `FK_work_item_attribute_work_item` FOREIGN KEY `FK_work_item_attribute_work_item` (`work_item_id`)
    REFERENCES `etrace_jaguar_local_dbo`.`work_item` (`work_item_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_work_item_attribute_work_item_attribute_type` 
  FOREIGN KEY `FK_work_item_attribute_work_item_attribute_type` (attribute_number,work_item_type_id)
    REFERENCES `etrace_jaguar_local_dbo`.`work_item_attribute_type` (attribute_number,work_item_type_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
ENGINE = INNODB;

In the erroneous script the composite foreign key constraint has been split into two separate foreign key constraints which is not correct. Any pointers to how it can be addressed. Is this a  known issue or are we doing something wrong? 

Will the fix for Bug # 12358 be applicable to this one?

Any pointers willl be highly appreciated.

-Krishnan

How to repeat:
1. [SQL SERVER] :Create a table having a composite primary key
2. [SQL SERVER] :Create another table having a composite foreign key reference to the table created in Step 1.
3. [MySQL] : Migrate the table created above using the migration toolkit.
An error will be shown during the table creation.

See the way the composite foreign key reference has been defined. This is the cause of the error.

Thank you for your bug report.  I was able to reproduce this bug by using the supplied table definitions under 1.0.18

I could not verify this. Can you please paste the complete script to create the master and detail table?

I tested with 1.0.19 and this script.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_product_part_product]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[product_part] DROP CONSTRAINT FK_product_part_product
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[product_part]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[product_part]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[product]
GO

CREATE TABLE [dbo].[product] (
	[idproduct_group] [int] NOT NULL ,
	[idproduct] [int] NOT NULL ,
	[name] [char] (10) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[product_part] (
	[idproduct_part] [int] NOT NULL ,
	[idproduct_group] [int] NOT NULL ,
	[idproudct] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[product] WITH NOCHECK ADD 
	CONSTRAINT [PK_product] PRIMARY KEY  CLUSTERED 
	(
		[idproduct_group],
		[idproduct]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[product_part] WITH NOCHECK ADD 
	CONSTRAINT [PK_product_part] PRIMARY KEY  CLUSTERED 
	(
		[idproduct_part]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[product_part] ADD 
	CONSTRAINT [FK_product_part_product] FOREIGN KEY 
	(
		[idproduct_group],
		[idproudct]
	) REFERENCES [dbo].[product] (
		[idproduct_group],
		[idproduct]
	)
GO

I was unable to reproduce this bug using the last MT release.

The error i suppose is with the Migration Toolkit. I have given the script that we have used in the description of the bug. Is there a later version of the Migration Toolkt that have corrected this bug? Pls let me know. We are still trying with the Version we have  is ver 1.0.15.