I have a table like so:
id, title, yield, directions
and would like to prevent duplicate records from being
added (i.e. according to the title, yield, and
directions fields). I won't normally be querying on
the yield or directions fields, so I just have indexes
for id and title. What's the best way to prevent
duplicates from being added?
- Before inserting, do a 'select id from stuff where
title=? and yield=? and directions=?'. This would want
the title and directions fields indexed (which seems
like a waste of space since they won't be used except
for rare inserts).
- Create a unique index across the title, yield, and
directions fields.
- Create a 'hash' field by md5'ing the title, yield,
and directions fields, and create a unique index on
it. Then when inserting new records, first create a
hash and check if it already exists, or have the
database automatically handle this (trigger to compute
hash field at insert time - unique index will raise an
exception).
Thanks for any help, insights, suggestions, etc.
CSN
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
