FoxPro 9.0 - Free Table (DBF) - Set Fields to be Primary Keys

smithmrk
smithmrk used Ask the Experts™
on
I have a free standing DBF FoxPro Table that I would like to set a couple of Fields to be Primary Keys so that Duplicate Data doesn't get entered.

I haven't been able to figure out how to do it.

Thanks,
Mark
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
FoxPro does not allow to create index from several columns separated by comma in index command but it allows to create an index based on expression. So you have to convert all involved fields to a text and concatenate them. Also to ensure index uniqueness you have to create Candidate index when the table isn't in database.

Example:
CREATE TABLE MyTest (AccYear N(4,0), CurrCode C(3), Amount Y)
INDEX ON STR(AccYear,4)+CurrCode TAG YearCurr COLLATE "Machine" CANDIDATE
INSERT INTO MyTest VALUES (2012, "USD", $5000)
INSERT INTO MyTest VALUES (2012, "CZK", $5000000)
*-- Following command should generate an error
INSERT INTO MyTest VALUES (2012, "USD", 0)

Open in new window


It is recommended to create keys of the same length in all records and the key length must be less than 240 characters (valid for Machine collation). The shorter index key the better.
CaptainCyrilFounder, Software Engineer, Data Scientist
Commented:
Try to validate that via the screen if it's a free table.

IF SEEK(cValue, cTable, cIndexKey)
   WAIT WINDOW "This is a duplicate!"
   RETURN 0
ENDIF
One more note:

If your app uses SET DELETED ON setting then the Candidate index should contain FOR clause:

INDEX ON STR(AccYear,4)+CurrCode TAG YearCurr FOR !DELETED() COLLATE "Machine" CANDIDATE

It will avoid problems with "invisible" duplicities.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Commented:
Just to clarify Pavel's comment...
So you have to convert all involved fields to a text and concatenate them

You don't really have to convert the data table field types themselves.

Instead you can build an Index Expression using the String values of the various fields
Something like:
    INDEX ON CustName + DTOS(OrderDate) + STR(OrderNum) TAG Key

And, Yes - follow CaptainCyril's advice above about doing a SEEK on the Expression value before attempting to ADD a new record

Good Luck

Author

Commented:
Quick Update...I got stuck on a other project and haven't had time to get back to this.
I wanted to let everyone know I've looked over all your wonderful comments and suggestions, I just haven't had time to test out anything as of yet.

Stay tune...next week for sure!

Thanks,
Mark

Author

Commented:
Still haven't had time to test out these solutions, but I've had to move onto other projects and tasks.

I didn't want to leave this question hanging out there forever so I gave each of you the same amount of points.  If I ever do get back to this...I will try your solutions and follow up at that time.

Thanks again!
Mark

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial