Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ninel
Posting Yak Master
141 Posts |
 Posted - 2006-08-16 : 12:06:52
|
| I have table "List"[code] CREATE TABLE [list] ( [listid] [bigint] IDENTITY (1, 1) NOT NULL , [enterpriselistid] [bigint] NULL , [countrycode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [areacode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [number] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [disposition] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [callstart] [datetime] NULL , [recentattempts] [int] NULL , [totalattempts] [int] NULL , [timezone] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [lockstatus] [tinyint] NULL , [callback] [datetime] NULL , [enterpriseagentid] [int] NULL , [usercode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_List] PRIMARY KEY NONCLUSTERED ( [listid] ) ON [PRIMARY] ) ON [PRIMARY]GO [\code]This table has the following index: Unique clustered index IX_List_enterpriselistid on column name enterpriselistid.When I try inserting values into the enterpriselistid field I receive the following error: "Cannot insert duplicate key row in object 'list' with unique index 'IX_list_enterpriselistid'. The statement has been terminated."What does this mean? Does it mean that enterpriselistid cannot have the same values as listid?Thanks,Ninel |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-16 : 12:25:46
|
| >> When I try inserting values into the enterpriselistid Post the query used to Insert Data.The issue seems to be not related to Indexing. It is something to do with duplicate values in a columnSrinika |
 |
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-08-16 : 14:37:12
|
Ninel,I disagree with Srinika - I think it IS related to indexing. You've shown the reason for the error here:quote:
Unique clustered index IX_List_enterpriselistid on column name enterpriselistid.
Note the word "Unique", and that the index is on column name "enterpriselistid"? That means that enterpriselistid must be unique within the index. So yes, it means that enterpriselistid must be unique; you can't have two enterpriselistid's that are the same. It does NOT mean that enterpriselistid cannot have the same values as listid.Ken |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-16 : 15:41:07
|
| My understanding is that, Indexing done to query fasterUnique is a constraint (because of which u cannot put duplicate values in to that particular column)For eg. u can have a field indexed and still can have non-unique (duplicate) values in it.The question raiser is confused (by his title for the question it seems), that the issue is because of Indexing.Also in the middle of his question he is talking about 2 fields (which are not in anyway related).As I requested, if he posts data, v can find what he going wrong. I feel that he is trying to Insert duplicate values to a column having "Unique" constraint. Srinika |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-16 : 15:46:20
|
| Srinika, unique constraints are enforced by a unique index. So yes the problem is related to the index. ninel, the error means you are trying to insert a enterpriselistid that already exists in the enterpriselistid column.Tara Kizer |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-01 : 21:49:02
|
quote:
Srinika, unique constraints are enforced by a unique index. So yes the problem is related to the index. ninel, the error means you are trying to insert a enterpriselistid that already exists in the enterpriselistid column.
Hi TkizerSo eventhough it mentioned the UNIQUE INDEX issue NOT UNIQUE CONTRAINT, it's related on duplicate record on of the column?!?!In fact, I replicate this and it is associated with duplicate record. Mmm it's interesting. |
|
|
|
madhuotp
Yak Posting Veteran
78 Posts |
Posted - 2006-11-02 : 08:46:08
|
| Hi All,This table has a PrimaryKey and the index on PK is NonClustered. And you have another index Unique clustered index IX_List_enterpriselistid on column name enterpriselistid. It means enterpriselistid can have one null value and no duplicate values. So what you are inserting is already there in this column. Generally, when we want to maintain the Uniqueness in a column(s) other than PK we create Unique Index on that column(s). So Precisely You are trying to insert duplicate value into enterpriselistid.Madhu |
|
|
|
|
|
|
|
|
Index Question