File: demo.sql

CREATE DATABASE `demo`;

USE `demo`;


CREATE TABLE `cities` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int(11) unsigned NOT NULL,
  `state_id` int(11) unsigned NOT NULL,
  `name` varchar(100) NOT NULL DEFAULT '',
  `code` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `cities` (`id`, `country_id`, `state_id`, `name`, `code`)
VALUES
	(1,1,1,'Hubli',NULL),
	(2,1,1,'Dharwad',NULL),
	(3,1,2,'Chennai',NULL),
	(4,1,2,'Madhurai',NULL);




CREATE TABLE `countries` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `code` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `countries` (`id`, `name`, `code`)
VALUES
	(1,'India','IN');



CREATE TABLE `states` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int(11) unsigned NOT NULL,
  `name` varchar(100) NOT NULL DEFAULT '',
  `code` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `country_id` (`country_id`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `states` (`id`, `country_id`, `name`, `code`)
VALUES
	(1,1,'Karnataka',NULL),
	(2,1,'Tamilnadu',NULL);