Why is it that when I delete from my database the id remains?

Question

Sorry in advance for my question being maybe kind of vague. I just don't know how to necessarily phrase it properly.

When I attempt to delete from my database, the data id is still present and when I add new data to the table the id continues to increase.

My controller:

[HttpGet]
    public ViewResult Delete(int id)
    {
        var product = context.Products
                        .FirstOrDefault(c => c.ProductId == id);

        return View(product);
    }

    [HttpPost]
    public RedirectToActionResult Delete(Product product)
    {
        context.Products.Remove(product);
        context.SaveChanges();
        return RedirectToAction("List", "Product");
    }

My View for where I list the items:

@foreach (Product product in Model)
    {
        <tr>
            <td>@product.Code</td>
            <td>@product.Name</td>
            <td>$@product.Price</td>
            <td>@product.ReleaseDate</td>
            <td>
                <a asp-controller="Product" asp-action="Edit"
                   asp-route-id="@product.ProductId" class="btn btn-primary">Edit</a>
            </td>
            <td>
                <a asp-controller="Product" asp-action="Delete"
                   asp-route-id="@product.ProductId" class="btn btn-primary">Delete</a>
            </td>
        </tr>
    }

My View for the delete action:

<form asp-action="Delete" method="post">
<input type="hidden" asp-for="ProductId" />

<div>
    <button type="submit" class="btn btn-primary">Delete</button>
    <a asp-controller="Product" asp-action="List" class="btn btn-primary">Cancel</a>
</div>

What am I doing wrong here? or if nothing is wrong why does it behave the way it does in terms of the id field.

Answer 1

SQL server does not reuse values for IDENTITY columns.

From CREATE TABLE (Transact-SQL) IDENTITY (Property):

Reuse of values - For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.

Answer 2

If you are using Microsoft Access as your backend. An auto number type can be "by increment" or "by random". I believe that you set a value from your column to an auto number type. An auto number type on your table always increment even though you delete some data of it.

The other solution to is that you will make an another table that serves ID(for example ProductID) that is not an auto increment type maybe text or something . As charlie said auto increment ids doesn't decrement on delete.

If you are using Sql just remove the AUTO_INCREMENT keyword on your primary key (if you set your id as a primary key).

You might want to visit this post that is related to your problem : Auto Increment after delete in MySQL

Answer 3

Why is it that when I delete from my database the id remains?

As tymtam said, for the Identity column, if delete or insert fails, the identity value will not reuse.

To prevent this behavior, when you define the model, you could use the Data Annotations or Fluent API to specify that the value of ID property will not be generated by the underlying database.

Using Data Annotations method, code like this:

public class Product
{
    // using System.ComponentModel.DataAnnotations;
    // using System.ComponentModel.DataAnnotations.Schema;
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ID { get; set; }
    public string Name { get; set; }
    public DateTime RelaseDate { get; set; }
}

Using Fluent API, code like this:

public class Product
{
    // using System.ComponentModel.DataAnnotations; 
    [Key] 
    public int ID { get; set; }
    public string Name { get; set; }
    public DateTime RelaseDate { get; set; }
}

and

public class ApplicationDbContext : IdentityDbContext
{ 
    public DbSet<Product> Products { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder); 
        builder.Entity<Product>().Property(p => p.ID).ValueGeneratedNever();
    }
}

Then, use the EF core Migration to generate the table. By using this method, the ID primary key will not auto be generated, and you must set its value before inserting new Product.

If you have already generated the table with Identity column, by using the above method to change the property, it will show the To change the IDENTITY property of a column, the column needs to be dropped and re-created. error when migration. In this scenario, you have to drop the table from the database and delete the migration history, then, using the above method to re-generate the table.

Besides, there also have another solution, you could change the table designer via SSMS.

Click the View menu in VS2019, choose the SQL Server Object Explorer, connect the Database, and right click the table and choose the View Designer, then, in the table T-SQL panel, remove the Identity(1,1) from the identity column, click the Update button to save changes. After that, you could insert a new item with the custom value.