append with PK 1

[foxup]

Hi,

I have a simple question and I'm sure there's a simple answer. I have a .DBF which has a "PK" field. Straight from the command box, when I try to append to it I get an error.

I tried appending field by field (ie. appe from blahblah fields blahblahblah (without the PK field) and I still cannot append.

Can you please help.

FYI: The error I get is "Uniqueness of index PK is violated"

Thanks,
Foxup!

 

[Mike Lewis]

Yes, that's precisely what happens when you do an APPEND BLANK. Also when you do an INSERT without specifying the value for the primary key.

Either way, the key field will be blank. That's no problem with the first record that you append. But if you then append another record, that too will have a blank value in the key field. Two blank values = uniqueness of PK violated.

The best solution is always to do an INSERT, and always provide a value for the key. Or, make sure the key field is an Autoinc integer, or that it has a default value.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[foxup]

Hmmmm, it's not working for me.

1st DBF (NAME=ONE.DBF) = 5 fields
FIELDNAMES
pk, aa, ab, ac, ad

2nd DBF (NAME=TWO.DBF) = 5 fields
FIELDNAMES
pk, aa, ab, ac, ad

I want to append all the fields of TWO.DBF except the PK for which I want the "autoincrement" to continue in ONE.DBF


I get uniqueness of PK field

Please let me know how to do it with a SELECT/INSERT command.


Thanks,
FOXUP

 

[DSummZZZ]

You may try a SCAN with SCATTER ... then APPEND BLANK, then a GATHER FIELDS EXCEPT ...


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[foxup]

In the help file it says:

If the target table uses autoincrementing, APPEND FROM fails if AUTOINCERROR is set to ON, unless the FIELDS option is used to omit the AUTOINC column. Setting AUTOINCERROR to OFF or turning off autoincrementing in the target table by using CURSORSETPROP( ) allows the APPEND FROM to succeed. The target table's autoincrementing field or fields are incremented according to the values specified, and the values in source table are not applied.

OK, so I did append fromm with field names and it still doesn't work
and I also change the CURSORSETPROP( ) foR the ONE.DBF & it still doesn't work.

Please help.

Thanks,
FOXUP

 

[TamarGranor]

Show us exactly the code you tried with the FIELDS clause.

Or (assuming you're in VFP 8 or later), use SQL commands instead:

INSERT INTO ONE (AA, AB, AC, AD) ;
   SELECT AA, AB, AC, AD ;
      FROM TWO

Tamar

 

[Olaf Doschke]

See the note in the help on AUTOINCERROR:

A cursor inherits the current setting of SET AUTOINCERROR when opened. Changing the value of SET AUTOINCERROR during a session does not change the setting for any cursors already opened. To change the setting of an open cursor, use the CURSORSETPROP( ) function.

So if you do SET AUTOINCERROR OFF before appending, the table still will have the default ON setting in effect.

But the error you get due to autoincerror still being on is "field ... is read only", not "uniqueness of index is violated". Take a peek at the index expression, if it's really the field you omit and/or if it's not another index, eg a candidate index being violated instead.

Clear
* a little simple error reporting:
On Error ? Message()," in line ",Lineno()
* makeing sure we have the default setting:
Set Autoincerror On 
* create a cursor with an autoinc field (problematic destination cursor):
Create Cursor curTest (iid i autoinc, cText C(20) Default "some text")
Append Blank && works, blank records means all default values including autoinc

* create an append cursor (source cursor)
Select * from curTest into cursor curAppend nofilter

* select destination
Select curTest

* Test 1
* Append from source, while autoincerror is on:
? "executing line", Lineno()
Append From Dbf("curAppend") && outputs "Field IID is read-only" (line 19)
Append From Dbf("curAppend") Fields cText && works

* Test 2 with AutoincError Off
Set Autoincerror Off
* Append from source again, while the session autoincerror is off:
? "executing line", Lineno()
Append From Dbf("curAppend") && still outputs "Field IID is read-only" (line 26)

* Test 3 - now set the cursor property:
? "AutoincError of session is",Set("AutoincError")
? "AutoincError of curTest is ",CursorGetProp("Autoincerror","curTest")
CursorSetProp("AutoincError",.F.)
? "AutoincError of curTest is ",CursorGetProp("Autoincerror","curTest")
Append From Dbf("curAppend") && works now without error

* result will have 3 records:
* 1. was done by Append Blank
* 2. was done by Append From Dbf("curAppend") Fields cText with Autoincerror On in curTest
* 3. was done by Append From Dbf("curAppend") with Autoincerror Off in curTest

Bye, Olaf.

 

[Mike Lewis]

Well, this looks like another of those threads where it's going to take a dozen messages just to find out exactly what the question is all about.

Foxup: Are you now saying you are doing APPEND FROM? (Please be clear about this; it wasn't until your third post that you hinted at that possibility?)

If so, is the PK field an Integer (Autoinc)?

If so, then you need to exclude it from the append. You can do that as follows:

SET FIELDS TO ALL EXCEPT <name of primary key field here>
APPEND FROM .... etc
SET FIELDS TO

If you are not doing APPEND FROM, the please state clearly exactly what you are trying to do.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike, what other APPEND than APPEND FROM is allowing a FIELD clause, of which foxup speaks in it's first post?

I agree it might take some time, but it also might turn out quite easy. What we know already is, that it's not an autoinc error. Omitting the autoinc field is sufficient to make APPEND FROM work. But it seem the index tag PK is done on another field.

I tend to put an x in front of all tags (besides legacy tags I don't change). This it's making much clearer that foxpro is referring to a tag name, and not necessarily the field name. Open your table in the table designer and look into the index tab to see what pk refers to, really.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

My first assumption was that he was doing an APPEND BLANK, and then replacing the individual fields. This was re-enforced by the fact that a unique key violation is a very common error in those circumstances.

What I was complaining about was the fact that we so often have to do a lot of work just to understand the problem. And when we ask a direct question to try to clarify it, we don't get a straight answer.

Never mind. Many of the regulars here have got more patience than I have.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

OK, Mike, but if he would do append blank, and then replacing the individual fields, he would have said REPLACING field by field, not APPENDING field by field.

Besides I think you're one of the most patient here.

But the phrase "field by field" that makes me think of a new problem. foxup, if you do APPEND FROM you create new records, and if you do multiple appends with each one field name you'll not add field by field to the current record, you'd add all the records multiple times, each time leaving all but one field with their default value.

Please post the code you use, not just the behaviour you see. Also table structure and indexes and their expression will help.

Bye, Olaf.

 

[foxup]

OK, so i tried Tamar's try and still same error.

here is the stru
Field Field Name Type Width Dec Index Collate Nulls Next Step
1 BILLING_NU Character 20 No
2 FROM_DATE Character 10 No
3 NUMBERBILL Character 20 No
4 USOC_SDESC Character 40 No
5 USOC Character 20 No
6 AMOUNT Character 20 No
7 QUANTITY Character 10 No
8 F_ORIGIN Character 1 No
9 NPK_SRC Integer 4 Asc Machine No
** Total ** 146


the second file has the exact same stru with same index type&name.

I tried this:

INSERT INTO source_1 (billing_nu, from_date,numberbill,usoc_sdesc,usoc,amount,quantity) ;
SELECT billing_nu, from_date,numberbill,usoc_sdesc,usoc,amount,quantity ;
FROM source_2

to see if it works and I get "Uniqueness of index pk is violated"

I tried:
APPEND FROM source_2
to see if it works and I get "Uniqueness of index pk is violated"

please help.

Thanks,
FOXUP

 

[Olaf Doschke]

That say you have one asceding index on the f_origin field. But that is not an integer, neither so is it an autoinc field. it doesn't even seem to have a default value, so it will default to be empty.
If that index is primary theere is no guessing how many records you can append or insert omitting that field: 1, the second will error with uniqueness of index violated.

If you have a pk field it either needs a default value computing a unique value or bein autoinc, which only is available for integer fields, not character.

Bye, Olaf.

 

[foxup]

My index is on npk_src

it's candidate

 

[foxup]

index name=PK
type=candidate
expression=npk_src
collate=Machine

The same for both DBF.'s.

 

[Olaf Doschke]

The index may also be on the npk_src field, your structure data comes over quite messy. but it doesn't say autoinc anywhere. Next and step is not filled for any column.

So the same applies: If that field has a primary index on it, all you can add is one record, which will default to 0, the second one will also have 0 and uniqueness is violated.

Bye, Olaf.

 

[foxup]

can't I append the fields from dbf2 (excluding the npk_src) to dbf1 ? that should be possible to do simply,

 

[Olaf Doschke]

You can't do so, if npk_src has no default value or autoinc creating a unique value, if omitted in insert or append. For the third time: If that field always is filled with the same value because there is no default value or autoinc, it will always be filled with the same value, and that can only work once. At the second new record you will have the value twice, which the candidate index doesn't allow.

the index just just checks for double, it doesn't create unique values. It just refuses a second record with the same value.

Bye, Olaf.

 

[Olaf Doschke]

To solve this: Open the table exclusive, go into the table designer and change the field type from integer to integer autoinc, and set the next value to be at least 1 more than the current max value.

And then you can both append or insert the whole dbf2 omitting the npk_src field.

If both tables are having the exact same structure inclucing the primary field, it's questionable that you want this. You would rather want a merge, and take the newer version of records with the same npk_src value instead of creating a new key value.

If you just append, you will end up with double records or two versions of the previously same record with new pk value.

Bye, Olaf.

 

[foxup]

Olaf!

"Open the table exclusive, go into the table designer and change the field type from integer to integer autoinc, and set the next value to be at least 1 more than the current max value.

And then you can both append or insert the whole dbf2 omitting the npk_src field. "

WORKS PERFECTLY!!!!!

MANY MANY THANKS!!!

Have a great long weekend!!!

FOXUP!