Results 1 to 4 of 4

Thread: Unique Index on Lianja Table,Connectivity error: Cannot replace as key already exists

  1. #1
    Member
    Join Date
    Feb 2012
    Posts
    43

    Unique Index on Lianja Table,Connectivity error: Cannot replace as key already exists

    I use the following code and get

    Connectivity error: Cannot replace as key already exists

    Can you explain why am I getting this error ?

    ================================
    Code to produce the error
    ================================
    lnconn = SQLCONNECT("Lianja_Southwind")

    ? SQLEXEC(lnconn,"create table invpay (lineno N(2), locId N(3), invno N(10))")

    ? SQLEXEC(lnconn,"select * from invpay")

    ? SQLEXEC(lnconn,"create unique index pk_lineno_locID_invno on invpay (lineno,locId,invno)")

    ? SQLEXEC(lnconn,"create index pk_locID_invno on invpay (locId, invno)")

    ? SQLEXEC(lnconn,"insert into invpay values (1, 1,11500008)")

    ? SQLEXEC(lnconn,"select * from invpay")

    ? SQLEXEC(lnconn,"insert into invpay values (2, 1,11500008)")

    ? SQLEXEC(lnconn,"select * from invpay")
    BROWSE FONT 'ARIAL',12

    ? SQLEXEC(lnconn,"insert into invpay values (1, 1,11500009)")

    Connectivity error: Cannot replace as key already exists. (record not inserted)

    ? SQLEXEC(lnconn,"insert into invpay values (1, 1,11500010)")
    ? SQLEXEC(lnconn,"insert into invpay values (2, 1,11500010)")
    ? SQLEXEC(lnconn,"insert into invpay values (1, 1,11500011)")

    Connectivity error: Cannot replace as key already exists. ( record not inserted).

    1) So whenever I inserted two or more lines of a same invoice then the next new invoice record gives me error. What am I doing wrong?

    2) How can I print/fetch, tag() or key() functions from ODBC ?

    Regards
    Naeem

  2. #2
    Member
    Join Date
    Feb 2012
    Posts
    43
    Hi All,

    This the reply that I got against my ticket. Lianja index sum numeric fields to create unique index.

    =============================================
    Hi Naeem,

    The command 'create unique index pk_lineno_locID_invno on invpay (lineno,locId,invno)'

    creates the index on the key 'LINENO+LOCID+INVNO (Unique)', the sum of lineno,locId,invno

    so, 'insert into invpay values (1, 1,11500009)' would result in the same key as 'insert into invpay values (2, 1,11500008)'

    You need to use STR() (or ETOS(), STRZERO()/STR_ZERO()) in your index creation, e.g. create unique index pk_lineno_locID_invno on invpay (STR(lineno),STR(locId),STR(invno))

    ================================================== =========

    Is this good or bad ? Any comment about it.

    regards
    Naem

  3. #3
    Senior Member
    Join Date
    Feb 2012
    Location
    Rome - Italy
    Posts
    1,881
    Hi Naem,
    Lianja as VFP, does not have the management of composite indices as other DB.
    If you have numeric indices compounds, the only solution is that indicated by the staff.
    In my experience, you have to pay attention to the lengths of the fields.
    For example:
    LINENO = N (3)
    LOCID = N (3)
    INVNO = N (10)

    LINENO = 10
    LOCID = 5
    INVNO = 123

    if you write str (lineno, 3) + str (LOCID, 3) + str (invno, 10), the index is " 10 5 123"

    If you do a "seek", you need to remember the syntax of your index
    If you change a lenght of a any of your index field, you need to verify the index syntax.

    I hope I have helped you
    Fabio

  4. #4
    Member
    Join Date
    Feb 2012
    Posts
    43
    Hi Fabio,

    Thanks for sharing.

    Regards
    Naeem

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us