CODEWITHSUNDEEP
mysqlsql
GTucker
GTucker

Reputation: 162

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 :)

Upvotes: 1

Views: 56

Answers (2)

abdessamad el ouardi
abdessamad el ouardi

Reputation: 61

The type of PK_USER_ID is INT, where the FK_ISSUE_PROPOSED_BY type is VARCHAR

Upvotes: 1

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

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

Upvotes: 3

Related Questions