I have a datatable with the columns ID,Value,Date,Cumulative,UniqueID where Unique ID is my primary key. UniqueID is an Identity key which auto increments with every new entry.My question is 2 parts
a) Sometimes the data is inserted multiple times so does the primary key take the number of steps or the number of rows added to table to increment the Identity Column value (UniqueID) ???
b)Also there is a possibility of gap in the UniqueID because data deletion.How do I go about checking whether there is a gap in the table and how to resolve it.Do I have to delete the table and startover again when there is a gap???
Came across this page but could not figure out whether to do this when the table is created or can it be done after the table is created.
Thanx in advance
Edited 11-Sep-11 13:15pm
Unique key does what it should do: it guarantees its uniqueness, so it can be safely used for referencing of object from other tables. Every technique based an any assumption of the values of the key (and in particular, on the assumption of consecutive values of the key) is wrong and should never be used.—SA
The assumption about the consecutive values would be apparently wrong. Assume you delete few rows, so some unique values disappears. You could possibly expect that the values for unique ID can be later used again and fill the gaps (but it cannot be guaranteed), but the gap is unavoidable before you add any more rows. An attempt to rely on consecutive values will make your data model inconsistent, and the attempt to delete the whole table in hope to restore the same data later will destroy logical data integrity. You want to remove and re-create some rows only "technically", but "logically" restore the objects later, just shifting unique IDs for the restored object. How are you going to restore references from other table for those objects? Even if you can do it, this operation is not transactional: after you remove some rows, some other user might add some; and what you going to do with that? No, it cannot work. You simply should not use any technique based on any assumption of the key values.
Just to add to what SAKryukov said,
Sequentially incremented without "holes" numbers are usually a business requirement and I implement them in the application layer, and not in the database layer.
This is usually done by a renumbering a "document number" column.
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)