Silly Question About Indexes
-
Hello,
MSSQL 7 w/SP2
Would there be a reason why a helpdesk application (HE&%) would require a min of 1 unique index per table for it to be able to update or delete? There are no keys or constraints (ie: PK; UQ; FK) for these tables.
During a database migration, all objects/data were copied, minus indexes. Users were allowed to add new records, but couldn't modify or delete existing ... getting READ ONLY Cursor.
Is this an app issue or something in SQL/database design?
Many thanks. Jeff
-
For Deletes and Updates (which are actually deletes and inserts) the table will require a unique key to determine which record to delete.
--------------------
Colt 45 - the original point and click interface
-
I don't think the unique index has a lot to do with it...unless, you are updating and deleting through a view, or an updateable cursor. Are you using updateable views or updating through a cursor?
"Keep Your Stick On the Ice" ..Red Green -
Red Green?? Another fellow Canadian !!!
Can't really reply to JEFF W ... it's a proprietary app ... from reviewing the existing View definitions, I believe it is directly updating the tables ... but here is the actual error message the application administrator was getting while trying to update or delete a record via the app form ...
"The cursor is READ ONLY.
Last SQL String:
update Profile set CustID=?,ModBy=?,ModDate=?,ModTime=? where Current of SQL_CUR01D3D2F8
Last ODBC Command:
Function: SQLExecute()"
The problem was resolved once I captured and migrated the Unique Indexes from the old environment over and into the new environment (and all of the tables had a minimum of one, and some were clustered).
I'm puzzled as to why a table would need a Unique Index in able to perform an UPDATE or DELETE function?
Many thanks. Jeff
-
It's nothing to do with views.
Try this example,
Run the following in Query Analyzer,
create table tmp (a int, b int, c int)
insert into tmp values ( 1, 2, 3)
insert into tmp values ( 1, 2, 3)
insert into tmp values ( 1, 2, 3)
insert into tmp values ( 1, 2, 3)
insert into tmp values ( 1, 2, 3)
Now using Enterprise Manager,
open the table.
highlight and delete one of the rows.
You will get a message that says,
"Key column information is insufficient or incorrect. Too many rows affected by update."
Now add an identity field to the table and create a PK, Unique constraint, or unique index on the identity field and you can delete rows without problems.
--------------------
Colt 45 - the original point and click interface -
Without an index, the table has no way to convey information from your client to the server (what you see is records thrown to your screen from a server far away, disconnected from your screen). Now, when you delete a record in your client window, a message has to go to the server with a pointer telling the sever to delete a row. If there is no index on the table, there is no pointer for each record. The only way for the server to know what you wish to delete is to compare ALL fields of deleted record with ALL fields of ALL records. Now, are you not happy that it demands you make an index before undergoing the process of comparing ALL fields and ALL records to the record you send to server to delete (the client will send all fields with no pointer, as there is no pointer in the absence of an index). Same explanation for update. This problem can occur also when more than one record has same data.
A very smart app coder who had this problem, did not know the solution..went about deleting that record by retrieving ALL records from DB, deleting them all and then inserting them all EXCEPT the one he wished to delete. Imagine that? he he. It happens. That's life (quoting Donald Rumsfeld).
Anup
Semiprecious.com
HaldiramsUSA.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply