SQL conditional row insert

Question

Is it possible to insert a new row if a condition is meet?

For example, i have this table with no primary key nor uniqueness

    +----------+--------+
    | image_id | tag_id |
    +----------+--------+
    |    39    |    8   |
    |    8     |    39  |
    |    5     |    11  |
    +----------+--------+

I would like to insert a row if a combination of image_id and tag_id doesn't exists for example;

INSERT ..... WHERE image_id!=39 AND tag_id!=8

Answer 1

Try using a database trigger for an efficient way to add rows to a different table based on updates to a table.

Answer 2

I'm assuming you mean you want to insert a row if it does not contain those values.

I don't have MySQL in front of me, but in general SQL this should work:

INSERT INTO a_table (image_id, tag_id) SELECT ? image_id, ? tag_id WHERE  image_id!=39 AND tag_id!=8

If you mean to insert the row only if no such row exists at all, then you can do this:

INSERT INTO a_table (image_id, tag_id) SELECT ? image_id, ? tag_id WHERE not exists (SELECT 1 from a_table WHERE image_id!=39 AND tag_id!=8)

Answer 3

If you're using InnoDB with transcations, then you can simply query for the data first, and in your code subsequently execute the insert if now rows were already found with the values. Alternatively, add a unique constaint over both columns, and try the insert. If will fail, if it already exists, which you can ignore. (This is less preferred than my first approach.)

Answer 4

I think you're saying: you need to avoid duplicate rows in this table.

There are many ways of handling this. One of the simplest:

INSERT INTO theTable (image_id, tag_id) VALUES (39, 8)
WHERE NOT EXISTS 
    (SELECT * FROM theTable 
    WHERE image_id = 39 AND tag_id = 8)

As @Henrik Opel pointed out, you can use a check constraint on the combined columns, but then you have to have a try/catch block somewhere else, which adds irrelevant complexity.

Edit to explain that comment...

I'm assuming this is a table mapping a many-to-many relationship between Movies and Tags. I realize you're probably using php, but I hope the C# pseudocode below is clear enough anyway.

If I have a Movie class, the most natural way to add a tag is an AddTag() method:

class Movie 
{
    public void AddTag(string tagname)
    {
        Tag mytag = new Tag(tagname); // creates new tag if needed

        JoinMovieToTag(this.id, mytag.id);
    }

    private void JoinMovieToTag(movie_id, tag_id)
    {
        /* database code to insert record into MovieTags goes here */

        /* db connection happens here */
        SqlCommand sqlCmd = new SqlCommand("INSERT INTO theTable... /* etc */");

        /* if you have a check constraint on Movie/Tag, this will 
           throw an exception if the row already exists */
        cmd.ExecuteNonQuery(); 
    }
}

There's no practical way to check for duplicates earlier in the process, because another user might Tag the Movie at any moment, so there's no way around this.

Note: If trying to insert a dupe record means there's a bug, then throwing an error is appropriate, but if not, you don't want extra complexity in your error handler.

Answer 5

Is fare to give credit to Henrik Opel as he spotted what we all overlooked including me, a simple unique constraint on the two columns. Is ultimately the best solution.