Mangler wrote:
Bob Barrows [MVP] wrote:
>Why not? Include all the fields that make a record unique in the
unique key ... even if it means including all the fields. "unique
key" does not have to mean "unique single-field key". If you need
specifics as to how to do this, let us know what database type and
version you are using.
SQL 2000 - 8.0.760
I appreciate your help and patience on this.
You have two choices: a key (constraint) or a unique index. Look up both
in BOL to see the differences.
You add a constraint to a table by using an ALTER TABLE statement. Here
is the syntax, form BOL:
ALTER TABLE [database.[owner.]]table_name
[WITH {CHECK | NOCHECK}]
{{CHECK | NOCHECK} CONSTRAINT {constraint_name | ALL}
|
[ADD
{col_name column_properties [column_constraints]
| [[, ] table_constraint]}
[, {next_col_name | next_table_constraint}]...]
|
[DROP CONSTRAINT]
constraint_name [, constraint_name2]...]}
Here is an example of a statement to create a unique constraint:
ALTER TABLE dbo.ProductionConstants ADD UNIQUE NONCLUSTERED
(CompanyNumber,DivisionNumber,FiscalYear,FiscalMon th)
ON [PRIMARY]
Here is the basic syntax for the CREATE INDEX statement, from BOL:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [[database.]owner.]table_name (column_name [, column_name]...)
[WITH
[PAD_INDEX, ]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] SORTED_DATA | SORTED_DATA_REORG]
[[,] IGNORE_DUP_ROW | ALLOW_DUP_ROW]]
[ON segment_name]
Here is an example:
CREATE UNIQUE CLUSTERED INDEX IX_ProductionConstants_CompDivFisc
ON dbo.ProductionConstants(DivisionNumber,FiscalYear, FiscalMonth)
WITH FILLFACTOR = 90
ON [PRIMARY]
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.