Avoid duplicate rows, without reference to keys or indexes?

Question

I have a MySQL table in which each row is a TV episode. It looks like this:

     showTitle  |  season   |  episode   |  episodeTitle  |  airdate  |  absoluteEpisode
 ----------------------------------------------------------------------------------------
 The X-Files          5          12          Bad Blood     1998-02-22         109
 The X-Files          5          13          Patient X     1998-03-01         110

(Where absoluteEpisode is the episode's overall number counting from episode 1.)

It is populated using a Ruby program I wrote which fetches the data from a web service. Periodically, I'd like to run the program again to fetch new episodes. The question then becomes, how do I avoid adding duplicates of the already-existing rows? None of the columns in this table are suitable for use as a primary key or unique field.

I had two ideas. The first was to create a new column, md5, with an MD5 hash of all of those values, and make that a unique column, to prevent two rows with identical data from being added. That seems like it would work, but be messy.

My second was to use this solution from StackOverflow. But I can't quite get that to work. My SQL query is

INSERT INTO `tv`.`episodes` (showTitle,episodeTitle,season,episode,date,absoluteEpisode) 
SELECT '#{show}','#{title}','#{y['airdate']}' FROM `tv`.`episodes`
WHERE NOT EXISTS (SELECT * from `tv`.`episodes` 
WHERE showTitle='#{show}' AND episodeTitle='#{title}' AND season='#{season_string}' AND episode='#{y['seasonnum']}' AND date='#{y['airdate']}' AND absoluteEpisode='#{y['epnum']}'")

The #{...} bits are Ruby variables. This gets me the obvious error You have an error in your SQL syntax.

Flipping through the books and documentation I can find on the subject, I'm still not sure how to properly execute this query, or if it's not a smart way of solving my problem. I'd appreciate any advice!

Instead of using an `md5` of all relevant values, you can also add a single unique index on a combination of all those fields. — GolezTrol, Mar 07 '14 at 22:33
Maybe [how to create a composite key in MySQL database](http://stackoverflow.com/q/15302011/205233) could help. — Filburt, Mar 07 '14 at 22:34
`(showTitle, season, episode)` looks like it could be used as primary or unique key. `(showTitle, absoluteEpisode)` as well. — ypercubeᵀᴹ, Mar 23 '14 at 18:12

score 0 · Answer 1 · answered Apr 25 '14 at 18:45

why not create a primary key from the showTitle, season, and episode, this will solve the problem because I guess because the episode number can not be duplicate under the same season, and that's apply for the same TV show, example

x-files==>season 1==>episode 1 this will be primary key as one unit

Avoid duplicate rows, without reference to keys or indexes?

1 Answers1