error en base datos

[gnzsoloyo]

La idea sería:

Código MySQL:

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';
 
DROP SCHEMA IF EXISTS `prueba` ;
CREATE SCHEMA IF NOT EXISTS `prueba` DEFAULT CHARACTER SET latin1 ;
USE `prueba` ;
 
-- -----------------------------------------------------
-- Table `administrador`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `administrador` ;
 
CREATE  TABLE IF NOT EXISTS `administrador` (
  `adm_cod` INT(11) NOT NULL AUTO_INCREMENT ,
  `adm_usuario` VARCHAR(10) NOT NULL COMMENT 'nombre del usuario' ,
  `adm_clav` VARCHAR(10) NOT NULL COMMENT 'contraseña del sistema' ,
  `adm_nom` VARCHAR(10) NOT NULL ,
  `adm_apell` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`adm_cod`) ,
  UNIQUE INDEX `adm_usuario` (`adm_usuario` ASC) ,
  UNIQUE INDEX `adm_clav` (`adm_clav` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'administracion de validacion de usuarios';
 
 
-- -----------------------------------------------------
-- Table `sede`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `sede` ;
 
CREATE  TABLE IF NOT EXISTS `sede` (
  `sed_cod` VARCHAR(20) NOT NULL COMMENT 'sede' ,
  `sed_nom` VARCHAR(20) NULL DEFAULT NULL ,
  `sed_ubic` VARCHAR(20) NOT NULL COMMENT 'ubicacion' ,
  `sed_telf` CHAR(10) NOT NULL COMMENT 'telefono' ,
  PRIMARY KEY (`sed_cod`) ,
  UNIQUE INDEX `sed_nom` (`sed_nom` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
 
 
-- -----------------------------------------------------
-- Table `tipo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tipo` ;
 
CREATE  TABLE IF NOT EXISTS `tipo` (
  `tip_cod` VARCHAR(20) NOT NULL ,
  `tip_nom` CHAR(20) NOT NULL COMMENT 'nombre' ,
  PRIMARY KEY (`tip_cod`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'tipo de afiliado\r\n';
 
 
-- -----------------------------------------------------
-- Table `afiliados`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `afiliados` ;
 
CREATE  TABLE IF NOT EXISTS `afiliados` (
  `afil_cod` VARCHAR(50) NOT NULL COMMENT 'codigo' ,
  `afil_nom` VARCHAR(20) NOT NULL COMMENT 'nombre' ,
  `afil_apell` VARCHAR(20) NOT NULL COMMENT 'apellido' ,
  `afil_sex` CHAR(1) NOT NULL COMMENT 'sexo' ,
  `afil_ced` VARCHAR(8) NULL DEFAULT NULL COMMENT 'cedula' ,
  `afil_ed` VARCHAR(2) NOT NULL COMMENT 'edad' ,
  `afil_fnac` DATE NOT NULL COMMENT 'fecha de nacimiento' ,
  `afil_direcc` CHAR(100) NOT NULL COMMENT 'direccion' ,
  `afil_telf` CHAR(10) NOT NULL COMMENT 'telefono' ,
  `afil_tip` VARCHAR(20) NOT NULL ,
  `afil_sed` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`afil_cod`) ,
  UNIQUE INDEX `afil_ced` (`afil_ced` ASC) ,
  INDEX `Relationship3` (`afil_tip` ASC) ,
  INDEX `Relationship5` (`afil_sed` ASC) ,
  CONSTRAINT `Relationship5`
    FOREIGN KEY (`afil_sed` )
    REFERENCES `sede` (`sed_cod` ),
  CONSTRAINT `Relationship3`
    FOREIGN KEY (`afil_tip` )
    REFERENCES `tipo` (`tip_cod` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'afiliados al ipasme (pacientes)\r\n';
 
 
-- -----------------------------------------------------
-- Table `historia`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `historia` ;
 
CREATE  TABLE IF NOT EXISTS `historia` (
  `hist_cod` VARCHAR(20) NOT NULL ,
  `hist_adm` INT(11) NOT NULL ,
  `afil_cod` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`hist_cod`) ,
  INDEX `Relationship14` (`hist_adm` ASC) ,
  INDEX `fk_historia_afiliados1` (`afil_cod` ASC) ,
  CONSTRAINT `Relationship14`
    FOREIGN KEY (`hist_adm` )
    REFERENCES `administrador` (`adm_cod` ),
  CONSTRAINT `fk_historia_afiliados1`
    FOREIGN KEY (`afil_cod` )
    REFERENCES `afiliados` (`afil_cod` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COMMENT = 'historia medica\r\n';
 
 
-- -----------------------------------------------------
-- Table `especialidad`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `especialidad` ;
 
CREATE  TABLE IF NOT EXISTS `especialidad` (
  `esp_cod` VARCHAR(20) NOT NULL ,
  `esp_nom` VARCHAR(20) NULL DEFAULT NULL ,
  `esp_ubic` CHAR(20) NOT NULL ,
  PRIMARY KEY (`esp_cod`) ,
  UNIQUE INDEX `esp_nom` (`esp_nom` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
 
 
-- -----------------------------------------------------
-- Table `medico`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `medico` ;
 
CREATE  TABLE IF NOT EXISTS `medico` (
  `med_cod` VARCHAR(20) NOT NULL COMMENT 'codigo' ,
  `med_nom` VARCHAR(20) NOT NULL COMMENT 'nombre' ,
  `med_apell` VARCHAR(20) NOT NULL COMMENT 'apellido' ,
  `med_sex` CHAR(1) NOT NULL COMMENT 'sex' ,
  `med_ced` VARCHAR(8) NOT NULL COMMENT 'cedula' ,
  `med_telf` VARCHAR(10) NOT NULL COMMENT 'telefono' ,
  `med_tur` CHAR(10) NOT NULL COMMENT 'turno' ,
  `med_cons` CHAR(2) NOT NULL COMMENT 'consultorio' ,
  `med_esp` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`med_cod`) ,
  UNIQUE INDEX `med_ced` (`med_ced` ASC) ,
  INDEX `Relationship15` (`med_esp` ASC) ,
  CONSTRAINT `Relationship15`
    FOREIGN KEY (`med_esp` )
    REFERENCES `especialidad` (`esp_cod` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
 
 
-- -----------------------------------------------------
-- Table `cita`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `cita` ;
 
CREATE  TABLE IF NOT EXISTS `cita` (
  `cit_cod` VARCHAR(20) NOT NULL COMMENT 'codigo' ,
  `cit_med` VARCHAR(20) NOT NULL ,
  `cit_fech` DATE NOT NULL COMMENT 'fecha' ,
  `cit_hora` TIME NOT NULL ,
  `cit_hist` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`cit_cod`) ,
  INDEX `Relationship18` (`cit_med` ASC) ,
  INDEX `Relationship7` (`cit_hist` ASC) ,
  CONSTRAINT `Relationship7`
    FOREIGN KEY (`cit_hist` )
    REFERENCES `historia` (`hist_cod` ),
  CONSTRAINT `Relationship18`
    FOREIGN KEY (`cit_med` )
    REFERENCES `medico` (`med_cod` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
 
 
-- -----------------------------------------------------
-- Table `laboratorio`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `laboratorio` ;
 
CREATE  TABLE IF NOT EXISTS `laboratorio` (
  `lab_cod` VARCHAR(20) NOT NULL ,
  `lab_cit` VARCHAR(20) NOT NULL ,
  `lab_nom` VARCHAR(20) NOT NULL ,
  `lab_descp` VARCHAR(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`lab_cod`) ,
  UNIQUE INDEX `lab_nom` (`lab_nom` ASC) ,
  INDEX `Relationship16` (`lab_cit` ASC) ,
  CONSTRAINT `Relationship16`
    FOREIGN KEY (`lab_cit` )
    REFERENCES `cita` (`cit_cod` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
 
 
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
 

Y su graficación: