On Thu, 15 Jun 2006 20:12:41 +0100, Adam wrote: >Hi All,
>I'm creating a database which will look a little like: >create table main (f1 TEXT, f2 TEXT, f3 TEXT, t1 TEXT, t2 TEXT, tn TEXT); >f1, f2 and f3 will always be present. Any or all of the ts might be NULL. >I'd like to prevent entries where all of the fields are the same being >created. How can I do this. I've tried experimenting with UNIQUE KEYs but >haven't been successful - perhaps because of the NULLs? >If this isn't possible my second best option would be a command which >removes existing duplicates. >Thanks a lot for any pointers, >Adam >-- >Adam Richardson >Carpe Diem Use an MD5 signature across the values of all fields and store that value in an indexed additional field. When the apparent duplicate is presented for storage, compute its MD5 signature and do preliminary query to check for existence of a record with the same signature. Reject the new record if the signature is found in an existing record.
