Conversation

Notices

1. Embed this notice
   silverwizard (silverwizard@convenient.email)'s status on Monday, 10-Jul-2023 23:27:49 JST silverwizard

   in reply to

   • hypolite
   @hypolite 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"
   • Embed this notice
     hypolite (hypolite@friendica.mrpetovan.com)'s status on Monday, 10-Jul-2023 23:27:51 JST hypolite

     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