0

I have the following table:

CREATE TABLE IF NOT EXISTS `customer_list` (
   `id` INT AUTO_INCREMENT,
   `first_name` char(4) NOT NULL,
   `last_name` varchar(80) NOT NULL,
   `phone` varchar(50) NOT NULL,
   `province` varchar(50) NOT NULL,
   `country` varchar(30) NOT NULL,
   `start_date` TIMESTAMP NOT NULL,
   `end_date` TIMESTAMP NOT NULL,
   PRIMARY KEY (id)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I want to be able to insert into this table with the only restriction being that first_name, last_name and phone CANNOT be the same. If they are the same I want some sort of error returned to warn the end user that the record already exists - No insert/update/replace action is performed.

The key here is the INSERT statement must somehow check 3 fields for duplication. The error must only return if ALL 3 fields are duplicates. IE. 1 or 2 out of the 3 are allowed to be duplicates and still be entered.

Is this possible with one INSERT statement?

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
Vidarious
  • 746
  • 2
  • 10
  • 22
  • possible duplicate of [MySql Unique Constraint](http://stackoverflow.com/questions/6679122/mysql-unique-constraint) – OMG Ponies Mar 19 '12 at 03:59

1 Answers1

1

Try:

alter table customer_list add unique index(first_name, last_name, phone);
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • Thats perfect! Thank you. Unique Indexing is new to me but I fully understand it. There is no checking I need to do in the INSERT. By making those columns unique the general INSERT statement does it all and I just catch the errors. – Vidarious Mar 19 '12 at 22:45