INDEX with UNIQUE clause

[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

Code: Select all

(cAlias)->(ORDLISTADD(cIndex))

I don't remember nothing changes about index on Harbour compiler.

[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

[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

[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.

[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

[sudip]

Hello Luigi,

My sent SQL codes are taken from my current SQLite project. It works fine with duplicate check

With best regards.

Sudip