Skip to main content

Question for the #MySQL / #MariaDB buffs, I have three tables defined thus:

CREATE TABLE IF NOT EXISTS `user` (
	`uid` mediumint unsigned NOT NULL auto_increment COMMENT 'sequential ID',
	...
	 PRIMARY KEY(`uid`)
);
CREATE TABLE IF NOT EXISTS `gserver` (
	`id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
	...
	 PRIMARY KEY(`id`)
);

CREATE TABLE IF NOT EXISTS `user-gserver` (
	`uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
	`gsid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Gserver id',
	...
	PRIMARY KEY(`uid`,`gsid`),
	FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
	FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
);

Running the last query triggers the error Foreign key constraint is incorrectly formed. Is there no way to reference multiple tables in foreign keys out of a compound primary key?

#SQL #Database #DBA

#mysql #Database #sql #MariaDB #dba
in reply to Hypolite Petovan

silverwizard
silverwizard
•

@Hypolite Petovan I see nothing wrong with that - the foreign key in both cases is against a primary key which is what that error usually complains about.

So one upvote for "huh, that's dumb"

@Hypolite Petovan
in reply to Hypolite Petovan

Karl Levik Ⓥ
Karl Levik Ⓥ
• •
It's because gserver id is defined as 'int unsigned' whereas in user-gserver the corresponding column is defined as 'mediumint unsigned'. So there is a data type mismatch.
in reply to Karl Levik Ⓥ

Hypolite Petovan
Hypolite Petovan
• •
@Karl Levik Ⓥ Changing the `user-gserver`.`gsid` field type to int unsigned enabled the creation of the table. 😌
@Karl Levik Ⓥ
in reply to Hypolite Petovan

Karl Levik Ⓥ
Karl Levik Ⓥ
• •
Glad I could help! MySQL and MariaDB are super-strict on this. In your case it makes sense (although the error message could be more helpful!), but they will also refuse to accept a mismatch on things that shouldn't matter, e.g. int(10) vs int(11) - where the numbers in parentheses only matter for display/formatting purposes inside the mysql and mariadb command-line clients.
⇧