PHPMyAdmin - InnoDB tables will not join
This is my database structure:
CREATE database mytvguide
CREATE TABLE IF NOT EXISTS `channels` (
`id` int(11) NOT NULL auto_increment,
`channel1` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `episodeairings` (
`id` mediumint(255) unsigned NOT NULL auto_increment,
`programme` varchar(255) collate utf8_unicode_ci NOT NULL,
`channel` varchar(255) collate utf8_unicode_ci default NULL,
`airdate` datetime default NULL,
`displayair` datetime default NULL,
`expiration` datetime default NULL,
`epname` varchar(256) collate utf8_unicode_ci NOT NULL,
`epno` mediumint(255) unsigned NOT NULL,
`epseries` mediumint(255) unsigned NOT NULL,
`setreminder` varchar(255) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`id`),
KEY `channel` (`channel`),
K开发者_如何转开发EY `programme` (`programme`),
KEY `setreminder` (`setreminder`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC AUTO_INCREMENT=3 ;
INSERT INTO `episodeairings` (`id`, `programme`, `channel`, `airdate`, `displayair`, `expiration`, `epname`, `setreminder`) VALUES
(1, 'TV Programme 1', 'ITV2', '2011-07-09 22:35:00', '2011-06-30 22:35:00', '2011-06-30 23:05:00', 'Episode', '' , '', NULL),
(2, 'TV Programme 1', 'ITV2', '2011-07-10 02:25:00', '2011-07-01 02:25:00', '2011-07-01 02:55:00', 'EpisodeTest', '1', '2', NULL);
CREATE TABLE IF NOT EXISTS `episode` (
`id` int(11) NOT NULL auto_increment,
`epname` varchar(255) NOT NULL,
`seriesnumber` int(11) NOT NULL,
`episodenumber` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `epname` (`epname`),
KEY `seriesnumber` (`seriesnumber`),
KEY `episodenumber` (`episodenumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `episode` (`id`, `epname`, `seriesnumber`, `episodenumber`) VALUES
(1, 'Episode', 1, 1);
CREATE TABLE IF NOT EXISTS `programme1` (
`id` int(11) NOT NULL auto_increment,
`programme` varchar(255) NOT NULL default '<a href="pca1.php">Police, Camera, Action!</a>',
PRIMARY KEY (`id`),
KEY `programme` (`programme`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
INSERT INTO `programme1` (`id`, `programme`) VALUES
(1, '<a href="prog1.php">TV Programme 1</a>');
INSERT INTO `channels` (`id`, `channel`) VALUES
(1, '<a href="TV2.php">ITV2</a>');
For some reason I can't link any of the tables in episodeairings - namely programme, channel, airdate, epname, epno, epseries with those in the other tables (which are programme, epname, seriesnumber, episodenumber). Basically, the dropdown won't happen at all for linked tables, as it should do.
This is despite the fact my database is stored as InnoDB via PHPmyadmin and I set the linked tables.
Why is this and how can I fix it?
I am not sure how phpmyadmin works but I would assume that it requires you to define some foreign key constraints between your tables (e.g. using the alter table statement).
See alter table docs: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
精彩评论