mysql assign auto number to non-primary key column

Question

i have a table with columns

1. cid (customer id)
2. cemail (customer email)
3. cfax (customer email)
4. cname (customer name)

now i want that there should be no duplication in cid,cemail,cfax as i do that i make these all as primary keys but if only one value is different database accept data i want that it should check all values what should i do now ?

Answer 1

You should create a unique index across those fields.

Just as a simple example (not tested):

CREATE UNIQUE INDEX my_index ON my_table(cemail, cfax, cname);

Answer 2

CREATE TABLE `customers` (
  `cid` int NOT NULL auto_increment,
  `cemail` varchar(123) NOT NULL,
  `cfax` varchar(123) NOT NULL,
  `cname` varchar(123) NOT NULL,
  PRIMARY KEY  (`cid`),
  UNIQUE KEY `cemail` (`cemail`,`cfax`,`cname`)
);