uPSt6Msc

1CREATE TABLES:
2
3CREATE TABLE IF NOT EXISTS `pn_city` (
4  `id` bigint(20) NOT NULL auto_increment,
5  `country_id` smallint(6) NOT NULL,
6  `region_id` smallint(6) NOT NULL,
7  `name` varchar(45) collate utf8_bin NOT NULL,
8  `latitude` float NOT NULL,
9  `longitude` float NOT NULL,
10  `timezone` varchar(10) collate utf8_bin NOT NULL,
11  PRIMARY KEY  (`id`)
12) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=42333 ;
13
14CREATE TABLE IF NOT EXISTS `pn_country` (
15  `id` bigint(20) NOT NULL auto_increment,
16  `name` varchar(50) collate utf8_bin NOT NULL,
17  `abbr` varchar(2) collate utf8_bin NOT NULL,
18  `currency_code` varchar(3) collate utf8_bin default NULL,
19  `full_name` varchar(100) collate utf8_bin default NULL,
20  PRIMARY KEY  (`id`)
21) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=276 ;
22
23CREATE TABLE IF NOT EXISTS `pn_region` (
24  `id` bigint(20) NOT NULL auto_increment,
25  `country_id` bigint(20) NOT NULL,
26  `name` varchar(45) collate utf8_bin NOT NULL,
27  `code` varchar(8) collate utf8_bin NOT NULL,
28  PRIMARY KEY  (`id`),
29  KEY `country_id` (`country_id`)
30) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5400 ;
31
32ALTER TABLE `pn_region`
33  ADD CONSTRAINT `pn_region_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `pn_country` (`id`),
34  ADD CONSTRAINT `pn_region_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `pn_country` (`id`);
35
36
37-- SQL query:
38-- 
39-- ALTER TABLE pn_city ADD FOREIGN KEY ( region_id ) REFERENCES pn_region( id )
40-- 
41-- MySQL said:
42-- 
43-- #1005 - Can't create table '.\parton\#sql-aa4_e1.frm' (errno: 150) 
44-- 
45-- It's still missing the pn_city and pn_country relation after this one.... =\
46--
47-- ALTER TABLE pn_city ADD FOREIGN KEY ( country_id ) REFERENCES pn_country( id )