MySQL Multiple Column Unique Key

Question

I'm familiar with the syntax of create unique index 'blah' on 'some_table' ('col1','col2') however I'm unsure exactly how this will behave. More specifically, the documentation says

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.

That seems like it might be okay in some cases, but I want make a table that holds just numbers unique. So, I could see col1 being 12 and col2 being 2, which will concatenate to 122 from what I can tell, and then a value of col1 1 and col2 22 would match that, which is not what I want. Is there a way to define a separator for the concatenation or is there already one in place? Should I be doing this differently?

Here's an attempt at a diagram

table 1             middle               table 2
-------             ------               -------
t1_id               mid_id               t2_id
some_value          t1_id                another_value
                    t2_id

I want the t1_id and t2_id combination to be unique, but t1 can repeat and t2 can repeat.

Have you looked at INFORMATION_SCHEMA to see what your index looks like? — Brian Driscoll
– Brian Driscoll, Commented Feb 21, 2011 at 18:46
I haven't actually implemented the above design because I'm not sure that it would work. I could give it a try real quick. — Robert
– Robert, Commented Feb 21, 2011 at 18:49

Mark Byers · Accepted Answer · 2011-02-21 18:47:15Z

3

It is not possible nor necessary to specify a separator. The index works as you want it to.

answered Feb 21, 2011 at 18:47

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

MySQL Multiple Column Unique Key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related