Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1

CREATE TABLE contacts 
    (
     id int auto_increment primary key, 
     name varchar(20), 
     network_id int,
     network_contact_id int
    );

INSERT INTO contacts
(name, network_id, network_contact_id)
VALUES
('John', 4, 10),
('Alex', 4, 11),
('Bob', 4, 12),
('Jeff', 4, 45),
('Bill', 7, 11),
('Walter', 7, 45),
('Jessie', 7, 360) ;

I have a basic table of contacts. The network_id and network_contact_id fields contain id numbers that link to other tables.

I want to be able to run INSERT IGNORE queries to this table, but I want to use the combination of the network_id and network_contact_id as the unique key to match against.

So for example, if I tried to insert a contact that had network_id = 4 and network_contact_id = 12, the INSERT IGNORE query would see that entry already exists, and ignore any error that was thrown.

So basically, network_id is not unique. network_contact_id is not unique. But the combination of the two is unique. How do I set this up? Would I have to have a single other field that is the concatenated values of the two other fields? Or is there a way to setup the keys for this table so it will do what I need?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

Did you try

CREATE TABLE contacts (
 id int auto_increment primary key, 
 name varchar(20), 
 network_id int,
 network_contact_id int, 
 UNIQUE KEY (`network_id`, `network_contact_id`)
);
share|improve this answer
1  
OMG Both your answer and @ypercube's have identical timestamps. +1 for both of you. –  RolandoMySQLDBA May 11 '12 at 21:36
    
Is there any reason to keep the id field as primary key if this unique key thing is now set? –  Jakobud May 11 '12 at 21:46
    
Here's one related question on the matter of keeping the id field. –  Derek Downey May 11 '12 at 21:58
    
@Rolando: Actually DTest was faster by 1 second :) –  ypercube May 11 '12 at 22:01
add comment

Change the table's definition by adding a UNIQUE KEY constraint on the combination of the two columns:

CREATE TABLE contacts 
    (
     id int auto_increment primary key, 
     name varchar(20), 
     network_id int,
     network_contact_id int,
     CONSTRAINT network_id_contact_id_UNIQUE
       UNIQUE KEY (network_id, network_contact_id)
    );

You should also check this answer by Bill Karwin on the differences between INSERT IGNORE, REPLACE and INSERT ... ON DUPLICATE KEY UPDATE

share|improve this answer
    
Why does your definition have the constraint in there when DTest's doesn't? What is the difference? –  Jakobud May 11 '12 at 21:44
2  
This is only providing a name for the constraint. If I remember well, with DTest answer, the constraint will be auto-named by MySQL. –  ypercube May 11 '12 at 22:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.