MySQL Bugs: #21776: NO FOREIGN KEY when migrating ACCES 2003 to Mysql 5.xx

Bug #21776	NO FOREIGN KEY when migrating ACCES 2003 to Mysql 5.xx
Submitted:	22 Aug 2006 9:37	Modified:	29 Aug 2006 7:20
Reporter:	LE MOULLAC Nicolas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.1.2 beta	OS:	Windows (XP SP2 (v. 2002))
Assigned to:	Peter Lavin	CPU Architecture:	Any
Tags:	ACCES, foreign key, migration, MySQL

Description:
I couldn't export an ACCESS 2003 database to Mysql without lost my FOREIGN KEY. Indeed I show MSYS tables and grant administrator privileges to MSYSobject and the other one in ACCESS, it dosn't work. 

How to repeat:
You have to link tables in ACCESS in order to make relations between tables. Show MSYS tables and grant administrator privileges to MSYSobject and the other (read online documentation) . Then try to export with MYSQL MIGRATION TOOLKIT your ACCESS database toward Mysql.

Suggested fix:
I don't Know ! perhaps a patch??

Thank you for the bug report.

There's nothing to do ?

Could you please try the following SELECT inside MS Access against the Northwind database and paste the result here? You need to substitute "Artikel" with "Article", depending on the real table name in the Northwind DB.

SELECT szRelationship, 
 szColumn, szReferencedObject, szReferencedColumn, grbit 
FROM MSysRelationships
WHERE szObject="Artikel"
ORDER BY szRelationship, icolumn

szRelationship	szColumn	szReferencedObject	szReferencedColumn	grbit
KategorienArtikel	Kategorie-Nr	Kategorien	Kategorie-Nr	0
LieferantenArtikel	Lieferanten-Nr	Lieferanten	Lieferanten-Nr	0

Therefore the generated SQL on my machine looks like this, including the Foreign Keys.

DROP TABLE IF EXISTS `NordwindEx`.`Artikel`;
CREATE TABLE `NordwindEx`.`Artikel` (
  `Artikel-Nr` INT(10) NOT NULL AUTO_INCREMENT,
  `Artikelname` VARCHAR(40) NULL,
  `Lieferanten-Nr` INT(10) NULL,
  `Kategorie-Nr` INT(10) NULL,
  `Liefereinheit` VARCHAR(25) NULL,
  `Einzelpreis` DECIMAL(19, 4) NULL,
  `Lagerbestand` SMALLINT(5) NULL,
  `BestellteEinheiten` SMALLINT(5) NULL,
  `Mindestbestand` SMALLINT(5) NULL,
  `Auslaufartikel` TINYINT(1) NOT NULL,
  PRIMARY KEY (`Artikel-Nr`),
  INDEX `ArtikelKategorie-Nr` (`Kategorie-Nr`),
  INDEX `Artikelname` (`Artikelname`),
  INDEX `KategorienArtikel` (`Kategorie-Nr`),
  INDEX `Kategorie-Nr` (`Kategorie-Nr`),
  INDEX `LieferantenArtikel` (`Lieferanten-Nr`),
  INDEX `Lieferanten-Nr` (`Lieferanten-Nr`),
  CONSTRAINT `KategorienArtikel` FOREIGN KEY `KategorienArtikel` (`Kategorie-Nr`)
    REFERENCES `NordwindEx`.`Kategorien` (`Kategorie-Nr`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `LieferantenArtikel` FOREIGN KEY `LieferantenArtikel` (`Lieferanten-Nr`)
    REFERENCES `NordwindEx`.`Lieferanten` (`Lieferanten-Nr`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = INNODB;

Okay, you just have to enable the Administer permission for both the MSysObjects and MSysQueries tables AND for MSysRelationships !!

In the online documentation, it's not written (http://dev.mysql.com/doc/migration-toolkit/en/mysql-migration-toolkit-accessprep.html).

Perhaps I didn't see it but, you should add that you Have to enable the Administer permission for MSysRelationships.

Thanks for discovering what was wrong. I assign this bug to the documentation team so they can fix the description.

Closed with the following commit:

svn commit - mysqldoc@docsrva: r3105 - trunk/migration-toolkit

 - added system relationship table

It was greatfull to work with your team, your are really reactive
Thanks