EXPERT RESPONSE
Excellent question. One of the problems
with using a surrogate key such as an autonumber
for the table's primary key is that you can end up with duplicates
in the "real" primary key.
Consider the following Product table:
prodID prodCode prodDescription
1 XTBW Widget size 27
2 XTCW Widget size 52
3 XTDW Widget one-size
4 YBWD Widget size 27
5 YBWX Widget size 52
In this scenario, assuming prodID is an autonumber
column, you could easily run:
insert
into Product
( ProdCode, ProdDescription )
values
( 'XTDW', 'Widget any size' )
This insert would succeed, and now you have two
rows with the same prodCode.
The "standard algorithm" you're looking for
is the unique constraint.
alter table Product
add constraint uniqueprodcode
unique (prodCode)
Note that "duplicate records" would have to
be equal in all columns. Usually this is not a concern.
If it is necessary to check multiple columns for uniqueness
of the combinations of values, just list those
columns in the UNIQUE clause when defining the constraint.
For example,
alter table Customer
add constraint uniquecustomer
unique (LastName, MiddleInit, FirstName)
For More Information
|
