primary key?

[sfullman]

Hi there,

There's probably an understanding I'm lacking here. I"m intermediate and higher on database architecture, but with mySQL I want to have the following table (this is a skeleton):

ID
Category
SubCategory

Iwant ID to be an auto increment, primary key, but I also want to make Category and subCategory, when joined together, be "Unique" (i.e. it's OK to have a category several times, and ok to have a subcategory several times, but you cant have category_subcategory twice).

I would like the primary key in numeric formate for ID because I can reference a single value easily as a foreign key in another table.

Any insights or suggestions on this?

Sam Fullman
Compass Point Media

[chriskl]

Just add a unique constraint on category,subcategory. Don't know if this is mysql syntax, but:

alter table tablename add unique(category, subcategory);

Chris

[sfullman]

I can't really do alter table add unique(Field, Field) ==

because It's OK to have a duplicate in ONE column, it's just not OK to have both Category AND SubCategory be a duplicate.

Sam

[tomhath]

Chris' suggestion is what you want. The unique index will be the combination of the two fields.