your main issue seems to be that you really need to normalize your db. using your example of pepper 220 gr, when you examine that string it consists of 3 parts, pepper = product description or product name, 220 = weight or volume or measurement of product, followed by the measurement type. All 3 of those properties really dont make a unique item but instead are properties of the item. I think that multiple tables would give you a better db design and fix some of your isses with the constraints. looking at your current create table statement you have columns that belong in a store table, an item table, a purchase history table as well as store_carried table. just imho.
Woody --- On Tue, 7/1/08, flakpit <[EMAIL PROTECTED]> wrote: From: flakpit <[EMAIL PROTECTED]> Subject: [sqlite] Multiple constraints per table? To: sqlite-users@sqlite.org Date: Tuesday, July 1, 2008, 1:10 AM This is the way I normally create my shopping database, but this leads to thousands of duplicates. CREATE TABLE shopping(item TEXT,units TEXT,quantity TEXT,category TEXT,shop TEXT,aisle TEXT,price TEXT,total TEXT,date TEXT,note TEXT,record INTEGER PRIMARY KEY AUTOINCREMENT) 'item' is the full retailer's description for this shopping item. If I make this unique, then i eliminate all duplicates and any further entries of this item, so that's no good. item TEXT CONSTRAINT item UNIQUE is it legal sql syntax to allow more than one constraint field in table creation? I need at least these four below to guarantee that duplicate items do make it into the database but not on the same day. item TEXT CONSTRAINT item UNIQUE units TEXT CONSTRAINT units UNIQUE shop TEXT CONSTRAINT shop UNIQUE date TEXT CONSTRAINT date UNIQUE So the below three records would be allowed as the unit weight is different and also there are two different dates and as everyone knows, peppers come in all shapes and sizes (grin) (This is okay) pepper, 120gm, coles, 02/02/2006 pepper, 50gm, coles, 02/02/2006 pepper, 50gm, coles, 04/11/2007 Or would the multiple constraints work globally (if legal syntax) Would it end up with only the single record below because the all constraints operated globally? pepper, 120gm, coles, 02/02/2006 -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18209309.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
