0

I am currently working on a evolutionary algorithm which will try to find an optimum configuration of parameters for a given problem.

I'm trying to create records in a MySQL table which will store a configuration where each parameter will be represented as a table column. If a configuration has already been attempted then I would like to prevent it from being duplicated.

Is there a mechanism to do this at the database/ORM level or should I create programmatic logic to prevent duplicate entries?

K-Dawg
  • 2,045
  • 1
  • 27
  • 41
  • 2
    slap a unique key on the relevant fields in your table? – Marc B Sep 08 '15 at 19:42
  • The uniqueness will be a combination of all of the columns. I don't think a primary key will suffice. The look up must be done on all columns when determining whether or not the configuration has been attempted already. – K-Dawg Sep 08 '15 at 19:46
  • which is what the unique key does. `alter table foo add unique key (field1, field2, ..., fieldN)`. keys can have multiple fields in them, after all... – Marc B Sep 08 '15 at 19:50
  • `N` can be large. Knock yourself out. – Drew Sep 08 '15 at 19:50

1 Answers1

2

Is there a mechanism to do this at the database/ORM level

Yes, declare or define the Configuration column as Primary Key or Unique Key while you are creating your table to avoid duplication.

I mentioned configuration column cause I am not sure about your table structure but if it involves multiple column then you can make it a composite key always. Why you are worried about key lookup. if you design your table properly with proper index then you shouldn't worries about that.

Say your col1, col2, col3, col4 is part of configuration and you want them to be unique. Then make them as composite primary key saying

primary key (col1, col2, col3, col4)
Rahul
  • 71,392
  • 13
  • 57
  • 105
  • Rahul, I don't think that's right the uniqueness will be a combination of all of the columns. A primary key will not suffice as the look up must be done on all columns when determining whether or not the configuration has been attempted already. – K-Dawg Sep 08 '15 at 19:46
  • @PrimeByDesign, I mentioned `configuration` column cause I am not sure about your table structure but if it involves multiple column then you can make it a composite key always. Why you are worried about key lookup. if you design your table properly with proper index then you shouldn't worries about that. – Rahul Sep 08 '15 at 19:47
  • So what is going to prevent me from storing a duplicate configuration with a different primary key? @Rahul – K-Dawg Sep 08 '15 at 19:49
  • @PrimeByDesign, BY DBMS concept there can be only one PK per table and so that's not possible. Isn't it? – Rahul Sep 08 '15 at 19:50
  • @Rahul That is my problem. I need to ensure that a combination of all of the columns are unique in the database – K-Dawg Sep 08 '15 at 19:51
  • Does this mean that I have to do this using code logic? – K-Dawg Sep 08 '15 at 19:51
  • look what Marc said above. Stop typing here, type it on mysql. Now do it – Drew Sep 08 '15 at 19:52
  • 1
    @PrimeByDesign, you are getting confused. That's what I said that define or make all the columns involved as composite primary key and DB engine will make sure about uniqueness of them. Consider reading about primary key for more information. – Rahul Sep 08 '15 at 19:53
  • Ah Rahul! I understand now... ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`); as found here: http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql – K-Dawg Sep 08 '15 at 19:55