[ Aleksandar Ružičić @ 04.08.2009. 16:03 ] @
uzeh danas MySQL Workbench da se malo igram u njemu (olaksava dizajn baze dosta, moram priznati :D), uradim export u sql i pokusam da izvrsim generisani script ali dobijem error 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; > 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. |