Hello,



I plan to create a table with 3 unique keys.

Combination of three fields has to be unique for each row in a table that
are vendor ID (char 8), vendor name (char 40), and vendor office (5).



Will it be okay to have a unique key which has a long character such as
vendor name?

How should I index those three fields? Those fields will be searched many
times.



RCW

You would need to have three seperate non-unique indexes for id, name
and office respectively. (in your posting you say that these are
unique - this sounds strange given your later statement)

You should add a unique index that is a composite of the above.

Index names that long are no problem (assuming of course that you dont
have millions of rows).

Some additionals notes:

(1) If this database table contains vendors, I would have expected
that vendor id would be the unique primary key??? If it cannot be
unique, I would consider adding another column that is the unique
primary key (if you have not already done so).

(2) Consider making vendor name a varchar(40) rather than a char(40)

(3) You will need to investigate the types of queries that users make.
This may result in additional indexes (or removing some of the above)


"reneeccwest" <reneeccwest (AT) hotmail (DOT) com> wrote

reneeccwest (reneeccwest (AT) hotmail (DOT) com) writes:
It sounds funny to me that the vendor ID alone would not be unique.
OK, I can envision that a vendor has several offices, but several
names?

And if a vendor can have several offices, you should probably have the
offices in a subtable.

And as "Mystery Man" said, make that vendor name varchar(40).


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp