SQL Code for INSERT while checking of multiple keys for duplicates

Question

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?

possible duplicate of MySql Unique Constraint

OMG Ponies
– OMG Ponies

2012-03-19 03:59:05 +00:00
Commented Mar 19, 2012 at 3:59 — OMG Ponies
– OMG Ponies, Commented Mar 19, 2012 at 3:59

Mosty Mostacho · Accepted Answer · 2012-03-19 04:00:38Z

1

Try:

alter table customer_list add unique index(first_name, last_name, phone);

answered Mar 19, 2012 at 4:00

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vidarious Over a year ago

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.

Collectives™ on Stack Overflow

SQL Code for INSERT while checking of multiple keys for duplicates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related