SQLitening Support Forum

Support Forums => You've got Questions? We've got Answers! => Topic started by: Bern Ertl on February 11, 2009, 04:12:12 PM

Title: How do you ensure unique composite value of pair of fields in a table
Post by: Bern Ertl on February 11, 2009, 04:12:12 PM
I've got a table T1 that has fields F1, F2, F3, F4

I'm using the RowID as the primary key (in other words, I'm not defining any of my F# fields as primary keys).

I was to ensure that the pairing of (F2, F3) is unique in the table (and F2 and/or F3 can be NULL).  I'm assuming there is a way to enforce this in the table definition, but I'm not sure how. Ideas?
Title: Re: How do you ensure unique composite value of pair of fields in a table
Post by: Fred Meier on February 11, 2009, 05:07:43 PM
   slExe "Create Table T1 (F1, F2, F3, F4, Unique (F2, F3))"

Quote from SQLite3 doc:
QuoteFor the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique.
So SQLite3 will allow multiply rows with same value in F2 as long as F3 is NULL.  Best to not allow NULLs or
you will have to control NULL duplicates yourself with code.
   slExe "Create Table T1 (F1, F2 Not Null, F3 Not Null, F4, Unique (F2, F3))"
Title: Re: How do you ensure unique composite value of pair of fields in a table
Post by: Bern Ertl on February 11, 2009, 05:13:58 PM
Thanks Fred.  Not too thrilled at how SQLite handles NULLs in this case.  Hmm....