How to make a pair of columns unique in mysql

Question

+----+---------------+-------+---------------+
| id |     name      | phone |    ip_addr    |
+----+---------------+-------+---------------+
|  1 | customname001 |  1234 | 192.168.100.1 |
|  2 | customname002 |  2156 | 192.168.100.2 |
|  3 | customname003 |  9685 | 192.168.100.3 |
|  4 | customname004 |  1546 | 192.168.100.1 |
|  5 | customname005 |  1234 | 192.168.100.1 |
+----+---------------+-------+---------------+

In my codeigniter project I am trying to build a model which will help me insert a new row into a table which looks something like the above one. The condition I want to apply here is to check if the combination of name, phone and ip_addr is unique or not.

I know that for a single column if we set the value to be unique then the values can't be repeated but my example is a different case where I want the combination of all the columns(except the id) to be unique.

Can anyone help me here in building the model which will help me to add new pair of name, phone and ip_addr and check if the pair already exists and accordingly give a feedback.

Why not check if those values are already exists? – Sougata Bose Mar 16 '16 at 12:53 — Sougata Bose, Mar 16 '16 at 12:53

M. Suurland · Accepted Answer · 2016-03-16T12:59:49.840

4

ALTER TABLE `table` ADD UNIQUE `unique_index`(`name`, `phone`, `ip_addr`);

edited Mar 16 '16 at 12:59

answered Mar 16 '16 at 12:58

M. Suurland

725
12
31

score 2 · Answer 2 · answered Mar 16 '16 at 13:10

2

If you have view in which you user is going to enter name, phone and ip_address you can set form validation rule which easily prevent the record duplication!!

So,set the rule is_unique for each field is_unique[tbl_name.column_name]

For Ex:

$this->form_validation->set_rules('name', 'User Name', 'required|trim|xss_clean|is_unique[users.name]);

NOTE: This is only useful if you are supposed to enter the value from the view otherwise you have to check using SQl Query

answered Mar 16 '16 at 13:10

Kunal

604
10
18

But that will set only one column to be unique. But what I am looking for is how can I set a combination of columns to be unique. I think, it is not possible and probably I have to write the sql query. – Kiran Dash Mar 17 '16 at 06:22
No,you can set validation for any number of column!!For that,You are going to set validation rules for each field having required or something else ...So Do the same for that field also.@KiranKumarDash – Kunal Mar 17 '16 at 07:15

score 1 · Answer 3 · edited Mar 16 '16 at 20:03

1

insert into table (name, phone, ip_addr)
select new.name, new.phone, new.ip_addr
from table
where (new.name,new.phone,new.ip_addr) not in (
    select name,phone,ip_addr from table
);

edited Mar 16 '16 at 20:03

Vasfed

18,013
10
47
53

answered Mar 16 '16 at 13:00

Priyanshu

885
6
12

How to make a pair of columns unique in mysql

3 Answers3