Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    3

    Talking Unanswered: duplicate data violating clustered index ?

    sybase 11.9.2

    i have a table with a clustered index that nevertheless has allowed in a duplicate.

    how on earth can this happen?

    I've tested all of the combos i can think of on my v 12 server to try and recreate, but sybase blocks me from inserting (or updating) to a duplicate that violates the key no matter what order the index / data creation occurs.

    any ideas??? thanks in advance.

    jess.

  2. #2
    Join Date
    Mar 2003
    Posts
    3

    Re: duplicate data violating clustered index ?

    if i create a clustered, composite index on fields 1 & 2 from a 3 field table i can insert duplicates of fields 1 & 2 provided 3 is different. if i try to insert a duplicate of all 3 fields (including the 3rd, non-indexed field) I get dup errors:

    Attempt to insert duplicate row in table 'testuser' with index 'index_one' in
    database 'data_sup2'. Could drop and recreate index with ignore duprow or allow
    duprow.
    Command has been aborted.

    i would expect not to be able to create a duplicate of fields 1 & 2 regardless of what i'm doing with field 3 because a clustered index is unique by default. is there something wrong with my assumptions?


    thank u

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    A clustered index does not enforce uniqueness unless you specify the keyword UNIQUE.

    CREATE CLUSTERED INDEX bob ON foo( bar )
    is not the same as
    CREATE UNIQUE CLUSTERED INDEX bob on foo( bar )

    You may be thinking of a PRIMARY KEY constraint in a CREATE TABLE statement. In this example:
    CREATE TABLE foo ( bar PRIMARY KEY )

    ASE will create a UNIQUE, CLUSTERED index on bar.
    Thanks,

    Matt

  4. #4
    Join Date
    Jan 2004
    Posts
    19
    Jess,
    By default clustered index is not unique till you explicitly specify it(as already mentioned by Matt).But the problem which you are facing for the table testuser is because in a nonunique clustered index(which is default and which i guess you have created) sybase will allow duplicate keys but will not allow duplicate rows(unless you have specified allow_dup_row option).
    Cheers,
    Andy

  5. #5
    Join Date
    Feb 2004
    Location
    presently CS.USA
    Posts
    1

    Thumbs up Re: duplicate data violating clustered index ?

    Clustered Index will physically sort the data.
    If you want uniqueness of data with Clustered Index then you should create Clustered Index with unique option explicitly.
    Otherwise it will accept duplicate records and sort then in order.

    Other option to follow the normalization standard i.e., use of primary key (which in turn doest allow duplicate records)

    I hope its clear to you.

    P.G.Vinod
    Sybase DBA


    Originally posted by jessicab
    sybase 11.9.2

    i have a table with a clustered index that nevertheless has allowed in a duplicate.

    how on earth can this happen?

    I've tested all of the combos i can think of on my v 12 server to try and recreate, but sybase blocks me from inserting (or updating) to a duplicate that violates the key no matter what order the index / data creation occurs.

    any ideas??? thanks in advance.

    jess.

  6. #6
    Join Date
    May 2006
    Posts
    1

    duplicates not allowed in nonunique clustered index

    Hi ,

    As you told i am not able to insert duplicate rows in a nonunique clsutered index my version is 15

    Please help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •