How to prevent SQL insert when certain row already exists?

Question

I have a database with different movies, actors, directors.

To connect a director with a movie I'm using a table in which I have the following:

film_id and director_id - so when I add a director for the movie the necessary id's get inserted to this table.

So I have a bunch of id's already there and it works just fine. For example:

movie_id - director_id
10         15
11         17 
9          13

Now when I add another director with let's say an id 9 to a movie with an id 13 then I have two rows that are exactly the same and that's what I don't want to happen. Shown below:

movie_id - director_id
10         15
11         17 
9          13
9          13

Instead it should not insert when there already is a row with the same film_id equal to the same director_id.

How can I prevent this from happening?

use select query with where clause if it returns any row data is duplicate otherwise insert the data — JYoThI, Sep 07 '17 at 09:45
Simply make on of these two columns unique. The data will automatically be prevented from being inserted into the database. — Prakhar Sood, Sep 07 '17 at 09:46

Neodan · Accepted Answer · 2017-09-07T09:58:49.520

Just create the unique key from movie_id and director_id columns.

SQL example:

ALTER TABLE `table_name` ADD UNIQUE `index_name` (`movie_id`, `director_id`);

And then add IGNORE for INSERT query, it will prevent from the Duplicate entry... error message.

SQL example:

INSERT IGNORE INTO `table_name` (...) VALUES (...)

If you want to update a some data if the entry already exists, then you can use ON DUPLICATE KEY UPDATE.

SQL example:

INSERT INTO `table_name` (...) VALUES (...) ON DUPLICATE KEY UPDATE `column` = [new_value]

How to prevent SQL insert when certain row already exists?

1 Answers1