Page 1 of 1
INDEX with UNIQUE clause
Posted: Fri Apr 02, 2010 1:33 pm
by l3whmg
Hy guys,
I need heeelp!
I don't know if it's my mystake, but today I can insert a record with the same key into one .DBF table!!!
I've create index -.NTX- with this:
Code: Select all
INDEX ON camcodice TO (cIndexname) UNIQUE
When I open .DBF I'm using this
Code: Select all
DBUSEAREA( .T., DBSETDRIVER(), cFile, cAlias, .F., .F.)
and for (all) index join with this table I use this
I don't remember nothing changes about index on Harbour compiler.
Re: INDEX with UNIQUE clause
Posted: Fri Apr 02, 2010 6:04 pm
by sudip
Hello Luigi,
AFIK, UNIQUE clause in INDEX command determines if each key value inserted in to order should be unique. Duplicate key values are not added to the order. But, it will not restrict duplicate entry in the table.
When I use DBF, I check duplicate key field programatically. But, SQL databases have this feature of duplicate checking, using primary key clause or unique clause. In the following example, both Compid and Compnm are unique keys:
Code: Select all
CREATE TABLE IF NOT EXISTS Comp ( Compid Integer PRIMARY KEY, Compnm TEXT UNIQUE COLLATE NOCASE)
Please correct me if I said anything wrong
With best regards.
Sudip
Re: INDEX with UNIQUE clause
Posted: Fri Apr 02, 2010 7:43 pm
by l3whmg
Hi Sudip,
many thansk for your fast replay.
I think you are right, because I've done one test and it's seem you describe.
I remember (perhaps wrong) something about one Clipper error
Perhaps my mind is out of order
The problem started because I was doing one test of a program and I realize I inserted a record with the same key and so I lost my head
About MySql (I prefer PostGreSql becasue I'm able to lock record/row) I think you're right but I've never done a realistic audit.
Many thanks for your help
Re: INDEX with UNIQUE clause
Posted: Fri Apr 02, 2010 7:53 pm
by Czarny_Pijar
AFAIK you can allways insert the record into dbf database, and UNIQUE index will not stop you. No such things like 'referential integrity' known, for example, in the Access.
In my programs I check the existence of the suspicious record, then go on. In a pseudocode that will be:
Code: Select all
SELECT first_base
key := ...
SELECT second_base
SEEK key
if !FOUND()
APPEND BLANK
endif
REPLACE ...
Maybe I'm wrong, then let somebody correct me.
Re: INDEX with UNIQUE clause
Posted: Fri Apr 02, 2010 8:06 pm
by l3whmg
Hy Czarny_Pijar
I do the same control in all my programs, but today my mind seems to be on vacation
Many thanks to you
Re: INDEX with UNIQUE clause
Posted: Sat Apr 03, 2010 1:02 pm
by sudip
Hello Luigi,
My sent SQL codes are taken from my current SQLite project. It works fine with duplicate check
With best regards.
Sudip