Foreign keys/default values/load data infile

keopz

Registered
May 9, 2009
1
0
51
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 @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@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 [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,607
79
458
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.
 
Thread starter Similar threads Forum Replies Date
H Databases 1
D Databases 2