Re: [sqlite] Trouble coding conditional UNIQUE

On Thu, 16 Jun 2016 23:23:33 +0200 Dominique Devienne wrote: > > > On Behalf Of James K. Lowden > > > > > > create view vParts as > > > select 1 as Matched, * from Parts > > > UNION > > > select 0, * from UnmatchedParts > > > > Why UNION instead of

Re: [sqlite] Trouble coding conditional UNIQUE

On Thu, Jun 16, 2016 at 9:40 PM, Drago, William @ CSG - NARDA-MITEQ < william.dr...@l-3com.com> wrote: > > On Behalf Of James K. Lowden > > > > create view vParts as > > select 1 as Matched, * from Parts > > UNION > > select 0, * from UnmatchedParts > Why UNION instead of

Re: [sqlite] Trouble coding conditional UNIQUE

> -Original Message- > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users- > boun...@mailinglists.sqlite.org] On Behalf Of James K. Lowden > Sent: Tuesday, June 14, 2016 8:55 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqli

Re: [sqlite] Trouble coding conditional UNIQUE

On Tue, 14 Jun 2016 16:27:29 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > Once the part has been grouped into a set (Matched=1) it receives a > unique permanent serial number and the temporary serial number can be > reused, so (Model, TemporarySerialNumber)

Re: [sqlite] Trouble coding conditional UNIQUE

> -Original Message- > From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users- > boun...@mailinglists.sqlite.org] On Behalf Of James K. Lowden > Sent: Tuesday, June 14, 2016 9:48 AM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqli

Re: [sqlite] Trouble coding conditional UNIQUE

On Tue, Jun 14, 2016 at 8:47 AM, James K. Lowden wrote: > On Mon, 13 Jun 2016 19:11:29 + > "Drago, William @ CSG - NARDA-MITEQ" wrote: > > > I need UNIQUE(B, C) only when E=0. > > A conditional constraint is evidence that you have two

Re: [sqlite] Trouble coding conditional UNIQUE

On Mon, 13 Jun 2016 19:11:29 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > I need UNIQUE(B, C) only when E=0. A conditional constraint is evidence that you have two kinds of things represented in one table: those E=0 types that are identified by {B,C}, and the

Re: [sqlite] Trouble coding conditional UNIQUE

g > Subject: Re: [sqlite] Trouble coding conditional UNIQUE > > Apologies Bill, I hit send before linking the actual documentation, here > goes: > > https://www.sqlite.org/partialindex.html > > See especially section 2.1 > > > Good luck! > Ryan > > > On 2016/06/

Re: [sqlite] Trouble coding conditional UNIQUE

CREATE UNIQUE INDEX bcuif0e ON (b,c) WHERE e = 0; Assuming you're not using a pre-WHERE clause on Indexes version of SQLite.   Since unique constraints and unique indexes are functionally identical. Peter On Monday, June 13, 2016 12:11 PM, "Drago, William @ CSG - NARDA-MITEQ"

Re: [sqlite] Trouble coding conditional UNIQUE

Apologies Bill, I hit send before linking the actual documentation, here goes: https://www.sqlite.org/partialindex.html See especially section 2.1 Good luck! Ryan On 2016/06/13 9:16 PM, R Smith wrote: On 2016/06/13 9:11 PM, Drago, William @ CSG - NARDA-MITEQ wrote: All, I am having

Re: [sqlite] Trouble coding conditional UNIQUE

On 2016/06/13 9:11 PM, Drago, William @ CSG - NARDA-MITEQ wrote: All, I am having trouble figuring out how to implement a conditional UNIQUE constraint. Assume the following table: CREATE TABLE myTable ( A INTEGER PRIMARY KEY, B TEXT NOT NULL COLLATE NOCASE, C TEXT NOT NULL COLLATE NOCASE,

[sqlite] Trouble coding conditional UNIQUE

All, I am having trouble figuring out how to implement a conditional UNIQUE constraint. Assume the following table: CREATE TABLE myTable ( A INTEGER PRIMARY KEY, B TEXT NOT NULL COLLATE NOCASE, C TEXT NOT NULL COLLATE NOCASE, D TEXT NOT NULL COLLATE NOCASE, E INTEGER, F TEXT COLLATE NOCASE, G