Jason,

J> Alter Table Company
J> Add Constraint CK_NAME Check (Not Exists (Select Distinct Upper(Name)
J> from Company));

J> Now, I would imagine on a rather large table, this would get slower to
J> store as the record number increased, but it saves you from doing
J> something silly.... like adding an UPPER field and denormalizing your
J> database.

1) you can't guarantee uniqueness if you can't see what other
concurrent transactions are doing (which you can't in a check
constraint, since it's evaluated in the statement's transaction
context). That's what unique constraints are for, I believe. BTW, I
believe your example is also unnecessarily inefficient, as the
DISTINCT clause will probably force a fetch and sort of all the
records in the table before exists() enters the game.

2) I wouldn't call the addition of an UPPER field "denormalizing" the
database, provided that the field is kept in sync through one or two
triggers. Conceptually, that UPPER field is more akin to an index than
a field: it has no place in your database design, yet you are adding it,
for performance reasons.

3) I don't think the problem outlined by the OP is completely solved
by case insensitivity (whether it's implemented through UPPER() or a
case insensitive collation). AFAICS he wants to prevent users from
inserting the "same" name twice. What about "DIESEL CO" and "DIESEL
CO."? Probably an expression-based index and a good soundex UDF would
help here, but I have seen enough databases to know that this problem
is best solved at the user interface level.

Ciao
--
Nando Dessena
http://www.flamerobin.org
======================================================
I support Firebird, I am a Firebird Foundation member!
Join today at http://www.firebirdsql.org/ff/foundation
======================================================