0

Can't really think of a relevant title :-(

I have made a program in C# that registers the sales of a product. Inside my database theres two tables. One called 'Product' and another table called 'Customer'. 'Product' contains the CustomerId (so I can join the two tables), and the customer table contains the data that is relevant to the customer.

Theres a point where the user of my program has to enter the product details and the customer details. But how do I know if a customer already exists in my database' customer table? Would I have to read out all the records that are in the customer table already and compare them to the new customer details? It seems kind of devious to me.

(Need to know whether the data has already been filled in to prevent duplicates)

user1534664
  • 3,258
  • 8
  • 40
  • 66

2 Answers2

2

Put a constraint on the table that doesn't allow duplicates, and then just try to insert the data. If it's already there, the server won't allow it to be inserted and will let you know.

I can't explain how it will let you know, because you provided no information about the database you're using. It's typically an exception that's thrown.

Ken White
  • 123,280
  • 14
  • 225
  • 444
  • Yes you can define a column (say Username) as Unique. sql won't allow you to insert if a duplicate exists. – ajon Oct 05 '12 at 23:19
  • 1
    You mean you use `MySQL` through `phpAdmin`? Put the constraint on the column for your customer and then try to do an `INSERT` of a duplicate, and see what happens in your code. – Ken White Oct 05 '12 at 23:20
  • So how would I put a constraint on it using phpmyadmin? – user1534664 Oct 05 '12 at 23:24
  • You do it by altering the table and adding the constraint. See the [MySQL documentation](http://dev.mysql.com/doc/refman/5.5/en/alter-table.html). I can't tell you how to do it in phpMyAdmin, because I don't use it. (That would be a separate question - "How do I add a unique constraint using PHPMyAdmin?" might be a good title.) Or you could just search here and find [Adding constraints in phpMyAdmin](http://stackoverflow.com/a/9499779/62576). – Ken White Oct 05 '12 at 23:30
-1

You could just issue a suitable select statement with an appropriate where clause to check to see if there is a row that already matches the data.

If your customer ID is not generated by the database itself (i.e. it's something that you would enter when you were entering the customer details into the database then you can issue a select statement like:

SELECT * FROM Customer WHERE CustomerId = <Customer's ID>

But if the customer ID is generated by the database, you'll want to change the where clause to work across the fields that you want to ensure are not duplicated.

Richard Comish
  • 197
  • 2
  • 20
  • A constraint on the table would be a much better idea than doing a SELECT before every INSERT. – Ken White Oct 05 '12 at 23:21
  • The constraint is more than a better idea it is needed doing a select then an insert will not work if you have more than one connection to the database as the two connections could both insert – mmmmmm Oct 05 '12 at 23:23