June 8, 2004 at 11:11 pm
Hi All.. Following is the description of the problem. Any help would be appreciated.
Table A has a primary key and a clustered index on one of the fields"Document No". Table B refereces Document No of Table A with a foreign key.
When you do an index search for Table A, in other words use the index field in the where clause, it returns a completely different row. For example: Where Document No = 1 returns a row with Document No = 2. When you do a table scan, there is no row with Document No = 1. Now this is a clear case of wrong index data. There is data for DocumentNo = 1 in the child table though. So I try reindexing on Table A, and the index data is corrected. Now when I do "Where Document No = 1" no row is returned, which is correct but the row in child table still exists to be there even with the Foreign Key constraint.
Clearly the data is lost from Table A. How to retrieve it back is my question. Please help.
June 9, 2004 at 7:48 am
The only way to recover lost data is to restore from a backup.
However, you may not have lost data. What is your actual table structure?
On table A, there seems to be DocumentID as a primary key, correct? How is that number created - is it an IDENTITY that is automatically created?
On table B, I assume there is also a DocumentID that is a Foreign Key, correct? How is that number created - is it also an IDENTITY?
IDENTITY's are created when data is inserted to the table. If the transaction is rolled back, that number is NOT reused. If the data is deleted, the number is not reused. Therefore you can have 'missing' IDENTITYs. Normally, a trigger or some other method is used to 'copy' that IDENTITY to the child table. But you might actually be creating a new IDENTITIY in the child table. If that is happening, that would cause you to have a DocumentID of 1 in the child table when there isn't one in the parent table.
Also, do you have a Primary/Foreign key relationship? If not, then when a record is deleted from the parent table, it won't delete the child record (unless you have a trigger to validate the transaction).
-SQLBill
June 10, 2004 at 4:40 am
you can also Restore your full backup to any New DB and copy the deleted columns from that database and paste it in your Working DB
i hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply