T-SQL Error in Enterprise Manager 1

[phzero]

Hi All,

I'm using SQL Server 7.0 I'm running the following simple query to get results. I have in the top the SQL View and at the bottom the Results Set. When I try editing any of the columns in the grid, I get the following error: "Key column information is insufficient or incorrect. Too many rows were affected by update." How can this be if I'm only changing one measely field, any field for that matter. It only happens in this table. Any replies will be appreciated.
My Query: "select * from ArchiveInfo order by refnum"

 

[grahams]

What's happening is that SQL Server is unable to tell which row you're trying to update (hence the error message). Without having the structure of the table I can't tell exactly why this is, but for example this is exactly the error message you'd expect to get when trying to update a table that has no primary key set. If this is the case, set a primary key on the table and your problems should be solved. Otherwise please post the table structure and I'll have a look.

HTH,

Graham

 

[phzero]

Hi there,

I'm afraid you're right, I have no PK set. I have a 63-field table and I have tried to set a PK, but none of my field are unique. Am I Stuffed? Can I add an INT field and run an update query to sequentially increment this new field? Then again, how would I increment the value during the update?

 

[grahams]

Ok, are you able to identify a compound key in your data? A compound key (and forgive me if you already know this) is two or more columns that taken together can uniquely identify a row, e.g. an item number and an order number could be used to identify a row in an orderitems table, where one or the other alone could not.

If you can't do this, I think you could change the design of the table adding an identity column and making that the PK, even if only to allow you to render some more permanent fix. I seem to remember doing this before to get myself out of a similar hole, but my memory is notoriously fallible . Try it - if it doesn't work, get back to me. The compound key solution is preferable though, and more correct.

Good luck, and let me know how it goes.

Graham

 

[phzero]

Thanks grahams, IT WORKED!!! I have not been using SQL Server for too long, but of late, I have been making it a point of spending much more time in the design phase of my tables and their relations. Just yesterday I ran into another problem, also because of not having a PK on my table in which a few other people on this forum helped me. Thanks a stack for the support, I really do appreciate it.
Have a good one. Cheers.

 

[grahams]

You're very welcome, glad you're sorted. As a general point of interest, your comment that:

"...of late, I have been making it a point of spending much more time in the design phase of my tables and their relations"

is a very important one. The more time spent in getting the initial design as correct as possible, the better - and the fewer problems you'll run into later on. You always need to have a way of uniquely identifying a row, whether by using a PK or a unique constraint. Maybe spend some time reading up on relational DB design theory - it'll repay you in spades. Having said that, mind, lessons learned this way tend not to be forgotten!

Regards,

Graham

 

[phzero]

Well said, grahams, well said. I've taken your advice on the relational DB design theory and I'll be looking forward to my payment. Thanks again. Have a great day, I know I will.