Re: [sqlite] Duplicate records (null handling)
On 6/17/06, David M. Cook <[EMAIL PROTECTED]> wrote: I took it to mean NULL is distinct from any other value, not that each NULL is distinct from any other NULL. I believe it means that NULL is distinct even with itself. In other words, I believe NULL == NULL is false.
Re: [sqlite] Duplicate records (null handling)
On Sat, Jun 17, 2006 at 10:16:47AM -0700, Will Leshner wrote: > But if NULL is distinct for UNIQUE columns, isn't that the result you > would expect? I took it to mean NULL is distinct from any other value, not that each NULL is distinct from any other NULL. Why would one want NULL to behave this way? Dave Cook
Re: [sqlite] Duplicate records (null handling)
On 6/17/06, David M. Cook <[EMAIL PROTECTED]> wrote: says that nulls are distinct in a column, but I haven't found that to be the case (this is a test with 3.3.5) create table foo (bar text, baz text, unique(bar, baz)); select * from foo; bar|baz quux|null quux|null quux|null But if NULL is distinct for UNIQUE columns, isn't that the result you would expect?
Re: [sqlite] Duplicate records
In message <[EMAIL PROTECTED]>, Jay Sprenkle 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? > > You might try adding NOT NULL to your column constraints and > a default to an empty string in the create table. > I thought UNIQUE implied NOT NULL but I might be > wrong. Thanks a lot - I think I've got it working now: create table main (f1 TEXT not null default '~', f2 TEXT not null default '~', f3 TEXT not null default '~', t1 TEXT not null default '~', t2 TEXT not null default '~', tn TEXT not null default '~'); ...gives me an error when I try to insert two similar rows :-) Now I'll just have to work out how to sensibly deal with the error in my C app. Cheers, Adam -- Adam Richardson Carpe Diem
Re: [sqlite] Duplicate records
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.
Re: [sqlite] Duplicate records
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? You might try adding NOT NULL to your column constraints and a default to an empty string in the create table. I thought UNIQUE implied NOT NULL but I might be wrong.
[sqlite] Duplicate records
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
