Making values of two columns Unique

[Delphard]

1. Two columns, one is CHAR type, second is INTEGER. None of them is Unique, but combination of them must be. How to make this in MySQL 4.1.10?
2. What kind of Index (INDEX, UNIQUE INDEX,...) I should make on this columns to get better performance?
Thanks in advance!

 

[azzazzello]

make both columns primary keys. That should do exactly what you are looking for. As for the index, the regular one should be fine

 

[r937]

if you make the pair of columns the primary key, then you do not need to declare any other index (unless you want to access the 2nd column separately, in which case a separate index on the 2nd column alone might be useful)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Delphard]

I forgot to say: I already have column that acts as PrimaryKey (ID). I just need that combination of values in these other two columns must be Unique. Is there other approach, without making PrimaryKey on these two columns?

 

[r937]

you already have an ID column that acts as primary key?

you don't need it!

but you can simply add the unique constraint if you insist on keeping the ID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Delphard]

ID must remain as PrimaryKey, because it's used in many Tables as ForeignKey and in Queries as Parameter.
Any other way?

 

[r937]

okay, so add a unique index on the other two columns

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Delphard]

I try this before your reply and find it works!
Thanks anyway!