Subject | Re: [ib-support] Is there any UNIQUE limit? |
---|---|
Author | Matteo Giacomazzi |
Post date | 2002-07-03T09:03:09Z |
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
CONSTRAINT?
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,
right?
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,
--
Matteo
mailto:matteo.giacomazzi@...
ICQ# 24075529
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
CONSTRAINT?
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,
right?
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,
--
Matteo
mailto:matteo.giacomazzi@...
ICQ# 24075529