I'm using phpmyadmin with SQL and had an earlier statement of the exact structure work with foreign key personID to ID in a person table, I'm not sure why this one won't work, does anyone know? Two foreign keys are allowed so I'm not sure. The engine is ENGINE=InnoDB, and just to check I've added that to the foreign key statement with no luck.
Update: I had a similar situation occur again where only the foreign key with the song table didn't work but another one structured the same did work.
--
-- Constraints for table `part_of`
--
ALTER TABLE `part_of`
ADD CONSTRAINT `part_of_ibfk2` FOREIGN KEY (`songID`) REFERENCES `song` (`ID`)
ON UPDATE CASCADE
ON DELETE CASCADE;
Here's part_of table:
--
-- Table structure for table `part_of`
--
CREATE TABLE `part_of` (
`personID` int(6) NOT NULL,
`songID` int(6) NOT NULL,
`role` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Here's song table:
--
-- Table structure for table `song` PK is ID, index leadID
--
CREATE TABLE `song` (
`ID` int(6) NOT NULL,
`title` varchar(10) DEFAULT NULL,
`run_time` varchar(10) DEFAULT NULL,
`lyrics` varchar(255) DEFAULT NULL,
`leadID` int(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Error
Error
SQL query: Copy
MySQL said: Documentation
#1005 - Can't create table `musicdb`.`part_of` (errno: 150 "Foreign key constraint is incorrectly formed")
CodePudding user response:
i think you need primary key on table song, then you can try again to make FK
Reference : https://www.w3schools.com/sql/sql_foreignkey.asp