Thread: Preventing duplicate records according to several fields

Preventing duplicate records according to several fields

From
CSN
Date:
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

Re: Preventing duplicate records according to several fields

From
Michael Glaesemann
Date:
On Sep 21, 2005, at 10:34 AM, CSN wrote:

> id, title, yield, directions
>
> and would like to prevent duplicate records from being
> added (i.e. according to the title, yield, and
> directions fields).

<snip />

> - Create a unique index across the title, yield, and
> directions fields.

This scenario is exactly what the UNIQUE contraint is for.

Michael Glaesemann
grzm myrealbox com