InnoDB i foreign kljucevi (can't create table)SQL:
Code:
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 TABLE IF EXISTS `Player` ;
CREATE TABLE IF NOT EXISTS `Player` (
`id` INT NOT NULL AUTO_INCREMENT ,
`tag_id` INT UNSIGNED NOT NULL ,
`name` VARCHAR(80) NOT NULL ,
`birthday` DATETIME NULL ,
`birthplace` VARCHAR(100) NULL ,
`country` VARCHAR(50) NULL ,
`metadata` TEXT NULL ,
PRIMARY KEY (`id`) ,
INDEX `name` (`name` ASC) ,
INDEX `tag_id` (`tag_id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'Player entity';
DROP TABLE IF EXISTS `Team` ;
CREATE TABLE IF NOT EXISTS `Team` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`tag_id` INT UNSIGNED NOT NULL ,
`name` VARCHAR(80) NOT NULL ,
`desc` TEXT NULL ,
`flag` VARCHAR(200) NULL ,
`country` VARCHAR(50) NULL ,
`status` SMALLINT NOT NULL DEFAULT 1 ,
`metadata` TEXT NULL ,
PRIMARY KEY (`id`) ,
INDEX `name` (`name` ASC) ,
INDEX `tag_id` (`tag_id` ASC) )
ENGINE = InnoDB
COMMENT = 'Team entity';
DROP TABLE IF EXISTS `Transfer` ;
CREATE TABLE IF NOT EXISTS `Transfer` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`player_id` INT UNSIGNED NOT NULL ,
`team_from` INT UNSIGNED NOT NULL ,
`team_to` INT UNSIGNED NOT NULL ,
`date` DATETIME NOT NULL ,
`price` INT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_Transfer_TeamFrom` (`team_from` ASC) ,
INDEX `fk_Transfer_TeamTo` (`team_to` ASC) ,
INDEX `fk_Transfer_Player` (`player_id` ASC) ,
CONSTRAINT `fk_Transfer_Player`
FOREIGN KEY (`player_id`)
REFERENCES `Player` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Transfer_TeamFrom`
FOREIGN KEY (`team_from` )
REFERENCES `Team` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Transfer_TeamTo`
FOREIGN KEY (`team_to` )
REFERENCES `Team` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Player transfer details';
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
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 TABLE IF EXISTS `Player` ;
CREATE TABLE IF NOT EXISTS `Player` (
`id` INT NOT NULL AUTO_INCREMENT ,
`tag_id` INT UNSIGNED NOT NULL ,
`name` VARCHAR(80) NOT NULL ,
`birthday` DATETIME NULL ,
`birthplace` VARCHAR(100) NULL ,
`country` VARCHAR(50) NULL ,
`metadata` TEXT NULL ,
PRIMARY KEY (`id`) ,
INDEX `name` (`name` ASC) ,
INDEX `tag_id` (`tag_id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'Player entity';
DROP TABLE IF EXISTS `Team` ;
CREATE TABLE IF NOT EXISTS `Team` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`tag_id` INT UNSIGNED NOT NULL ,
`name` VARCHAR(80) NOT NULL ,
`desc` TEXT NULL ,
`flag` VARCHAR(200) NULL ,
`country` VARCHAR(50) NULL ,
`status` SMALLINT NOT NULL DEFAULT 1 ,
`metadata` TEXT NULL ,
PRIMARY KEY (`id`) ,
INDEX `name` (`name` ASC) ,
INDEX `tag_id` (`tag_id` ASC) )
ENGINE = InnoDB
COMMENT = 'Team entity';
DROP TABLE IF EXISTS `Transfer` ;
CREATE TABLE IF NOT EXISTS `Transfer` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`player_id` INT UNSIGNED NOT NULL ,
`team_from` INT UNSIGNED NOT NULL ,
`team_to` INT UNSIGNED NOT NULL ,
`date` DATETIME NOT NULL ,
`price` INT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_Transfer_TeamFrom` (`team_from` ASC) ,
INDEX `fk_Transfer_TeamTo` (`team_to` ASC) ,
INDEX `fk_Transfer_Player` (`player_id` ASC) ,
CONSTRAINT `fk_Transfer_Player`
FOREIGN KEY (`player_id`)
REFERENCES `Player` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Transfer_TeamFrom`
FOREIGN KEY (`team_from` )
REFERENCES `Team` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Transfer_TeamTo`
FOREIGN KEY (`team_to` )
REFERENCES `Team` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Player transfer details';
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
> mysql test < test.sql
ERROR 1005 (HY000) at line 41: Can't create table './test/Transfer.frm' (errno: 150)
a zatim u mysql konzoli:
mysql> show engine innodb status;
i izmedju ostalog dobijem:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
090804 16:59:23 Error in foreign key constraint of table test/Transfer:
FOREIGN KEY (`player_id`)
REFERENCES `Player` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Transfer_TeamFrom`
FOREIGN KEY (`team_from` )
REFERENCES `Team` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Transfer_TeamTo`
FOREIGN KEY (`team_to` )
REFERENCES `Team` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Player transfer details':
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refma...b-foreign-key-constraints.html
for correct foreign key definition.
i nije mi jasno kako se buni da Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint kad Player.id ima PRIMARY KEY?
da li moze neko da mi objasni zasto se ovo desava i sta to radim pogresno?
btw, ja sam samo sklonio CONSTRAINT na Player tabelu i upit je prosao bez problema, ali me interesuje zasto sam morao da sklonim fk?
hvala unapred.
