> -----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: [sqlite] Trouble coding conditional UNIQUE > > On Tue, 14 Jun 2016 16:27:29 +0000 > "Drago, William @ CSG - NARDA-MITEQ" <william.dr...@l-3com.com> > 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) doesn't have to be unique > > anymore. Information about what parts belong to what set is stored in > > a different table. > > > > So is using a conditional constraint in this case okay, or is there a > > better way? > > It's OK, Bill; it was OK before I posted. I'm only suggesting an alternative > you > might like better. > > You pasted your particulars into my generic description, and they fit > perfectly. You have two sets: > > 1. Parts with a unique, permanent serial number. > 2. Parts with a temporary, reusable serial number. > > You could remove the latter set to a new table, perhaps "UnmatchedParts", > having the characteristics you want, namely a primary key or unique > constraint on (Model, TemporarySerialNumber). Then you don't need the > Matched bolean column in either table, solving your original problem (a > conditional constraint). Then you have a view, > > create view vParts as > select 1 as Matched, * from Parts > UNION > select 0, * from UnmatchedParts > ; > > I think there's a natural tendency to put similar things in one table when > they > share common properties, sometimes by adding a discriminator column. I've > seen lots of tables like that, and designed some myself upon a time. SQL > encourages it, because that way you can write just one INSERT, etc., and just > set the flag right. > > When you push hard on such a table, though, by trying to do things right, you > wind up with little conundrums (conundra?) like the one you posted. They > grow out of the fact that the things aren't the same. > They're just a smidgen different, so they need to be tweaked just so, and > before you know it you either have to lean on some oddball feature of the > DBMS, or punt. If you separate them, the you might have more query text, > but each one will be simpler and easier to understand. > > HTH.
This helps a lot. Thank you. I would have never thought of this on my own. I tried it, it works, and I like it. Regards, -Bill CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
