PrimaryKey Problem

[dgrewe]

OK what am I doing wrong ?

The question is, Why am I getting the Fox error "The uniqueness of Primary Key Violated" from what I have done below ??

Trying to create a database to document the classes of a Very Large application.
I created 3 tables
LIBRARIES - 21 unique records for the different Class Libraries in the Application.
CLASSES - ends up with 5192 records
PURPOSES - 1to1 Relation with CLASSES with memo fields for purpose & Reason (plus audit fields for new , obsolete, updated , etc)

Basic Structure of CLASSES and PURPOSES was created with
use c:\xxxx\(classname.vcx)
copy stru to CLASSES
copy stru to PURPOSES
Modified the structure and changed the following MEMO fields to VarChar's. Names did not change but I dropped all the reserved fields.

UNIQUEID=C(15)
CLASS = VC(25)
CLASSLOC =VC(25)
BASECLASS=VC(25)
OBJNAME=VC(50)
PARENT=VC(50)
FOLDER M (Added it)
code to populate table CLASSES is
CLOSE ALL
USE CLASSES EXCLU
ZAP
COPY STRU TO LIBTEMP
SELECT 0
USE LIBRARIES
SCAN ALL
lcFolder = ADDBS(UPPER(ALLTRIM(LIBRARIES.FOLDER)))
lcClass = UPPER(ALLTRIM(LIBRARIES.CLASSLOC))
lcFileName = lcFolder + lcClass + ".VCX"
SELECT 0
USE (lcFileName)
COPY TO libtemp
USE libtemp EXCLUSIVE
replace ALL LIBTEMP.FOLDER WITH lcFolder
replace ALL LIBTEMP.CLASSLOC WITH lcCLass
USE
SELECT CLASSES
APPEND FROM LIBTEMP
SELECT LIBRARIES
ENDSCAN
USE IN LIBRARIES
SELECT CLASSES
DELETE ALL FOR EMPTY(class)
PACK
..
.. code to upper() all the values in the VarChar fields..
.. code to create regular indexes ..
..
CLOSE ALL
USE PURPOSES
APPEND FROM CLASSES
.. (Played with the Audit fields)..
CLOSE ALL

I then created the database ClassLibrary and added the 3 tables CLASSES, LIBRARIES, PURPOSES

close databases all
close tables all

open database CLassLibrary exclusive
use CLASSES EXCL
Modi Stru
tried to set the index on UNIQUEID+CLASSLOC+OBJNAME as Candidate or Primary. Here is where I get the error.

But if I do the following in the command window

set deleted off
set unique ON && (ON or OFF has no effect on the outcome)
INDEX ON UNIQUEID+CLASSLOC+OBJNAME TAG UPRIKEY UNIQUE
set order to
delete all
set order to uprikey
recall all
set unique OFF
set order to
browse for deleted()
No records are shown so there are no duplicates in the table

I've have done this before to document other applications (none as big as this) without problems. First time I've done it in VFP9 using VarChar instead of Char fields. Any Ideas why I can not get the Primary / Candidate Index ?


David W. Grewe Dave

 

[Olaf Doschke]

Could it be just because of the varchar fields? Accessing a varchar eg ?varcharfield results in the rtrimmed value, so concatenating varchars could be a problem.

Even if your test on double values is successful, I'd try to change to normal char fields. You don't save diskspace anyway with varchar fields.

Bye, Olaf.

 

[dgrewe]

Thanks for the suggestion Olaf,
I tried that after posting the question and using Char did not make a difference. I still get the error.


David W. Grewe Dave

 

[Mike Lewis]

Dave,

tried to set the index on UNIQUEID+CLASSLOC+OBJNAME as Candidate or Primary. Here is where I get the error.

What exactly was your code here?

The reason that I ask is that "as Candidate or Primary" isn't part of the syntax, so I was wondering what exactly you entered.

If you are using INDEX ON, that's fine for creating a candidate key, but not a primary. To create a primary key, add a PRIMARY KEY clause to your CREATE TABLE.

Also, you later wrote:

INDEX ON UNIQUEID+CLASSLOC+OBJNAME TAG UPRIKEY UNIQUE

Are you sure you want the UNIQUE keyword here? As I'm sure you know, this doesn't create a primary or candidate key, and can't in itself give rise to a unique key violation.

If you could clarify the above points, we can take it further.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Mike,

Dave created the UNIQUE key and used the code snippet to test if there are any double combinations of UNIQUEID+CLASSLOC+OBJNAME, this is a valid test, it first deletes all records, then recalls the records in the UNIQUE index and if there are no deleted records left, there are n0o double keys.

Dave, why no introduce a new primary key field to get rid of the problem. Maybe you'll see in the results where the double key get's in...

Bye, Olaf.

 

[Olaf Doschke]

Mike,

second: I think Dave did use the table designer to manually add the primary key on that expression and got an error then.

Bye, Olaf.

 

[Olaf Doschke]

Dave,

did you have SET DELETED OFF, when browsingf For Deleted() in your check? If not, you won't see the offending records, there seem to be some.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

Thanks for the clarification. I should have realised Dave was using INDEX ON ... UNIQUE to test for duplicates.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

Olaf,
Yes I tried it with Set Deleted Off.

Mike,
As Olaf Pointed out, I tried to make the index Primay & Candidate in the Table Properties, Index Tab of Modify Structure.

I even tried
INDEX ON .... TAG PRIKEY CANDIDATE and PRIMARY
Same Resultes as in the Properties screen.

I also Tried
INDEX ON UNIQUEID+CLASSLOC+OBJNAME+CLASS+BASECLASS+PARENT TAG PRIKEY CANDIDATE
same error

Changed all the fields to CHAR tried again, Same Error.

Ran every Test I can think of on the table to find duplicate records. - Results - ZERO.

Tried Deleteing the Table and rebuilding it.
With no records I did created the Candidate then Primary key on UNIQUEID+CLASSLOC+OBJNAME and it worked.
(I know I only needed to do it once but did both anyway)

Did
APPEND FROM C:xxxx\(classname.vcx) and it failed because of the Error

did
use C:\xxxx\(className.vcx)
copy to test
INDEX ON >>>>> TAG PRIKEY CANDIDATE and got the error
Ran Duplicate record checks on TEST.DBF and found None.

I'm stumped.

David W. Grewe Dave

 

[dgrewe]

Olaf,
Just reread your earlier post and noticed a question you asked and I over looked it.

Yes I could create a unique key field in the CLASSES / PURPOSES tables. But, The idea is to have a table to hold the documentation for the classes, Hense table PURPOSES. The PURPOSES.DBF holds all the audit info and the a place for the comments on the Why's, Whats, and Wherefores of the CLASSES table.

When new classes are Added to the library or the Code in the methods is changed by programmers, the program posted above can be run to Rebuild the CLASSES table and get the new classes and Methods. A program can them be run to notify me of what classes were changed and added so I can update the documentation and indicate classes as New, Obsolete, etc. then post the docs' on the Company web site for the programmers in the 10 countries that work on it.

If the CLASSES table is rebuilt and a new PRIKEY is assigned to the records that will break the relationship between the CLASSES and PURPOSES tables.

I have thought about not ZAPPING the CLASSES table before the rebuild and using a rebuild.DBF and then look for records that have added / changed.

The problem here is the same, with out a PRIKEY, I can not be assured I'm on the same record in all 3 tables.

David W. Grewe Dave

 

[Olaf Doschke]

Hi Dave,

I see the need for a constant primary key. But couldn't you update records based on locating the expression you now want to use for the primary key.

I just tried with some vcx, copied it to a free table, then changed the classloc and objname memo fields to C(80) each and could create a candidate key without problems, even though there are records, where uniqueid is "Class" or "RESERVED" and empty Classloc, due to Objname it's unique.

Trying without changing from Memo to Char fields index creation failed, but not due to uniqueness violation, the error message is just "error building key..."

Bye, Olaf.

 

[Mike Lewis]

Dave,

This is a bit of a long shot, but is it possible you've got NULL values in any of the key fields? If you have, it's just possible that they might be causing the problem. But don't get too excited -- it's just a guess on my part.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

Mike,
Thanks for the Guess, I would not have thought to look for that, but the problem is elsewhere, there were no nulls in the table.

Olaf,
UNIQUEID+OBJNAME is Unique in one ClassLibrary, but could be duplicated in a second class from a different Library. That is why UNIQUEID+CLASSLOC+OBJNAME can not be duplicated.

The Problem seems to be in only one class library.
I manually imported 20 Class Libraries with no problem. The one I picked on first to test seems to be the problem.
Looks like I have a Corrupt Class Library VCX (DBF) File.

Mike, Olaf, Thanks for the help.


David W. Grewe Dave

 

[dgrewe]

Mike, Olaf,
Dig this

Use c:\xxx\classname.vcx exclusive
pack memo
pack
use CLASSES
append from c:\xxx\classname.vcx

Problem is gone, I have my Primary Key on UNIQUEID+CLASSLOC+OBJNAME

Thanks again for the comments.

David W. Grewe Dave

 

[Olaf Doschke]

Hi Dave,

that suggests you had deleted records with duplicate uniqueid+classloc+objname. I think this is quite normal for classes edited several times. Your test with the unique index should have shown these records...

Or have you moved classes from classlibA to classlibB?

Well, perhaps it will stay just one of these mysteries...

Bye, Olaf.

 

[Mike Lewis]

Dave,

Glad to hear the problem has gone away. It's a pity you didn't work out the exact cause, but at least you can progress now with your documentation exercise.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

Olaf, I think you hit it,
Classes have been moved from one library to another in the past. I checked for deleted records when I started this project and did not see any. Maybe I forgot to set deleted off when I was doing my Eval.

Mike, Glad you see you have the same view of documentation I have "exercise"


David W. Grewe Dave