How to make a non-Primary Key a unique valid in mysql?

Question

I am working on an inventory system for my company. Each item in our inventory has a unique certification number. However, this certification number can and does commonly change while it moves through the system from purchase to evaluation to sale. Because the certification number can change, I use an item_id field that's just an auto incrementing integer as the primary key so that I can reference that in other tables and not have to update the other tables if/when the certification number changes.

How do I make it so that I can also make the certification field require a unique value? Is there a way to do an insert query that is something like "on duplicate key update cost = '$cost'" but would check the certification field instead of the key for that duplication? Do I just make it as the 2nd key in the table?

Thanks for your time everyone.

You probably need a NOT NULL constraint on the certification column, too. — Mike Sherrill 'Cat Recall', Jun 01 '13 at 18:09

score 0 · Answer 1 · answered Jun 01 '13 at 17:17

0

here's a way to add UNIQUE constraint on non-primary key field,

ALTER TABLE tableNameHere ADD CONSTRAINT tb_Uq UNIQUE (CertificationNumber)

answered Jun 01 '13 at 17:17

John Woo

258,903
69
498
492

[here's the importance of naming constraints](http://stackoverflow.com/questions/1397440/what-is-the-purpose-of-constraint-naming) – John Woo Jun 01 '13 at 18:32

score 0 · Accepted Answer · answered Jun 01 '13 at 17:25

0

Use:

ALTER TABLE yourtable ADD UNIQUE (yourcolumn);

answered Jun 01 '13 at 17:25

Gimmy

3,781
2
18
27

How to make a non-Primary Key a unique valid in mysql?

2 Answers2