Home > Software design >  Foreign key constraint not working in alter table statement SQL and phpmyadmin
Foreign key constraint not working in alter table statement SQL and phpmyadmin

Time:04-10

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

  • Related