Hello,

I was wondering about using the IN operator with check constraints to
restrict the values of a column to a certain number of elements
(strings). Is there any reasonable maximum for the number of elements
in the set?

Given:

CREATE TABLE Languages
(
iso_code CHAR(2) NOT NULL PRIMARY KEY CHECK (iso_code IN ('en',
'de', 'fr', ...))
)

I might extend the list every now and then, e.g. when supporting a new
language. But where is the point the check constraint doesn't really
serve any more purpose?

I could remove the check and allow any combination, but I'm not sure
why I should do that.

Some people believe that using ENUMs, which are similar, only makes
sense for a rather low number of elements, but I'm not sure what
should be wrong with larger sets/lists.

Any comments on that appreciated.

Karsten

Karsten Wutzke <kwutzke (AT) web (DOT) de> wrote:
Use a lookup-table instead. (FK-constraint)


Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Andreas Kretschmer, 07.04.2010 08:51:
I was about to answer the same, until I realized his languages table *is* the lookup table and he wants to limit the values in there.

Thomas

Thomas Kellerer <OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:
That make no sense. Create a table with a CHECK-constraint and alter
this constraint every time for a new record?




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

On 7 Apr., 18:46, Andreas Kretschmer <akretsch... (AT) spamfence (DOT) net>
wrote:
Yes. I know it sounds contradictory at first, but if I don't use a
lookup table, I have to adjust the CHECK for each new entry, too.
Lookup tables are for situations where you want/need to *reuse* a
check constraint, right? Using this kind of "double-check" clearly
flags the table as a lookup table and not a regular one (even though
the check is redundant). I mean, if it's still somewhat static, I do
not change it often - if at all - anyway. It's truly another issue I'm
very undecided about.

But the question was rather: is there any limit on the IN check
itself, I mean the list, if that makes sense?

Example: country codes.

http://www.iso.org/iso/iso-3166-1_decoding_table

While they are CHAR(2), there are 26*26 = 676 combinations, of which
not every one is valid. So, I might want to create a CHECK (cc IN
('AD', ..., 'ZW')) or so, with several hundred entries. Does it make
sense to create such a big IN check? Is there a reasonable limit to
the number of elements in the list? If so, how many? 7? 10? 14? 23?
62? 89? 200?

Karsten

Karsten Wutzke <kwutzke (AT) web (DOT) de> wrote:
Well, haven't tried that, but i think up to several thousand rows it is
possible, so i don't see a problem in this case. Try it, and tell us
about the result ;-)


Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

On 04/08/2010 12:48 AM, Karsten Wutzke wrote:

I have happily used IN() with 30k elements, but the right way to solve
your issue is to have another single-column table with the full iso-3166
combinations, and make languages.iso_code both a primary and foreign key.

On 2010-04-07, Karsten Wutzke <kwutzke (AT) web (DOT) de> wrote:
definately use a lookup table. having constraints that need to be
updated every time a country changes name they time release a new
version seems like a mug's game
(see all those entries marked transitional...)

not when the rules will potentially change every few years.

somewhere Out past 10000 it could start getting more expensive than
the foreign key check. somewhere before that maintenance cost will
get excessive.




--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Years? That sounds ok for me...

So using a small list does not or only have marginal performance
differences? Why is that so? Because the DBMS does the majority of
integrity checks for the values on ALTER TABLE rather than on row
inserts?

Karsten

On 2010-04-08, Karsten Wutzke <kwutzke (AT) web (DOT) de> wrote:
how soon Y2K is forgotten!

having to dig into the logic of the application to make changes that
could be anticipated seems like a bad plan.

If it's a frequently performed test count on the relevant table or index
already being in memory....

if the check rule is long enough there is more cost loading the rule than
examinining the parts of the index needed for the for the FK check.

It's a database, it writes stuff to disk and reads it back. that is
always going to be slow. Sql is compiled only to bytecode (the
planner does this), that bytecode is going to be slow too.


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---