1

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Adrian Roy Baguio
  • 104
  • 10
  • possible duplicate of [How do I update if exists, insert if not (AKA "upsert" or "merge") in MySQL?](http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) – NeddySpaghetti Jan 18 '15 at 02:06

1 Answers1

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.

spender
  • 117,338
  • 33
  • 229
  • 351
  • . . This is an answer. It is the right way to enforce a unique constraint on a table. – Gordon Linoff Jan 18 '15 at 02:14
  • @GordonLinoff The number of times I've been downvoted for ignoring the Y in an XY problem means that I now qualify such answers with a little disclaimer! – spender Jan 18 '15 at 02:20