• Welcome to SQLitening Support Forum.
 

How do you ensure unique composite value of pair of fields in a table

Started by Bern Ertl, February 11, 2009, 04:12:12 PM

Previous topic - Next topic

Bern Ertl

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?

Fred Meier

   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))"

Bern Ertl

Thanks Fred.  Not too thrilled at how SQLite handles NULLs in this case.  Hmm....