0

I am getting the below error on trying to do and insert

'Cannot add or update a child row: a foreign key constraint fails (tvlistings.channelprogramme, CONSTRAINT channelprogramme_ibfk_1 FOREIGN KEY (CHANNELID) REFERENCES channels (CHANNELID))')

A quick google led me to this, but it's a little too specific too the code/query

Cannot add or update a child row: a foreign key constraint fails

The insert query I have is

  sqlString = """INSERT INTO CHANNELPROGRAMME (
                     CHANNELID, PROGRAMMEID, EPISODEID,
                     RPEAT, NEWSERIES, PREMIERE, CHOICE, SUBTITLES,
                     DEAFSIGNED, STARRATING, PROGDATE, STARTTIME, ENDTIME,
                     DURATION)
                 SELECT
                     T.CHANNELID, P.PROGRAMMEID, E.EPISODEID, T.RPEAT, T.NEWSERIES,
                     T.PREMIERE, T.CHOICE, T.SUBTITLES, T.DEAFSIGNED, T.STARRATING,
                     T.PROGDATE, T.STARTTIME, T.ENDTIME, T.DURATION
                 FROM
                     TVTEMPTABLE T
                     INNER JOIN PROGRAMME P ON P.PROGTITLE=T.PROGTITLE
                     INNER JOIN EPISODE E ON E.SUBTITLE=T.SUBTITLE AND E.EPISODE=T.EPISODE"""
  self._cursor.execute(sqlString)

I am using Python 2.7 and MySQLdb to execute this. I believe the message is saying I am trying to insert a channelid into the channelprogramme table that does not exist the channels table. Unfortunately I am none the wiser on how to fix this?

Here are how the table are defined

CREATE TABLE IF NOT EXISTS CHANNELPROGRAMME (
                                      CHANNELPROGRAMMEID INT NOT NULL AUTO_INCREMENT, CHANNELID INT NOT NULL,
                                      PROGRAMMEID INT NOT NULL, EPISODEID INT NOT NULL, RPEAT BOOL, NEWSERIES BOOL,
                                      PREMIERE BOOL, CHOICE BOOL, SUBTITLES BOOL, DEAFSIGNED BOOL, STARRATING TINYINT,
                                      PROGDATE DATE, STARTTIME TIME, ENDTIME TIME, DURATION INT, PRIMARY KEY(CHANNELPROGRAMMEID),
                                      INDEX (CHANNELID), FOREIGN KEY (CHANNELID) REFERENCES CHANNELS(CHANNELID), INDEX (PROGRAMMEID),
                                      FOREIGN KEY (PROGRAMMEID) REFERENCES PROGRAMME(PROGRAMMEID), INDEX (EPISODEID),
                                      FOREIGN KEY (EPISODEID) REFERENCES EPISODE(EPISODEID)
                                  ) ENGINE=INNODB

CREATE TABLE IF NOT EXISTS CHANNELS (
                                      CHANNELID INT NOT NULL, CHANNELNAME VARCHAR(100), INDEX(CHANNELNAME), PRIMARY KEY(CHANNELID)
                                  ) ENGINE=INNODB
Community
  • 1
  • 1
PDStat
  • 5,513
  • 10
  • 51
  • 86
  • 1
    Your interpretation is correct. The fix is to make sure that the `CHANNELID` exists in the `CHANNELS` table before you insert it into the `CHANNELPROGRAMME` table. How you do that is entirely down to your application and beyond the scope of this question. –  Apr 02 '14 at 20:02
  • 1
    I think your problem lies in how TVTEMPTABLE is populated, not the code you posted; a CHANNELID exists in that table that does not exist in CHANNELS. – Paul Abbott Apr 02 '14 at 20:05
  • Both of these comments were useful actually @MikeW your suggestion led me to add another inner join between the temptable channelid and channels channelid. paul your comment has led me to question why temptable would have a channelid that channels doesn't so thanks for that, now to figure out why! – PDStat Apr 02 '14 at 21:16

0 Answers0