#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    42
    Rep Power
    1

    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

    Code:
    CREATE TABLE usergroup ( 
        id   INTEGER        PRIMARY KEY AUTOINCREMENT
                            NOT NULL
                            COLLATE 'RTRIM',
        name VARCHAR( 25 ) 
    );
    Problem is that when delete any record in between it never arranges / updates id following deleted record accordingly.

    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 ?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    352
    Rep Power
    7
    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:

    ParentTable:
    ParentID: autoincrement
    ParentName: Varchar(XX)
    ...

    ChildTable
    ChildID: autoIncrement
    ParentID: References ParentTable(ParentID)
    ChildName: VarChar(XX)
    ...

    Possible records
    ParentTable
    1 John Smith
    2 Bill Jones
    7 Al Johnson

    ChildTable
    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

    • ninadgac agrees

IMN logo majestic logo threadwatch logo seochat tools logo