The most appropriate way to check if the value already existed

Question

Let me explain the scenario first.

I have a customer and phone relation database. A customer can have multiple phone numbers as long as the phone numbers are unique.

For example,

• Customer Bryan added '111' number Inserted.
• Customer Bryan added '222' number Inserted.
• Customer Bryan added '111' number Not Accepted.

Normally, I have to execute couple queries to achieve this

First check if the number trying to add has already existed in the database. query = "SELECT COUNT(NAME) where name = 'Bryan' and number '111'";

If the result is 0, then proceed with INSERT execution.

I don't think this is the right way though.

I have tried using the following query but it didn't work. Its saying invalid syntax.

IF NOT EXISTS (
SELECT telephone
FROM phone_numbers
WHERE telephone = '123456' 
AND customer_id = 20)
BEGIN
INSERT INTO phone_numbers (telephone, customer_id) VALUES
("123456", 20)
END;

I'm writting this in C# could anyone please enlighten me the most appropriate way to do this?

Answer 1

Not an answer to your specific question, but advice as to how better define your schema such that the problem you face isn't allowed to happen.

So if you specify the following unique constraint:

ALTER TABLE `phone_numbers` 
  ADD UNIQUE `unique_index`(`customer_id`, `telephone`);

Your database won't allow you to mess this up in the first place.