Deleting redundant data records 1

[JefB]

For reasons that I do not understand, one of my SQL 7 data tables allowed redundant records to be entered but will not allow me to either delete or modify them.
If I try to delete one of the records I get the following message"
"Key column information is insufficient or incorrect. Too many rows were affected by update"
The table is standalone without any keys, indexes or relationships to other tables or views. (Because of the redundant data, I cannot add a key or set a UNIQUE index)
Any hints on what I am missing would be appreciated.
Jefb

 

[JohnAtSporgit]

One way skin this DB cat is to add an identity column to the table. You may have to create a new table with the same column names, (plus the new ID one,) then copy the table with import/export. I seems you can add the Identity col and it will handle it though. Now you have a piece of data that is unique, and you can delete a specific row.

What mechanism did you use to insure no duplicate data? there is nothing inherent to prevent it.

 

[JefB]

Thanks for the reply; I'll try adding the Identity field when I get back to the office.
The problem arose because we are using a product to build scripts (or dialogs) within MSFrontPage and I did not have the "Update Db" setting turned on. This meant that if a user navigated to the "SaveData" screen (where the data is added to the SQL 7 database) and then hit the "Back" button, the same record was saved a second time. The "Update Db" setting updates existing records instead of appending them.
I won't make that mistake again!
I was hoping not to have to go the "export/import" route, but that may be my only option if adding the field does not work.
I still do not understand why the table allowed the insert of a record that it then will not allow me to delete? There must be a setting that I am missing, but then, I am not a SQL7 DBA and we do not have one on staff at this time.

 

[JefB]

JohnAtSporgit,

I was able to add the identity and that did the trick! I have added identity fields to all my tables (just in case).

Thanks for the help.

JefB

 

[fluteplr]

In case you did not learn from your error, the other rule is NEVER create a table without a primary key.

 

[JefB]

Lesson learned!