[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.

[jessicab]

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

[MattR]

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.

[Andy2004]

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

[pg_vinod]

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.