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

>Hi Raul,
>
> I've just noticed that in your solution there's something strange:
>
>RC> create table FILES (
>RC> id integer not null,
>RC> file varchar(200) not null,
>RC> constraint FILES_PK primary key (id, file)
>RC> );
>
> in this way, id could be the same if file is different, right?
> I mean:
> (1, 'testFile.html')
> (1, 'testFile_2.html')
> Are both valid records, right?
>
> Just a doubt...

Matteo,
Reading your many postings, I'm starting to think you are confused about
keys in Firebird. A column (or group of columns) which has the PRIMARY KEY
constraint is unique by nature. The engine automatically creates a unique
index to support the primary key constraint, but a unique index alone is
NOT a key.

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

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

And index is not a key and a key is not an index.

So, the answer to this question:

in this way, id could be the same if file is different, right?
I mean:
(1, 'testFile.html')
(1, 'testFile_2.html')
Are both valid records, right?

is that, if you were so foolish as to allow users to enter the value for id
in Raul's example, then, by his rules, both would be valid rows.

As a sensible and conscientious programmer, you would use a generator to
generate the id value and never show this number to users.

Better advice would be to use an atomic primary key, e.g. id integer,
generated by a generator, and apply a unique constraint to the pathname and
file name. Unfortunately, you are still limited to about 250 bytes for the
index that will be created for the unique constraint. For this reason, and
for reasons of data integrity, I strongly recommend storing the paths and
file names in separate tables, each with an atomic integer primary key and
a unique constraint on the data column. I would also take care to ensure
that, regardless of what the user types in as the paths and filenames, you
store these values in upper case, otherwise these would be treated by the
database as distinct entries:

1, 'd:\mydir\mysubdir\'
2, 'D:\MyDir\MySubDir\'
3, 'd:\MYDIR\MYSUBDIR\'

and so on...
If you are on Linux, of course your paths and filenames will be
case-sensitive anyway so those three "duplications" are not duplications at
all, that is, the following could be distinct in your filesystem:

1, '/opt/mydir/mysubdir'
2, '/opt/MyDir/MySubDir/'
3, '/opt/MYDIR/MYSUBDIR/'

But the risk of users getting one character wrong remains, meaning these
non-atomic columns are *not* good candidates for keys.
heLen

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