The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Foreign keys/default values/load data infile

Discussion in 'General Discussion' started by keopz, May 9, 2009.

  1. keopz

    keopz Registered

    Joined:
    May 9, 2009
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Hello all, I'm trying to build a MySQL based site with Cpanel X and i've having a lot of problems. I used MySQL Workbench to do a schema and couldn't upload the .sql file. On that file was also a constraint to foreign keys and didn't work either. I wasn't abble to discover also why couldn't I use the DEFAULT value in CREATE TABLE query.

    Any help on MySQL Workbench vs Cpanel will help.
    Here goes the .sql forward engineered file:
    ________________________________________________

    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';


    -- -----------------------------------------------------
    -- Table `Concelho`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `Concelho` (
    `idConcelho` INT NOT NULL AUTO_INCREMENT ,
    `Nome` VARCHAR(45) NOT NULL ,
    PRIMARY KEY (`idConcelho`) )
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `AreaActividade`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `AreaActividade` (
    `idAreaActividade` SMALLINT NOT NULL AUTO_INCREMENT ,
    `Nome` VARCHAR(45) NOT NULL ,
    PRIMARY KEY (`idAreaActividade`) )
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `Empresa`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `Empresa` (
    `idEmpresa` SMALLINT NOT NULL AUTO_INCREMENT ,
    `NomeEmpresa` VARCHAR(45) NOT NULL ,
    `AreaActividade` VARCHAR(45) NOT NULL ,
    `Email` VARCHAR(45) NOT NULL ,
    `Telefone` INT NOT NULL ,
    `Morada` VARCHAR(45) NOT NULL ,
    `CodPostal` INT NOT NULL ,
    `Concelho` VARCHAR(45) NOT NULL ,
    `Nif` INT NOT NULL ,
    `NomeResponsavel` VARCHAR(45) NOT NULL ,
    `Password` VARCHAR(45) NULL ,
    PRIMARY KEY (`idEmpresa`) ,
    INDEX `Concelho` (`Concelho` ASC) ,
    INDEX `AreaActividade` (`AreaActividade` ASC) ,
    CONSTRAINT `Concelho`
    FOREIGN KEY (`Concelho` )
    REFERENCES `artestav_empresas`.`Concelho` (`idConcelho` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `AreaActividade`
    FOREIGN KEY (`AreaActividade` )
    REFERENCES `artestav_empresas`.`AreaActividade` (`idAreaActividade` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

    ENGINE = InnoDB
    DEFAULT CHARACTER SET = latin1
    COLLATE = latin1_general_ci;


    -- -----------------------------------------------------
    -- Table `Requerente`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `Requerente` (
    `idRequerente` SMALLINT NOT NULL AUTO_INCREMENT ,
    `NomeRequerente` VARCHAR(45) NOT NULL ,
    `Email` VARCHAR(45) NOT NULL ,
    `Telefone` INT NULL ,
    `Morada` VARCHAR(45) NULL ,
    `CodPostal` INT NULL ,
    `Concelho` VARCHAR(45) NOT NULL ,
    `Password` VARCHAR(45) NOT NULL ,
    PRIMARY KEY (`idRequerente`) ,
    INDEX `Concelho` (`Concelho` ASC) ,
    CONSTRAINT `Concelho`
    FOREIGN KEY (`Concelho` )
    REFERENCES `artestav_empresas`.`Concelho` (`idConcelho` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `PedidoOrcamento`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `PedidoOrcamento` (
    `idPedido` SMALLINT NOT NULL AUTO_INCREMENT ,
    `idRequerente` SMALLINT NOT NULL ,
    `AreaActividade` VARCHAR(45) NOT NULL ,
    `PedidoDesc` VARCHAR(45) NOT NULL ,
    `DataAbertura` DATE NOT NULL ,
    `NumeroRespostas` INT NOT NULL ,
    PRIMARY KEY (`idPedido`) ,
    INDEX `PedidoRequerente` (`idRequerente` ASC) ,
    INDEX `AreaActividade` (`AreaActividade` ASC) ,
    CONSTRAINT `PedidoRequerente`
    FOREIGN KEY (`idRequerente` )
    REFERENCES `artestav_empresas`.`Requerente` (`idRequerente` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
    CONSTRAINT `AreaActividade`
    FOREIGN KEY (`AreaActividade` )
    REFERENCES `artestav_empresas`.`AreaActividade` (`idAreaActividade` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `Orcamento`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `Orcamento` (
    `idOrcamento` SMALLINT NOT NULL AUTO_INCREMENT ,
    `idEmpresa` SMALLINT NOT NULL ,
    `PedidoOrcamento` SMALLINT NOT NULL ,
    `OrcamentoDesc` VARCHAR(45) NOT NULL ,
    `Preço` INT NOT NULL ,
    `Data` DATE NOT NULL ,
    PRIMARY KEY (`idOrcamento`) ,
    INDEX `Pedido` (`PedidoOrcamento` ASC) ,
    INDEX `Empresa` (`idEmpresa` ASC) ,
    CONSTRAINT `Pedido`
    FOREIGN KEY (`PedidoOrcamento` )
    REFERENCES `artestav_empresas`.`PedidoOrcamento` (`idPedido` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `Empresa`
    FOREIGN KEY (`idEmpresa` )
    REFERENCES `artestav_empresas`.`Empresa` (`idEmpresa` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

    ENGINE = InnoDB;



    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
     
  2. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    I'm moving this thread out of the forums for developing apps based on cPanel/WHM hooks and apps so you may get some better responses.
     
  3. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    Make certain InnoDB is enable in the MySQL service. If you are not the server owner, you might need to address these issues to your service provider.
     
Loading...
Similar Threads - Foreign keys default
  1. Dev@SH
    Replies:
    1
    Views:
    111
  2. comptech520
    Replies:
    1
    Views:
    252

Share This Page