January 17th, 2014, 02:49 PM
How to arrange primary key column column at SQLite Database?
I am using SQLite database with Delphi for developing an application. At Database table there is one primary key column.
There are 2 columns in a table
1)id :- Primary Key
2) name :- varchar(25)
Table DDL is as follows
Problem is that when delete any record in between it never arranges / updates id following deleted record accordingly.
CREATE TABLE usergroup (
id INTEGER PRIMARY KEY AUTOINCREMENT
name VARCHAR( 25 )
i.e Suppose there are id's like 1,2,3,4,5,6,7,8,9...etc
If I deleted record with id's 5 & 6 in between then I want id 7,8,9 to be upgrade as 5,6,7 I dont want gap in between. Is it possible ?
January 17th, 2014, 04:30 PM
This is NOT the behavior you'd want your database to do. The primary key is used for more than just a "counter" in your table. One such purpose is to reference a row in a parent table. As an example, if you are creating a database to track parents and their children, then you'd want to design your db as:
ParentID: References ParentTable(ParentID)
1 John Smith
2 Bill Jones
7 Al Johnson
1 1 Will Smith
2 1 Bob Smith
3 7 Dave Johnson
obviously a simplified design, but the point is that you have a reference to the parentID in the ChildTable. In the example, John Smith has 2 Child Records associated to him, Bill Jones has 0 Children and Al Johnson has 1. If you change the ParentID of Al Johnson to 3, (to have all your ID's sequential as you requested) then what's to happen to Dave Johnson in the Child Table? The point is if you were to change the ParentID in the ParentTable, then all references to that ParentID in the ChildTable, would no longer be valid (you've in essence disconnected the parent and child records from each other - Term to google is "referential integrity"). Notice that is different than changing the ParentName in ParentTable. The reference is still valid, but just the name changed.
You want this type of design, because any change to the Parent record only has to be made in the ParentTable (you hold the information once, in the correct table - you wouldn't want to hold any parent information in the child table, except the ID, nor would you want to hold any child information in the parent table).
Bottom line is that there are "Normalization" Rules to creating a Database, if you are not aware of them, then I can guarantee you are creating very inefficient databases. Again, google Database Normalization Rules - While there are more than 3 Normal Forms, it is standard to AT LEAST comply with 3rd Normal Form in any DB design.
Comments on this post