MySQL error 1005: Can't create table (lexeme database)

233 Views Asked by At

I've designed a MySQL database with 8 tables. I've written the SQL to generate each table with its keys and constraints. But one table always fail on creation, and there's no useful error message that gives a hint about what the problem is. I've described my database on GitHub. Zoom in on the diagram to get an idea how things are connected:

https://github.com/gustafl/Lexeme/wiki/Lexeme-data-model

And here's the code to generate the first 7 tables:

CREATE TABLE `grammatical_category` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `grammeme` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `grammatical_category` tinyint(3) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `grammatical_category_fk_idx` (`grammatical_category`),
  CONSTRAINT `grammeme_grammatical_category_fk` FOREIGN KEY (`grammatical_category`) REFERENCES `grammatical_category` (`id`)
);

CREATE TABLE `lexical_category` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `language` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `code` varchar(5) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_idx` (`code`)
);

CREATE TABLE `lexeme` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `language` tinyint(3) unsigned NOT NULL,
  `lexical_category` tinyint(3) unsigned NOT NULL,
  `spelling` varchar(100) NOT NULL,
  `pronounciation` varchar(100),
  PRIMARY KEY (`id`),
  KEY `language_fk_idx` (`language`),
  KEY `lexical_category_fk_idx` (`lexical_category`),
  CONSTRAINT `lexeme_language_fk` FOREIGN KEY (`language`) REFERENCES `language` (`id`),
  CONSTRAINT `lexeme_lexical_category_fk` FOREIGN KEY (`lexical_category`) REFERENCES `lexical_category` (`id`)
);

CREATE TABLE `inflection` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lexeme` int(10) unsigned NOT NULL,
  `spelling` varchar(100) NOT NULL,
  `pronounciation` varchar(100),
  PRIMARY KEY (`id`),
  KEY `lexeme_fk_idx` (`lexeme`),
  CONSTRAINT `inflection_lexeme_fk` FOREIGN KEY (`lexeme`) REFERENCES `lexeme` (`id`)
);

CREATE TABLE `inflection_grammatical_category` (
  `inflection` int(10) unsigned NOT NULL,
  `grammatical_category` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`inflection`,`grammatical_category`),
  KEY `inflection_fk_idx` (`inflection`),
  KEY `grammatical_category_fk_idx` (`grammatical_category`),
  CONSTRAINT `inflection_grammatical_category_grammatical_category_fk` FOREIGN KEY (`grammatical_category`) REFERENCES `grammatical_category` (`id`),
  CONSTRAINT `inflection_grammatical_category_inflection_fk` FOREIGN KEY (`inflection`) REFERENCES `inflection` (`id`)
);

Here's the last table that fails:

CREATE TABLE `lexeme_grammatical_category` (
  `lexeme` int(10) unsigned NOT NULL,
  `grammatical_category` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`lexeme`,`grammatical_category`),
  KEY `lexeme_fk_idx` (`lexeme`),
  KEY `grammatical_category_fk_idx` (`grammatical_category`),
  CONSTRAINT `lexeme_grammatical_category_grammatical_category_fk` FOREIGN KEY (`grammatical_category`) REFERENCES `grammatical_category` (`id`),
  CONSTRAINT `lexeme_grammatical_category_lexeme_fk` FOREIGN KEY (`lexeme`) REFERENCES `lexeme` (`id`)
);

The lexeme_grammatical_category table is identical to inflection_grammatical_category, except that "inflection" is replaced with "lexeme" everywhere. I've done a search and replace to be sure. I've also tried creating lexeme_grammatical_category before inflection_grammatical_category, but it still fails.

I just can't see what's wrong. I wondered it could be a circular reference problem, because lexeme and inflection are connected too. But lexeme_grammatical_category fails before I create inflection_grammatical_category, so I can't see how.

0

There are 0 best solutions below