GTucker GTucker - 3 years ago 96
SQL Question

SQL Foreign Key command won't execute

when trying to execute a SQL statement that will assign foreign keys to my database I receive an error, am I missing something simple?

CREATE TABLE `Organisation` (
`PK_ORG_ID` INT NOT NULL AUTO_INCREMENT,
`ORG_NAME` VARCHAR(128) NOT NULL,
PRIMARY KEY (`PK_ORG_ID`)
);

CREATE TABLE `User` (
`PK_USER_ID` INT NOT NULL AUTO_INCREMENT,
`USER_NAME` VARCHAR(128) NOT NULL,
`FK_USER_ORG` INT NOT NULL,
PRIMARY KEY (`PK_USER_ID`)
);

CREATE TABLE `Issue` (
`PK_ISSUE_ID` INT NOT NULL AUTO_INCREMENT,
`ISSUE_TEXT` VARCHAR(128) NOT NULL,
`FK_ISSUE_PROPOSED_BY` VARCHAR(255) NOT NULL,
`ISSUE_UPSCORE` INT NOT NULL,
`ISSUE_TYPE` INT NOT NULL,
`ISSUE_TITLE` VARCHAR(255) NOT NULL,
`FK_ISSUE_CANDIDATE` VARCHAR(255),
PRIMARY KEY (`PK_ISSUE_ID`)
);

CREATE TABLE `Vote Card` (
`PK_VOTECARD_ID` INT NOT NULL AUTO_INCREMENT,
`FK_VOTECARD_USERID` INT NOT NULL,
`FK_ISSUE_ID` INT NOT NULL,
`VOTESCORE` INT NOT NULL,
PRIMARY KEY (`PK_VOTECARD_ID`)
);

ALTER TABLE `User` ADD CONSTRAINT `User_fk0` FOREIGN KEY (`FK_USER_ORG`) REFERENCES `Organisation`(`PK_ORG_ID`);

ALTER TABLE `Issue` ADD CONSTRAINT `Issue_fk0` FOREIGN KEY (`FK_ISSUE_PROPOSED_BY`) REFERENCES `User`(`PK_USER_ID`);

ALTER TABLE `Issue` ADD CONSTRAINT `Issue_fk1` FOREIGN KEY (`FK_ISSUE_CANDIDATE`) REFERENCES `User`(`PK_USER_ID`);

ALTER TABLE `Vote Card` ADD CONSTRAINT `Vote Card_fk0` FOREIGN KEY (`FK_VOTECARD_USERID`) REFERENCES `User`(`PK_USER_ID`);

ALTER TABLE `Vote Card` ADD CONSTRAINT `Vote Card_fk1` FOREIGN KEY (`FK_ISSUE_ID`) REFERENCES `Issue`(`PK_ISSUE_ID`);


And the error message that is produced by MySQL is below

SQL query:


ALTER TABLE `Issue` ADD CONSTRAINT `Issue_fk0` FOREIGN KEY (`FK_ISSUE_PROPOSED_BY`) REFERENCES `User`(`PK_USER_ID`)
MySQL said: Documentation

1005 - Can't create table `youdecide`.`#sql-142a_de` (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)


Thank you for your help :)

Answer Source

PK_USER_ID is INT but FK_ISSUE_PROPOSED_BY is VARCHAR , foreign key must match with the primary key data type

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download