Error Code: 1701.

[volldamm69]

Hola a todos,

a ver si alguien me puede ayudar porque creo que tengo errores en la definicion de mi esquema.

Intento hacer un TRUNCATE de la tabla JOB, pero me da este error:

Código MySQL:

Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint 
(`elea`.`favorites`, CONSTRAINT `FAVORITES_JOB_FK` FOREIGN KEY (`JOB_ID`) REFERENCES 
`elea`.`job` (`ID`))
 

a pesar de que la tabla FAVORITES está vacía. También me da error al hacer inserts en algunas tablas, por ejemplo:

Código MySQL:

INSERT INTO job (ID, USER_ID, TITLE, DESCRIPTION, COUNTRY_ID, TOWN_ID, PROVINCE_ID, IS_OFFER, 
IS_ACTIVE, PAYMENT_MODE, AMOUNT, DATE_CREATED, DATE_MODIFIED, DATE_DELETED) 
VALUES (2, 4, 'Electricista', 'Una .....', 0, 0, 8, 1, 1, 'H', 80, '2015-07-28 13:27:42', '2015-07-28 13:28:57', 
NULL)   
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`elea`.`job`, 
CONSTRAINT `JOB_TOWN_FK` FOREIGN KEY (`TOWN_ID`) REFERENCES `town` (`ID`) ON DELETE 
CASCADE ON UPDATE CASCADE)
 

Las tablas COUNTRY, TOWN, PROVINCE y USER están cargadas.

El esquema es:

Código MySQL:

-- MySQL Workbench Forward Engineering
 
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
 
-- -----------------------------------------------------
-- Schema elea
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `elea` ;
 
-- -----------------------------------------------------
-- Schema elea
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `elea` DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci ;
USE `elea` ;
 
-- -----------------------------------------------------
-- Table `elea`.`COUNTRY`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `elea`.`COUNTRY` ;
 
CREATE TABLE IF NOT EXISTS `elea`.`COUNTRY` (
`ID` INT NOT NULL,
`NAME` VARCHAR(45) NOT NULL,
`AVAILABLE` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `elea`.`PROVINCE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `elea`.`PROVINCE` ;
 
CREATE TABLE IF NOT EXISTS `elea`.`PROVINCE` (
`ID` INT NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(45) NOT NULL,
`COUNTRY_ID` INT NOT NULL,
PRIMARY KEY (`ID`),
INDEX `COUNTRY_FK_idx` (`COUNTRY_ID` ASC),
CONSTRAINT `COUNTRY_FK`
FOREIGN KEY (`COUNTRY_ID`)
REFERENCES `elea`.`COUNTRY` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `elea`.`TOWN`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `elea`.`TOWN` ;
 
CREATE TABLE IF NOT EXISTS `elea`.`TOWN` (
`ID` INT NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(100) NOT NULL,
`PROVINCE_ID` INT NOT NULL,
`DC` INT NOT NULL,
`COD_MUNICIPIO` INT NOT NULL,
PRIMARY KEY (`ID`),
INDEX `PROVINCE_FK_idx` (`PROVINCE_ID` ASC),
CONSTRAINT `PROVINCE_FK`
FOREIGN KEY (`PROVINCE_ID`)
REFERENCES `elea`.`PROVINCE` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `elea`.`USER`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `elea`.`USER` ;
 
CREATE TABLE IF NOT EXISTS `elea`.`USER` (
`ID` INT NOT NULL,
`USER_KEY` VARCHAR(100) NULL,
`EMAIL` VARCHAR(150) NULL,
`MAIL_NOTIF_ACTIVE` TINYINT(1) NULL DEFAULT 1,
`DEVICE_NOTIF_ACTIVE` TINYINT(1) NULL DEFAULT 1,
`DEVICE_ID` VARCHAR(100) NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `elea`.`JOB`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `elea`.`JOB` ;
 
CREATE TABLE IF NOT EXISTS `elea`.`JOB` (
`ID` INT NOT NULL,
`USER_ID` INT NOT NULL,
`TITLE` VARCHAR(100) NULL,
`DESCRIPTION` VARCHAR(4000) NULL,
`COUNTRY_ID` INT NULL,
`TOWN_ID` INT NULL,
`PROVINCE_ID` INT NULL,
`IS_OFFER` TINYINT(1) NOT NULL,
`IS_ACTIVE` TINYINT(1) NULL DEFAULT 1,
`PAYMENT_MODE` VARCHAR(1) NULL,
`AMOUNT` INT NULL,
`DATE_CREATED` DATE NULL,
`DATE_MODIFIED` DATE NULL,
`DATE_DELETED` DATE NULL,
PRIMARY KEY (`ID`),
INDEX `COUNTRY_FK_idx` (`COUNTRY_ID` ASC),
INDEX `PROVINCE_FK_idx` (`PROVINCE_ID` ASC),
INDEX `TOWN_FK_idx` (`TOWN_ID` ASC),
INDEX `USER_FK_idx` (`USER_ID` ASC),
CONSTRAINT `JOB_COUNTRY_FK`
FOREIGN KEY (`COUNTRY_ID`)
REFERENCES `elea`.`COUNTRY` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `JOB_PROVINCE_FK`
FOREIGN KEY (`PROVINCE_ID`)
REFERENCES `elea`.`PROVINCE` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `JOB_TOWN_FK`
FOREIGN KEY (`TOWN_ID`)
REFERENCES `elea`.`TOWN` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `JOB_USER_FK`
FOREIGN KEY (`USER_ID`)
REFERENCES `elea`.`USER` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `elea`.`FAVORITES`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `elea`.`FAVORITES` ;
 
CREATE TABLE IF NOT EXISTS `elea`.`FAVORITES` (
`ID` INT NOT NULL,
`USER_ID` INT NULL,
`JOB_ID` INT NULL,
PRIMARY KEY (`ID`),
INDEX `USER_FK_idx` (`USER_ID` ASC),
INDEX `JOB_FK_idx` (`JOB_ID` ASC),
CONSTRAINT `FAVORITES_USER_FK`
FOREIGN KEY (`USER_ID`)
REFERENCES `elea`.`USER` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `FAVORITES_JOB_FK`
FOREIGN KEY (`JOB_ID`)
REFERENCES `elea`.`JOB` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `elea`.`MESSAGES`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `elea`.`MESSAGES` ;
 
CREATE TABLE IF NOT EXISTS `elea`.`MESSAGES` (
`ID` INT NOT NULL,
`SENDER_ID` INT NOT NULL,
`RECEIVER_ID` INT NOT NULL,
`MESSAGE_TEXT` VARCHAR(4000) NOT NULL,
`IS_READ` TINYINT(1) NULL DEFAULT 0,
`DATE_CREATED` DATE NULL,
`DATE_MODIFIED` DATE NULL,
`DATE_DELETED` DATE NULL,
`JOB_ID` INT NULL,
`SUBJECT` VARCHAR(100) NULL,
PRIMARY KEY (`ID`),
INDEX `MESSAGES_SENDER_FK_idx` (`SENDER_ID` ASC),
INDEX `MESSAGES_RECEIVER_FK_idx` (`RECEIVER_ID` ASC),
INDEX `MESSAGES_JOB_FK_idx` (`JOB_ID` ASC),
CONSTRAINT `MESSAGES_SENDER_FK`
FOREIGN KEY (`SENDER_ID`)
REFERENCES `elea`.`USER` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `MESSAGES_RECEIVER_FK`
FOREIGN KEY (`RECEIVER_ID`)
REFERENCES `elea`.`USER` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `MESSAGES_JOB_FK`
FOREIGN KEY (`JOB_ID`)
REFERENCES `elea`.`JOB` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
 
 
-- -----------------------------------------------------
-- Table `elea`.`PROPERTIES`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `elea`.`PROPERTIES` ;
 
CREATE TABLE IF NOT EXISTS `elea`.`PROPERTIES` (
`ID` INT NOT NULL,
`PROP_NAME` VARCHAR(45) NULL,
`PROP_VALUE` VARCHAR(200) NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;
 
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
 

[/QUOTE]



Gracias de antemano