More foreign key in creating MySQL

Question

i am trying to create DB with this script. But i am in the black hole. I don't know what to do now. Here is my MySQL code. Please somebody for kick me up.

If i use one foreign key it is ok, but i do not know how to use more of them.

EROOR IS:

#1005 - Can't create table 'ruda_dev_souteze2.results' (errno: 150)

QUERY IS:

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

CREATE SCHEMA IF NOT EXISTS `ruda_dev_souteze2` DEFAULT CHARACTER SET utf8 COLLATE utf8_czech_ci ;

USE `ruda_dev_souteze2` ;

-- -----------------------------------------------------
-- Table `ruda_dev_souteze2`.`contests`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ruda_dev_souteze2`.`contests` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  `date` DATE NOT NULL ,
  `public` TINYINT(1) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 11
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `ruda_dev_souteze2`.`people`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ruda_dev_souteze2`.`people` (
  `contest_id` BIGINT(20) NOT NULL ,
  `number` INT(11) NOT NULL ,
  `category` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  `fname` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  `lname` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  PRIMARY KEY (`contest_id`, `number`) ,
  INDEX `id_idx` (`contest_id` ASC) ,
  CONSTRAINT `cidfp`
    FOREIGN KEY (`contest_id` )
    REFERENCES `ruda_dev_souteze2`.`contests` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `ruda_dev_souteze2`.`subjects`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ruda_dev_souteze2`.`subjects` (
  `contest_id` BIGINT(20) NOT NULL ,
  `number` INT(11) NOT NULL ,
  `name` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  `direct` TINYINT(1) NOT NULL ,
  `type` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  `subjectscol` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NULL DEFAULT NULL ,
  PRIMARY KEY (`contest_id`, `number`) ,
  INDEX `id_idx` (`contest_id` ASC) ,
  CONSTRAINT `cidfs`
    FOREIGN KEY (`contest_id` )
    REFERENCES `ruda_dev_souteze2`.`contests` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `ruda_dev_souteze2`.`results`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ruda_dev_souteze2`.`results` (
  `contest_id` BIGINT(20) NOT NULL ,
  `subject_number` INT(11) NOT NULL ,
  `people_number` INT(11) NOT NULL ,
  `number_8c2` FLOAT(10,2) NULL DEFAULT NULL ,
  `time_time` TIME NULL DEFAULT NULL ,
  `time_frag` FLOAT(2,2) NULL DEFAULT NULL ,
  PRIMARY KEY (`contest_id`, `subject_number`, `people_number`) ,
  INDEX `cidfr_idx` (`contest_id` ASC) ,
  INDEX `snfr_idx` (`subject_number` ASC) ,
  INDEX `pnfr_idx` (`people_number` ASC) ,
  CONSTRAINT `cidfr`
    FOREIGN KEY (`contest_id` )
    REFERENCES `ruda_dev_souteze2`.`contests` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `snfr`
    FOREIGN KEY (`subject_number` )
    REFERENCES `ruda_dev_souteze2`.`subjects` (`number` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pnfr`
    FOREIGN KEY (`people_number` )
    REFERENCES `ruda_dev_souteze2`.`people` (`number` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;


-- -----------------------------------------------------
-- Table `ruda_dev_souteze2`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ruda_dev_souteze2`.`users` (
  `username` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  `fullname` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  `password` BINARY(40) NOT NULL ,
  `access` CHAR(1) CHARACTER SET 'utf8' COLLATE 'utf8_czech_ci' NOT NULL ,
  PRIMARY KEY (`username`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Are you getting an error (if so, what is it)? What table/columns do you want a second/additional foreign keys on? I'm not 70% sure I understand what you need help with. — newfurniturey, Oct 11 '12 at 01:45
Sorry, what's your question? You have several different FOREIGN KEYs defined in your SQL, are you geting an error of some kind? — Larry Lustig, Oct 11 '12 at 01:46

score 1 · Accepted Answer · answered Oct 11 '12 at 02:07

I think I understand what you're asking (if not, please clarify).

In your CREATE TABLE query for the results table, you're trying to create three individual FOREIGN KEY columns and each goes to a different table. The problem in the query are these three lines:

INDEX `cidfr_idx` (`contest_id` ASC) ,
INDEX `snfr_idx` (`subject_number` ASC) ,
INDEX `pnfr_idx` (`people_number` ASC) ,

If you change INDEX to KEY it should work fine. Also, ASC is the default - so technically you could leave it out (but leaving it in wouldn't hurt a thing.

So, after this minor update your CREATE TABLE for the results table should look like:

CREATE  TABLE IF NOT EXISTS `ruda_dev_souteze2`.`results` (
  `contest_id` BIGINT(20) NOT NULL ,
  `subject_number` INT(11) NOT NULL ,
  `people_number` INT(11) NOT NULL ,
  `number_8c2` FLOAT(10,2) NULL DEFAULT NULL ,
  `time_time` TIME NULL DEFAULT NULL ,
  `time_frag` FLOAT(2,2) NULL DEFAULT NULL ,
  PRIMARY KEY (`contest_id`, `subject_number`, `people_number`) ,
  KEY `cidfr_idx` (`contest_id`) ,
  KEY `snfr_idx` (`subject_number`) ,
  KEY `pnfr_idx` (`people_number`) ,
  CONSTRAINT `cidfr`
    FOREIGN KEY (`contest_id` )
    REFERENCES `ruda_dev_souteze2`.`contests` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `snfr`
    FOREIGN KEY (`subject_number` )
    REFERENCES `ruda_dev_souteze2`.`subjects` (`number` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pnfr`
    FOREIGN KEY (`people_number` )
    REFERENCES `ruda_dev_souteze2`.`people` (`number` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_czech_ci;

I tested the full script locally with the changes and it ran fine - please let me know if you're having any issues and I can look into it further.

I tried it. I replace my part of query by your part. And i have this error: #1005 - Can't create table 'ruda_dev_souteze2.results' (errno: 150) I am doing it by phpmyadmin. Thank you for interested! — Rudolf Dvořák, Oct 11 '12 at 07:12

score 1 · Answer 2 · answered Oct 12 '12 at 06:16

The referenced column is BIGINT:

CREATE  TABLE IF NOT EXISTS `ruda_dev_souteze2`.`subjects` (
  `contest_id` BIGINT(20) NOT NULL ,                           --- BIGINT
 ...

while the referencing is INT:

CREATE  TABLE IF NOT EXISTS `ruda_dev_souteze2`.`results` (
  `contest_id` BIGINT(20) NOT NULL ,
  `subject_number` INT(11) NOT NULL ,                          --- INT
    ...
  CONSTRAINT `snfr`
    FOREIGN KEY (`subject_number` )
    REFERENCES `ruda_dev_souteze2`.`subjects` (`number` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

They should have the same datatype.

Oh, i have more primary keys. contests(id), people(contest_id, number), subjects(contest_id, number), results(contest_id, people_number, subject_number). In contests is PK AUTOINCREMENT and BIGINT. And in others is only this col is BIGINT others are only INT. — Rudolf Dvořák, Oct 12 '12 at 07:23

More foreign key in creating MySQL

2 Answers2