j*****n
发帖数: 1781 | 1 A department was looking for my support for an error they received while
they were loading a set of data into a table.
The DB is SS2K5.
The error message shows "Violation of PRIMARY KEY constraint 'PK_index'.
Cannot insert duplicate key into object 'dbo.Table'".
Sounds straight forward? No...
I then load the set of data into a temp table and do the comparison, no
duplicates found!
Well, this is the first time I met this problem and a little bit later I
had
my best guess to give a solution, and it worked...
Now 2 Baozi for each one who had the similar problem and what was your
solution.
Or give me your best guess. :D |
g***l
发帖数: 18555 | 2 三种情况
1. SET里面可能有DUPLICATE KEY，比如你INSERT这个
PK_ID, NAME
1 NAME1
1 NAME2
2. 如果SET里没有DUPLICATE KEY，就是TABLE已经有PK_ID 1了，不让你INSERT
3.另外PK可能是INDENTITY，你的PK_ID，他们已经用过了，虽然不存在，但你的PK_ID
太小，不能用，只能往上走
按你说的3的可能比较大 |
j*****n
发帖数: 1781 | 3 none of these 3... as I said, there are no duplicates...
as such, I won't say it is weird.
dude, continue squeezing your brain see what else you could imaging.
:-)
ID
【在 g***l 的大作中提到】
: 三种情况
: 1. SET里面可能有DUPLICATE KEY，比如你INSERT这个
: PK_ID, NAME
: 1 NAME1
: 1 NAME2
: 2. 如果SET里没有DUPLICATE KEY，就是TABLE已经有PK_ID 1了，不让你INSERT
: 3.另外PK可能是INDENTITY，你的PK_ID，他们已经用过了，虽然不存在，但你的PK_ID
: 太小，不能用，只能往上走
: 按你说的3的可能比较大
|
g***l
发帖数: 18555 | 4 太深奥了，给讲讲吧
【在 j*****n 的大作中提到】
: none of these 3... as I said, there are no duplicates...
: as such, I won't say it is weird.
: dude, continue squeezing your brain see what else you could imaging.
: :-)
:
: ID
|
j*****n
发帖数: 1781 | 5 dude, the solution is simple but this scenario is rare. just want to see if
anyone else met this.
hint, there is nothing wrong with the data itself.
【在 g***l 的大作中提到】
: 太深奥了，给讲讲吧
|
i****a
发帖数: 36252 | 6 Needed index rebuild?
A wild guess.
if
[发表自未名空间手机版 - m.mitbbs.com]
【在 j*****n 的大作中提到】
: dude, the solution is simple but this scenario is rare. just want to see if
: anyone else met this.
: hint, there is nothing wrong with the data itself.
|
v*****r
发帖数: 1119 | 7 interesting, I guess ...
The loading is trying to load into a table owned by a schema other than dbo,
while dbo schema happens to have a table with the same name as the target
table??? In that case, dropping the offending table in dbo schema is the
fix???
【在 j*****n 的大作中提到】
: A department was looking for my support for an error they received while
: they were loading a set of data into a table.
: The DB is SS2K5.
: The error message shows "Violation of PRIMARY KEY constraint 'PK_index'.
: Cannot insert duplicate key into object 'dbo.Table'".
: Sounds straight forward? No...
: I then load the set of data into a temp table and do the comparison, no
: duplicates found!
: Well, this is the first time I met this problem and a little bit later I
: had
|
g***l
发帖数: 18555 | 8 你插错TABLE啦或者COLUMN没对好？LOL
if
【在 j*****n 的大作中提到】
: dude, the solution is simple but this scenario is rare. just want to see if
: anyone else met this.
: hint, there is nothing wrong with the data itself.
|
j*****n
发帖数: 1781 | 9 nop, there is only dbo in the DB, no other schema.
dbo,
【在 v*****r 的大作中提到】
: interesting, I guess ...
: The loading is trying to load into a table owned by a schema other than dbo,
: while dbo schema happens to have a table with the same name as the target
: table??? In that case, dropping the offending table in dbo schema is the
: fix???
|
j*****n
发帖数: 1781 | 10 man, the process was built on 2006...
【在 g***l 的大作中提到】
: 你插错TABLE啦或者COLUMN没对好？LOL
:
: if
|
|
|
B*****g
发帖数: 34098 | 11 把表删了重建
【在 j*****n 的大作中提到】
: A department was looking for my support for an error they received while
: they were loading a set of data into a table.
: The DB is SS2K5.
: The error message shows "Violation of PRIMARY KEY constraint 'PK_index'.
: Cannot insert duplicate key into object 'dbo.Table'".
: Sounds straight forward? No...
: I then load the set of data into a temp table and do the comparison, no
: duplicates found!
: Well, this is the first time I met this problem and a little bit later I
: had
|
j*****n
发帖数: 1781 | 12 你这是捣乱，该你给包子
【在 B*****g 的大作中提到】
: 把表删了重建
|
a9
发帖数: 21638 | 13 你在故弄玄虚，还是你发吧。
【在 j*****n 的大作中提到】
: 你这是捣乱，该你给包子
|
B*****g
发帖数: 34098 | 14 这怎么是捣乱，大多数wierd问题都可以通过从头来解决
【在 j*****n 的大作中提到】
: 你这是捣乱，该你给包子
|
j*****n
发帖数: 1781 | 15 这可是 production...
【在 B*****g 的大作中提到】
: 这怎么是捣乱，大多数wierd问题都可以通过从头来解决
|
c*****d
发帖数: 6045 | 16 有primary key的col是通过unique index来enforce pk的
我觉着应该是table上数据已经删除，但是对应的index存在，很大可能是index block
corrupt
我会删除这个pk然后重新create pk，然后load data |
R*********r
发帖数: 225 | 17 PK需要被插入还是插入之后自动产生的identity？
【在 j*****n 的大作中提到】
: A department was looking for my support for an error they received while
: they were loading a set of data into a table.
: The DB is SS2K5.
: The error message shows "Violation of PRIMARY KEY constraint 'PK_index'.
: Cannot insert duplicate key into object 'dbo.Table'".
: Sounds straight forward? No...
: I then load the set of data into a temp table and do the comparison, no
: duplicates found!
: Well, this is the first time I met this problem and a little bit later I
: had
|
B*****g
发帖数: 34098 | 18 then delete and recreate PK, hoho
【在 j*****n 的大作中提到】
: 这可是 production...
|
j*****n
发帖数: 1781 | 19 ok, the fact is...
corrupted clustered PK index. a guy in CINAOUG had the same problem before.
the fix is rebuilding the index.
iMaJia and coolbid won Baozi, hehe. |
B*****g
发帖数: 34098 | 20 why no baozi for me? hehe
【在 j*****n 的大作中提到】
: ok, the fact is...
: corrupted clustered PK index. a guy in CINAOUG had the same problem before.
: the fix is rebuilding the index.
: iMaJia and coolbid won Baozi, hehe.
|
|
|
y****w
发帖数: 3747 | 21 看这贴又长见识了．
if
【在 j*****n 的大作中提到】
: dude, the solution is simple but this scenario is rare. just want to see if
: anyone else met this.
: hint, there is nothing wrong with the data itself.
|
i****a
发帖数: 36252 | 22 wow thank.
I should go buy lottery this week
Now pls tell us how did you find the solutulion
【在 j*****n 的大作中提到】
: ok, the fact is...
: corrupted clustered PK index. a guy in CINAOUG had the same problem before.
: the fix is rebuilding the index.
: iMaJia and coolbid won Baozi, hehe.
|
j*****n
发帖数: 1781 | 23 as said, there is nothing wrong with the data and sql server reported the PK
violation. the only thing i can think of is something wrong with the index.
even i doubted since this is the clustered index only with the PK, though
the PK is combination of 2 columns.
so i then went to DBA and ask for index rebuilt... after we saw the problem
was solved then, the DBA also shocked by this.
interesting is we also checked the maintenance plan and see index rebuilt
once a month and update statistics once a week. this table only get
populated few thousands records a month...
would be possible that something wrong with the disk that caused this index
corrupt?
【在 i****a 的大作中提到】
: wow thank.
: I should go buy lottery this week
: Now pls tell us how did you find the solutulion
|
c*****d
发帖数: 6045 | 24 多谢包子
【在 j*****n 的大作中提到】
: ok, the fact is...
: corrupted clustered PK index. a guy in CINAOUG had the same problem before.
: the fix is rebuilding the index.
: iMaJia and coolbid won Baozi, hehe.
|
