1

I need your help, I'm a little bit in trouble.

I have a "structured" info that has to be unique taken all field together, but can be not unique individually.

For example:

Columns: Two_Letters | Firts_Digit | Second_Digit | Third_Digit | Four_Letters

I want that the entry AA|2|3|4|BBBB is unique, but I can insert AA|2|3|4|CCCC.

Obviously letters can vary from A to Z and digit from 0 to 9.

Suggestion?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
z10h22
  • 51
  • 1
  • 9
  • 4
    Don't store this values in DB as concatenated strings, but split it to separate columns. In this case you will be able to create unique key on these columns. – Andrey Korneyev Jun 28 '16 at 14:33
  • 2
    Add a unique constraint, `unique(Two_Letters, Firts_Digit, Second_Digit, Third_Digit, Four_Letters)`, to your table. – jarlh Jun 28 '16 at 14:33
  • @AndyKorneyev they are not concatenated strings, and that is the problem. I have a code string structured that i choose to split in columns (that I've called "Field" in first post) To JARLH: I've have unique-ified them via query and via mysql workbench gui, and if I insert (AA,1,2,3,AAAA) and (AA,1,3,4,BBBB) I have back error for "duplicate entry" on first column – z10h22 Jun 28 '16 at 15:07

1 Answers1

0

THINK SOLVED: Since I've done all edits via MySQL Workbench, I do not consider differences between "checkbox UNIQUE" associated to each column and "unique constraint" that is really what I was looking for. So the solution for my problem is: -ALTER TABLE table_name ADD CONSTRAINT UNIQUE (Two_Letters, Firts_Digit, Second_Digit, Third_Digit, Four_Letters); associated with no checkbox checked for 'UQ' in gui editing table on MySQL WB.

z10h22
  • 51
  • 1
  • 9