
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- ag2r_region
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ag2r_region`;


CREATE TABLE `ag2r_region`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`hierarchy_id` INTEGER default 1 NOT NULL,
	`hierarchy_branche` VARCHAR(255)  NOT NULL,
	`hierarchy_region` VARCHAR(255)  NOT NULL,
	`description` TEXT,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_hierarchy_branche_region` (`hierarchy_branche`, `hierarchy_region`),
	INDEX `ag2r_region_FI_1` (`hierarchy_id`),
	CONSTRAINT `ag2r_region_FK_1`
		FOREIGN KEY (`hierarchy_id`)
		REFERENCES `hierarchy` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ag2r_department
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ag2r_department`;


CREATE TABLE `ag2r_department`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`code` INTEGER  NOT NULL,
	`name` VARCHAR(255)  NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_name` (`name`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ag2r_region_department
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ag2r_region_department`;


CREATE TABLE `ag2r_region_department`
(
	`ag2r_region_id` INTEGER  NOT NULL,
	`ag2r_department_id` INTEGER  NOT NULL,
	UNIQUE KEY `unique_ag2r_region_department` (`ag2r_region_id`, `ag2r_department_id`),
	CONSTRAINT `ag2r_region_department_FK_1`
		FOREIGN KEY (`ag2r_region_id`)
		REFERENCES `ag2r_region` (`id`)
		ON DELETE CASCADE,
	INDEX `ag2r_region_department_FI_2` (`ag2r_department_id`),
	CONSTRAINT `ag2r_region_department_FK_2`
		FOREIGN KEY (`ag2r_department_id`)
		REFERENCES `ag2r_department` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ag2r_ape_family
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ag2r_ape_family`;


CREATE TABLE `ag2r_ape_family`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255)  NOT NULL,
	`sort` INTEGER,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_name` (`name`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ag2r_ape
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ag2r_ape`;


CREATE TABLE `ag2r_ape`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`ag2r_ape_family_id` INTEGER  NOT NULL,
	`code` VARCHAR(255)  NOT NULL,
	`name` VARCHAR(255)  NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_code` (`code`),
	INDEX `ag2r_ape_FI_1` (`ag2r_ape_family_id`),
	CONSTRAINT `ag2r_ape_FK_1`
		FOREIGN KEY (`ag2r_ape_family_id`)
		REFERENCES `ag2r_ape_family` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
