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.