How to prevent SQL insert when certain row already exists? [duplicate]

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
– JYoThI, Commented Sep 7, 2017 at 9:45
Simply make on of these two columns unique. The data will automatically be prevented from being inserted into the database. — Prakhar Sood
– Prakhar Sood, Commented Sep 7, 2017 at 9:46

Neodan · Accepted Answer · 2017-09-07 09:58:49Z

3

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]

edited Sep 7, 2017 at 9:58

answered Sep 7, 2017 at 9:45

Neodan

5,2722 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to prevent SQL insert when certain row already exists? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related