title: MySQL foreign key to another foreign key
body: <p>The idea is quite simple: i have three (or more) tables</p>
  
  <pre><code>- master_tbl (id, something, somethingelse)
  - tbl2 (id, ....)
  - tbl3 (id, ....)
  </code></pre>
  
  <p>Now what i want is a foreign key relationship, such as tbl3.id would point to tbl2.id and tbl2.id would point to master_tbl.id - all foreign keys are <code>ON UPDATE CASCADE</code> and <code>ON DELETE CASCADE</code>. What I'll get from this is that when I delete a record from tbl2, its tbl3 equivalent will get erased as well, but not master_tbl. When I delete a record from master_tbl, all three tables get erased. </p>
  
  <p>When I try to create the foreign key on tbl3.id->tbl2.id, I get mysql error 150 - can't create table (the tbl2.id->master_tbl.id is already created).
  My MySQL version is <code>5.1.46</code>. Any ideas why this might be?</p>
  
  <p><strong>EDIT: table definitions</strong>
  smf_members aka master_table</p>
  
  <pre><code>-- Table "smf_members" DDL
  
  CREATE TABLE `smf_members` (
    `id_member` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `member_name` varchar(80) NOT NULL DEFAULT '',
    `date_registered` int(10) unsigned NOT NULL DEFAULT '0',
    `posts` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `id_group` smallint(5) unsigned NOT NULL DEFAULT '0',
    `lngfile` varchar(255) NOT NULL DEFAULT '',
    `last_login` int(10) unsigned NOT NULL DEFAULT '0',
    `real_name` varchar(255) NOT NULL DEFAULT '',
    `instant_messages` smallint(5) NOT NULL DEFAULT '0',
    `unread_messages` smallint(5) NOT NULL DEFAULT '0',
    `new_pm` tinyint(3) unsigned NOT NULL DEFAULT '0',
    `buddy_list` text NOT NULL,
    `pm_ignore_list` varchar(255) NOT NULL DEFAULT '',
    `pm_prefs` mediumint(8) NOT NULL DEFAULT '0',
    `mod_prefs` varchar(20) NOT NULL DEFAULT '',
    `message_labels` text NOT NULL,
    `passwd` varchar(64) NOT NULL DEFAULT '',
    `openid_uri` text NOT NULL,
    `email_address` varchar(255) NOT NULL DEFAULT '',
    `personal_text` varchar(255) NOT NULL DEFAULT '',
    `gender` tinyint(4) unsigned NOT NULL DEFAULT '0',
    `birthdate` date NOT NULL DEFAULT '0001-01-01',
    `website_title` varchar(255) NOT NULL DEFAULT '',
    `website_url` varchar(255) NOT NULL DEFAULT '',
    `location` varchar(255) NOT NULL DEFAULT '',
    `icq` varchar(255) NOT NULL DEFAULT '',
    `aim` varchar(255) NOT NULL DEFAULT '',
    `yim` varchar(32) NOT NULL DEFAULT '',
    `msn` varchar(255) NOT NULL DEFAULT '',
    `hide_email` tinyint(4) NOT NULL DEFAULT '0',
    `show_online` tinyint(4) NOT NULL DEFAULT '1',
    `time_format` varchar(80) NOT NULL DEFAULT '',
    `signature` text NOT NULL,
    `time_offset` float NOT NULL DEFAULT '0',
    `avatar` varchar(255) NOT NULL DEFAULT '',
    `pm_email_notify` tinyint(4) NOT NULL DEFAULT '0',
    `karma_bad` smallint(5) unsigned NOT NULL DEFAULT '0',
    `karma_good` smallint(5) unsigned NOT NULL DEFAULT '0',
    `usertitle` varchar(255) NOT NULL DEFAULT '',
    `notify_announcements` tinyint(4) NOT NULL DEFAULT '1',
    `notify_regularity` tinyint(4) NOT NULL DEFAULT '1',
    `notify_send_body` tinyint(4) NOT NULL DEFAULT '0',
    `notify_types` tinyint(4) NOT NULL DEFAULT '2',
    `member_ip` varchar(255) NOT NULL DEFAULT '',
    `member_ip2` varchar(255) NOT NULL DEFAULT '',
    `secret_question` varchar(255) NOT NULL DEFAULT '',
    `secret_answer` varchar(64) NOT NULL DEFAULT '',
    `id_theme` tinyint(4) unsigned NOT NULL DEFAULT '0',
    `is_activated` tinyint(3) unsigned NOT NULL DEFAULT '1',
    `validation_code` varchar(10) NOT NULL DEFAULT '',
    `id_msg_last_visit` int(10) unsigned NOT NULL DEFAULT '0',
    `additional_groups` varchar(255) NOT NULL DEFAULT '',
    `smiley_set` varchar(48) NOT NULL DEFAULT '',
    `id_post_group` smallint(5) unsigned NOT NULL DEFAULT '0',
    `total_time_logged_in` int(10) unsigned NOT NULL DEFAULT '0',
    `password_salt` varchar(255) NOT NULL DEFAULT '',
    `ignore_boards` text NOT NULL,
    `warning` tinyint(4) NOT NULL DEFAULT '0',
    `passwd_flood` varchar(12) NOT NULL DEFAULT '',
    `pm_receive_from` tinyint(4) unsigned NOT NULL DEFAULT '1',
    PRIMARY KEY (`id_member`),
    KEY `member_name` (`member_name`),
    KEY `real_name` (`real_name`),
    KEY `date_registered` (`date_registered`),
    KEY `id_group` (`id_group`),
    KEY `birthdate` (`birthdate`),
    KEY `posts` (`posts`),
    KEY `last_login` (`last_login`),
    KEY `lngfile` (`lngfile`(30)),
    KEY `id_post_group` (`id_post_group`),
    KEY `warning` (`warning`),
    KEY `total_time_logged_in` (`total_time_logged_in`),
    KEY `id_theme` (`id_theme`)
  ) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>cyp_users aka tbl2</p>
  
  <pre><code>-- Table "cyp_users" DDL
  
  CREATE TABLE `cyp_users` (
    `id` mediumint(8) unsigned NOT NULL,
    `role` varchar(255) NOT NULL DEFAULT 'unregistered',
    PRIMARY KEY (`id`),
    CONSTRAINT `cyp_users_ibfk_1` FOREIGN KEY (`id`) REFERENCES `smf_members` (`id_member`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>cyp_vip_users aka tbl3</p>
  
  <pre><code>-- Table "cyp_vip_users" DDL
  
  CREATE TABLE `cyp_vip_users` (
    `id` mediumint(8) NOT NULL,
    `od` date NOT NULL,
    `do` date NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>