I have a table with composite primary key of 7 fields, but table is allowing duplicate entries with primary key. Later I noticed it also has Unique clustered index with 10 fields including 7 of primary key. Is that the reason system allowing to insert duplicate primary key data?
If so, I am not able to think of reason creating Unique Index with field additional fields not much in use for searching data except limit of fields in composite key. I tried to look for answer but didn't find result for limitation. Can someone please help. I am using sybase.
Peformance-wise, clustered key, set correctly (aka does not include all fields you deem unique), gives you the most advantage. 7-column PK is a strange construct; if you need to guard uniqueness, I would go for a combination of a cluster + unique constraint.