At 11:03 AM 03-07-02 +0200, you wrote:

>Hi Helen,
>
>Wednesday, July 03, 2002, you wrote:
>
>HB> Reading your many postings,
>
>Ops, I hope I'm not abusing of the list...
>I think that: reading the manual, trying some coding and discussing
>with other people is the fastest way to understand any technology. ^_^

I wasn't suggesting you should post less.. :-))

>Okay: now I'm confused! :)
>What's the difference between an UNIQUE CONSTRAINT and an UNIQUE KEY
>CONSTRAINT?
>Is it only a logical one?

No, they are one and the same. You don't need the keyword KEY - sorry to
add confusion here - I'm not even sure that IB/FB accept UNIQUE KEY. But
UNIQUE on its own is 4 keystrokes shorter - that's a benefit. :-)

>I mean: a foreign key may references a primary key and an unique key
>but it cannot reference an unique constraint wich is not key?

To clarify, a foreign key may reference a column which has a UNIQUE
constraint on it. If it is referenced from a foreign key, then it is a
unique key. Even if it isn't referenced from a foreign key, I suppose you
could say it is just sitting there waiting to be asked to be a key. :-)

However, if the row has an atomic integer primary key you should use this
for the FK relationship, since it is shorter and is simplex. Complex
foreign keys are are PITA.

>HB> And index is not a key and a key is not an index.
>
>...uhmmm... but creating a key implies the creation of an index too,
>right?

It doesn't just *imply* it, it enforces it by creating the index automatically.

>I mean, they are two different thing but if I have a key I would have
>its correspondent index too?

You would have it created automatically and you should not then go and
create another index yourself which is identical (ascending, unique).

>HB> is that, if you were so foolish as to allow users to enter the
>HB> value for id in Raul's example, then, by his rules, both would be
>HB> valid rows.
>
>Of course is a stored procedure the one that may insert data into the
>table. My problem were only a logical problem: someone that reads the
>code could misunderstand the meaning of the fields and think that ID
>may be duplicated.
>
>HB> As a sensible and conscientious programmer, you would use a
>HB> generator to generate the id value and never show this number to
>HB> users.
>
>Yes, of course this is what I do.
>
>HB> Better advice would be to use an atomic primary key, e.g. id integer,
>[...]
>
>This is exactly what I was doing while responding to Raul e-mail! :)
>Anyway thank you for the suggestion!
>
>HB> you store these values in upper case, otherwise these would be
>HB> treated by the database as distinct entries:
>
>Well: datas are taken from existing document by a Python script.
>Anyway I would prefere if the DBMS can translate in uppercase the
>values - I don't want is the Python script to make it because the
>logic would be compromised.

Agreed.

>I will check in the manual if there are some useful string functions
>in Firebird that allow the programmer to translate a string in its
>uppercase version.

Look for UPPER(). Place BeforeInsert and BeforeUpdate triggers on these
columns that do
...
if (new.MyColumn is not null) then
new.MyColumn = UPPER(new.MyColumn);
...
cheers,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________