Subject Re: [ib-support] Is there any UNIQUE limit?
Author Matteo Giacomazzi
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. ^_^

HB> I'm starting to think you are confused about keys in Firebird. A
HB> column (or group of columns) which has the PRIMARY KEY constraint
HB> is unique by nature.

Yes, that's clear!

HB> The engine automatically creates a unique index to support the
HB> primary key constraint, but a unique index alone is NOT a key.

This is clear too! :)

HB> You can also create a UNIQUE KEY constraint on a column or group
HB> of columns. Again, the engine will create a unique index for it
HB> automatically.

HB> If you want a column or group to be a key, you must apply the
HB> appropriate constraint. And don't create additional indexes that
HB> are identical to the constraint indexes!!

Okay: now I'm confused! :)
What's the difference between an UNIQUE CONSTRAINT and an UNIQUE KEY
Is it only a logical one?
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?

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,
I mean, they are two different thing but if I have a key I would have
its correspondent index too?

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.

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.

Thank you!

Kind regards,

ICQ# 24075529