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.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
