Best way not to reenter same record twice

[justride]

Hi,

Not sure if I shoudl ask this here or in a mysql forum.

Is there an easier way to see if a customer record has already been entered or not. Right now I check names and phone numbers but it is very inneficient. Is there a way to do it on the fly with the insert statement. Like scan all records to see if any fields match the fields I want to insert with?

Thanks

 

[Chacalinc]

the column must be created as "UNIQUE" in the database. Take a look to the docs pages in

http://www.mysql.com

http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

 

[justride]

well i have the phone number in 3 diff fields
area code prefix suffix
so they really cant be unique

 

[Bastien]

create a unique index on the customername field, best to check anyways, no reason customers can't have the same name.

Bastien

Cat, the other other white meat

 

[ericbrunson]

You can create a multi field index and specify that it needs to be unique (at least in oracle you can).

 

[justride]

Im not sure mysql has the multi field feature.

i have 3 fields to store a phone number. PHONE1 PHONE2 PHONE3
I figure thats the best wat to see if a record has been entered since phone numbers are unique. Is there way to do a search on all records combining thsoe fields in the query.

like: select * where PHONE1 JOIN PHONE2 JOIN PHONE3 = "##########"

soemthing like that?

and if the query returns 0 I know the record is new.

 

[shadedecho]

mysql DOES have the "multi-fields index feature", called "multi-column index".


FYI, the MySQL manual talks about this...

http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html

here's an excerpt:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type]
ON tbl_name (index_col_name,...)

index_col_name:
col_name [(length)] [ASC | DESC]

...

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.