UNIQUE keyword and duplicate records

[sem_db]

I would like to prevent duplication of records in my table. Using the UNIQUE keyword when creating a table is likely the best thing to do.

When attempting to insert a duplicate record, how do I get the KEY value of the record which previously exists in the table and is a duplicate of the record I am attempting to insert?

eg.
unique(name, comment)

private_key_id name comment
1 'bob' 'hello'

attempt to insert ('bob', 'hello') should be prevented but I should ALSO get the private_key_id --- 1 --- which it is attempting to duplicate.

[nemonoman]

You would probably do well to use an automatic incremental counter. In mySQL you can set a column to be autoincrement.

In MySQL you would then insert values for all relevant fields EXCEPT the autoincrement ID. That would be set for you...1 for record one, 2 for record two, etc.

Alternatively you could programatically create a unique id for each record (for example using the uniqid() function.

There are other strategies for this sort thing in other databases, BTW. Oracle has a sequence function that is very helpful -- sadly lacking in MySQL, but it can be duplicated with some little effort.