Resolved how to implement reset identity seed after deleting records in a CRUD system

percival.09b

Member
Joined
Jul 19, 2022
Messages
15
Location
Philippines
Programming Experience
Beginner
Hello, I have here a simple CRUD system connected to my Postgres database. I noticed that every time I inserted a new record it will assign a new ID number next to the previous one, then after I deleted that record the ID number is no longer usable, when you add a new record it will assign the next ID no. not the previous one that was deleted. I have very little knowledge of programming, then I found a solution but don't know how to implement this in my program. Here is the solution

Source code:
Form1.cs:
using System;
using Npgsql;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CRUDSYSTEM_DB
{
    public partial class Form1 : Form
    {
        private string id = "";
        private int intRow = 0;
        public Form1()
        {
            InitializeComponent();
            resetMe();
        }
        private void resetMe()
        {

            this.id = string.Empty;

            firstNameTextBox.Text = "";
            lastNameTextBox.Text = "";

            if (genderComboBox.Items.Count > 0)
            {
                genderComboBox.SelectedIndex = 0;
            }

            updateButton.Text = "Update ()";
            deleteButton.Text = "Delete ()";

            keywordTextBox.Clear();

            if (keywordTextBox.CanSelect)
            {
                keywordTextBox.Select();
            }

        }
        private void Form1_Load(object sender, EventArgs e)
        {
            loadData("", dgv1);
        }
        private void loadData(string keyword, DataGridView dgv1)
        {

            CRUD.sql = "SELECT id, firstname, lastname, CONCAT(firstname, ' ', lastname) AS fullname, gender FROM tb_crudsystem " +
                       "WHERE CONCAT(CAST(id as varchar), ' ', firstname, ' ', lastname) LIKE @keyword::varchar " +
                       "OR TRIM(gender) LIKE @keyword::varchar ORDER BY id ASC";

            string strKeyword = string.Format("%{0}%", keyword);

            CRUD.cmd = new NpgsqlCommand(CRUD.sql, CRUD.con);
            CRUD.cmd.Parameters.Clear();
            CRUD.cmd.Parameters.AddWithValue("keyword", strKeyword);

            DataTable dt = CRUD.PerformCRUD(CRUD.cmd);

            if (dt.Rows.Count > 0)
            {
                intRow = Convert.ToInt32(dt.Rows.Count.ToString());
            }
            else
            {
                intRow = 0;
            }

            toolStripStatusLabel1.Text = "Number of row(s): " + intRow.ToString();
            dgv1.MultiSelect = false;
            dgv1.AutoGenerateColumns = true;
            dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

            dgv1.DataSource = dt;

            dgv1.Columns[0].HeaderText = "ID";
            dgv1.Columns[1].HeaderText = "First Name";
            dgv1.Columns[2].HeaderText = "Last Name";
            dgv1.Columns[3].HeaderText = "Full Name";
            dgv1.Columns[4].HeaderText = "Gender";

            dgv1.Columns[0].Width = 85;
            dgv1.Columns[1].Width = 170;
            dgv1.Columns[2].Width = 170;
            dgv1.Columns[3].Width = 220;
            dgv1.Columns[4].Width = 100;

        }

        private void execute(string mySQL, string param)
        {
            CRUD.cmd = new NpgsqlCommand(mySQL, CRUD.con);
            addParameters(param);
            CRUD.PerformCRUD(CRUD.cmd);
        }

        private void addParameters(string str)
        {
            CRUD.cmd.Parameters.Clear();
            CRUD.cmd.Parameters.AddWithValue("firstName", firstNameTextBox.Text.Trim());
            CRUD.cmd.Parameters.AddWithValue("lastName", lastNameTextBox.Text.Trim());
            CRUD.cmd.Parameters.AddWithValue("gender", genderComboBox.SelectedItem.ToString());

            if (str == "Update" || str == "Delete" && !string.IsNullOrEmpty(this.id))
            {
                CRUD.cmd.Parameters.AddWithValue("id", this.id);
            }
        }
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex != -1)
            {
                this.id = Convert.ToString(dgv1.CurrentRow.Cells[0].Value);
                updateButton.Text = "Update (" + this.id + ")";
                deleteButton.Text = "Delete (" + this.id + ")";

                firstNameTextBox.Text = Convert.ToString(dgv1.CurrentRow.Cells[1].Value);
                lastNameTextBox.Text = Convert.ToString(dgv1.CurrentRow.Cells[2].Value);

                genderComboBox.SelectedItem = Convert.ToString(dgv1.CurrentRow.Cells[4].Value);

            }
        }
        private void insertButton_Click_1(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(firstNameTextBox.Text.Trim()) || string.IsNullOrEmpty(lastNameTextBox.Text.Trim()))
            {
                MessageBox.Show("Please input first name and  last name.", "Insert Data : Admin said yes!",
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            CRUD.sql = "INSERT INTO tb_crudsystem (firstname, lastname, gender) VALUES(@firstName, @lastName, @gender)";

            execute(CRUD.sql, "Insert");

            MessageBox.Show("The record has been saved.", "Insert Data : Admin said yes!",
                MessageBoxButtons.OK, MessageBoxIcon.Information);

            loadData("", dgv1);

            resetMe();
        }

        private void updateButton_Click_1(object sender, EventArgs e)
        {
            if (dgv1.Rows.Count == 0)
            {
                return;
            }

            if (string.IsNullOrEmpty(this.id))
            {
                MessageBox.Show("Please select an item from the list.", "Update Data : Admin said yes!",
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            if (string.IsNullOrEmpty(firstNameTextBox.Text.Trim()) || string.IsNullOrEmpty(lastNameTextBox.Text.Trim()))
            {
                MessageBox.Show("Please input first name and  last name.", "Insert Data : Admin said yes!",
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            CRUD.sql = "UPDATE tb_crudsystem SET firstName = @firstName, lastname = @lastName, gender = @gender WHERE id = @id::integer";

            execute(CRUD.sql, "Update");

            MessageBox.Show("The record has been updated.", "Update Data : Admin said yes!",
                MessageBoxButtons.OK, MessageBoxIcon.Information);

            loadData("", dgv1);

            resetMe();
        }

        private void deleteButton_Click_1(object sender, EventArgs e)
        {
            if (dgv1.Rows.Count == 0)
            {
                return;
            }

            if (string.IsNullOrEmpty(this.id))
            {
                MessageBox.Show("Please select an item from the list.", "Delete Data : Admin said yes!",
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            if (MessageBox.Show("Do you want to permanently delete the selected record?", "Delete Data : Admin said yes!",
                                MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
            {

                CRUD.sql = "DELETE FROM tb_crudsystem WHERE id = @id::integer";

                execute(CRUD.sql, "Update");

                MessageBox.Show("The record has been deleted.", "Delete Data : Admin said yes!",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);

                loadData("", dgv1);

                resetMe();

            }
        }

        private void button1_Click(object sender, EventArgs e) //search button
        {
            // Let's try :)
            if (string.IsNullOrEmpty(keywordTextBox.Text.Trim()))
            {
                loadData("", dgv1);
            }
            else
            {
                loadData(keywordTextBox.Text.Trim(), dgv1);
            }
            resetMe();
        }
    }
}
screenshot:
Capture.PNG
 
That's how auto incrementing keys typically work with any database. If you want full control of the key value, then you'll have to write your own code to create key values instead of letting the database generate the values for you.

Also recall from your database design classes (or at least a good database design teacher should have told you this): Keys are only to be used for the sake of the relational database system to work correctly. If you are using the key as something that the user can use (and may have to remember on enter), create another field for this. Use that fields for initial searches and lookups, and then get the real key value from there.
 
That's how auto incrementing keys typically work with any database. If you want full control of the key value, then you'll have to write your own code to create key values instead of letting the database generate the values for you.

Also recall from your database design classes (or at least a good database design teacher should have told you this): Keys are only to be used for the sake of the relational database system to work correctly. If you are using the key as something that the user can use (and may have to remember on enter), create another field for this. Use that fields for initial searches and lookups, and then get the real key value from there.
Yeah, I think I messed something up in my database settings. But I want full control through writing codes.
 
Well, then, first step is to change the schema of your database so that the ID column in not an auto generated identity column, but still the primary key.

The next step is to change your code so that before you insert, you lock the entire table, and scan through every ID value and find the first available id value. You then do the insert using that available value. And then you finally unlock the table. You maybe able to get away from having to lock and unlock the table by starting a transaction and then committing the transaction.
 
Last edited:
Solution
Well, then, first step is to change the schema of your database so that the ID column in not an auto generated identity column, but still the primary key.

The next step is to change your code so that before you insert, you lock the entire table, and scan through every ID value and find the first available id value. You then do the insert using that available value. And then you finally unlock the table. You maybe able to get away from having to lock and unlock the table by starting a transaction and then committing the transaction.
Yeah I can imagine doing that but I think the best way is to automate things right?
 
You said you wanted full control. But now you are asking for automation.

If you want it automated without having to do anything extra, then you'll need to accept the automated behavior which is the monotonically increasing values. Stop and think about it from the database engine writer's point of view: how would you create key values cheap (as in CPU and memory, and disk space resources) that are guaranteed to be unique even after records are deleted from a database and later potentially restored?

If you really want it automated (from the point of view of your C# code), then you can add database triggers (assuming that your database supports triggers). The trigger basically looks for new rows inserted into a table, and then sets the column value to the next available value as described in post #4. Of course, you'll run into a chicken and egg problem. Since that column is supposed to be a key column, then a value needs to be there on the initial insert before your trigger fires. But your trigger's job is to change that value to the next available value. The only way around this is to always insert a magic number that is guaranteed to be replaced by the trigger, or to let the system actually first use the monotonically increasing number and then you replace it with the next available number in the trigger.
 
To automate everything not by doing more manual things, I knew it was a good concept but then just speculations inside my head rather than implementation.
That actually doesn't answer my question. If you want everything automated then let the identity work the way it works. My question was what do you think you are achieving by reusing IDs? People seem to think that having "holes" in their IDs is bad for some reason but what is that reason? No one has ever given me a good one.
 
People seem to think that having "holes" in their IDs is bad for some reason but what is that reason? No one has ever given me a good one.
As I recall that was a fun lecture day when I was taking my database class in college, when the discussion was about row ids. Our teacher entertained all kinds of reasons why there shouldn't be holes, but then it all came down to what I stated in second in post #2: Why does it really matter if there are holes? It's just the plumbing underneath that shouldn't even be visible to the users.
 
I get it from the perspective that having holes in the series makes people uncomfortable because I'm the type of person who likes things lined and in their proper place - what people sometimes colloquially refer to as OCD - but that's really all it comes down to. There is no practical reason for it.
 
What was funny was the teacher and class also had an impromptu lecture/discussion later that week outside the classroom about using negative numbers, and God forbid, zero as row IDs.
 
You said you wanted full control. But now you are asking for automation.

If you want it automated without having to do anything extra, then you'll need to accept the automated behavior which is the monotonically increasing values. Stop and think about it from the database engine writer's point of view: how would you create key values cheap (as in CPU and memory, and disk space resources) that are guaranteed to be unique even after records are deleted from a database and later potentially restored?

If you really want it automated (from the point of view of your C# code), then you can add database triggers (assuming that your database supports triggers). The trigger basically looks for new rows inserted into a table, and then sets the column value to the next available value as described in post #4. Of course, you'll run into a chicken and egg problem. Since that column is supposed to be a key column, then a value needs to be there on the initial insert before your trigger fires. But your trigger's job is to change that value to the next available value. The only way around this is to always insert a magic number that is guaranteed to be replaced by the trigger, or to let the system actually first use the monotonically increasing number and then you replace it with the next available number in the trigger.
I mean automated function to save more time that's what I mean to say, sir not the whole thing automated. Yes, full control of the program is essential for us to make the programming more precise. Resetting identity in the column without thinking of it is a disaster It's not advisable right? because for example when two clients were trying to insert a new row at the same time. I would have to agree with you about the triggers, the function should be called inside the trigger.
 
That actually doesn't answer my question. If you want everything automated then let the identity work the way it works. My question was what do you think you are achieving by reusing IDs? People seem to think that having "holes" in their IDs is bad for some reason but what is that reason? No one has ever given me a good one.
I don't know what is the exact word to say and I still learning about the depth of databases. But this came up the answer by jmoreno from db stackexchange.com :
Reusing an identity value, should in general be discouraged. Either the value is used entirely internally, in which case it’s actual value is immaterial, or it is also used externally in which case reusing the value is very likely going to lead to a misidentification.
Take the obvious case of an invoice or purchase order number, these might easily come from an identity column and be exposed externally, but you would never want to reuse them for precisely that reason. Both refer to specific transactions which you would not want to get confused about. Resolving such issues can be a big hassle when companies merge or are acquired. Creating such problems on purpose? Not wise.
 
I don't know what is the exact word to say and I still learning about the depth of databases. But this came up the answer by jmoreno from db stackexchange.com :
Reusing an identity value, should in general be discouraged. Either the value is used entirely internally, in which case it’s actual value is immaterial, or it is also used externally in which case reusing the value is very likely going to lead to a misidentification.
Take the obvious case of an invoice or purchase order number, these might easily come from an identity column and be exposed externally, but you would never want to reuse them for precisely that reason. Both refer to specific transactions which you would not want to get confused about. Resolving such issues can be a big hassle when companies merge or are acquired. Creating such problems on purpose? Not wise.
That basically reiterates what we're saying to you: what you're asking for is a bad idea so don't do it. Just let the identity column generate the values and if you delete a record then so be it. The fact that there are "holes" in your sequence of IDs is not a problem so you don't need a solution.
 
Back
Top Bottom