-1

I have a table like this:

something
a [INT]
b [INT]
c [INT]

...where a, b and c are separate Foreign Keys pointing to three different table.id. Since I want to make all regs be unique, and after having read this great answer, I think I should create a new Index this way: UNIQUE INDEX(a, b, c) and (in my case) do IGNORE INSERTS.

But as you can see, I would have one KEY for each column and then another extra UNIQUE INDEX containing all three. Is this a normal thing? It seems strange to me, and I have never seen it.

Community
  • 1
  • 1
Mark Tower
  • 559
  • 1
  • 5
  • 15

2 Answers2

1

It is perfectly normal and reasonable to include a column in more than one index. However, if the combination of (a, b, c) is enough to uniquely identify a row it seems that you want a PRIMARY index instead of a UNIQUE one here (technically there is very little difference, but semantically it might be the better choice).

Jon
  • 428,835
  • 81
  • 738
  • 806
  • Thank you. Here is a great answer too for those who don't know the differences between PRIMARY and UNIQUE (http://stackoverflow.com/a/708508). Going to accept your answer in some minutes ;D – Mark Tower Jan 29 '13 at 11:19
-1

Creating a Primary Key if Something (a, b, c) will invalidate the need for a unique index. An additional Unique index would make sense if your primary key was Something(a, b) and you wanted a Unique Index (a, b, c). But since all three columns are Foreignkey then a Primary key index is what you.

Sean McCully
  • 1,122
  • 3
  • 12
  • 21