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 |
|
yltan
Starting Member
5 Posts |
 Posted - 2004-06-21 : 02:34:56
|
| Hi all,I've got a little problem.I did a select on the table where salesorder = '700001'Then I deleted it.I check it was deleted then I inserted this record again using ADO.I get a primary key violation.Do I need to re-index? If I delete a record in the table, aren't the indexes automatically updated?Best regards,Lee |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-21 : 07:59:01
|
| Do I need to re-index?No>> If I delete a record in the table, aren't the indexes automatically updated?YesCheck that the PK is on salesorder. Check that the record has been deleted.Use the profiler to see what the app is really doing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-21 : 13:38:27
|
| Did you commit the delete before trying the insert?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
yltan
Starting Member
5 Posts |
Posted - 2004-06-21 : 22:29:57
|
| Thank you both for your replies.Yes, I did commit the delete.And I even checked that the record was deleted with a select statement.The Primary key is SalesOrder. However, there are a few indexes. The other is primary key as SalesOrder and Status.I think it is the problem of the indexes. It is a poorly designed table!!Best regards,Lee |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-21 : 22:42:29
|
| What version of SQL Server is this? You should be able to insert the record after the delete is commited.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-22 : 05:27:22
|
| You can only have one primary key soThe Primary key is SalesOrder. However, there are a few indexes. The other is primary key as SalesOrder and Status.is not possible.I suspect you are marking the row as deleted rather than deleting so SalesOrder should not be a unique constraint.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Primary key violation after Deletion