id / primary key reused after row deleted?

id / primary key reused after row deleted?

  

Would anyone know if the id variables in databases are reused after a row is deleted? This would be in a personal environment, not absolutely sure what database backs that environment. Sorry for noob question. Its just that as its a personal env I'd like to back up my data. I know even the code isn't guaranteed in a personal env, but my data and app aren't that precious, I just figured that as the rest api works so well and databases in AWS are so cheap I'd send my data to a cheapy provision of data there. But then I started writing code to pull it back in and started to wonder how I was going to make it work with all the foreign key relationships. I.e. if two tables are backed up and table one has a primary key and also field with a reference to the keys of table two, how will I maintain that when I pull the data back. I'll obviusly record the keys when I back the data up, but when I pull it back I guess I cant assume I'll be able to slot back into original rows with same ids. As such I guess I'll need to reconstruct the relationships. Not impossible, but I don't want to make work for myself if the id's dont get reused in the event of a delete then I would be able to slot things back in. Just would have thought database engines might reuse Ids. Any info very welcome!!

Solution

Hello Danny,


The RDBMS is SQL Server. If you don't issue any custom command, the id's will not be reused. It is the default behavior of the Identity property.


Hope this helps,

Armando


EDIT: You can issue some more "advanced" SQL instructions with an Advanced Query. I'm not sure if turning identify OFF and ON would be acceptable.

Solution

Armando, it does help so thank you!!

If the ID is not reused, if when a row is deleted the Identity is never assigned to another new row, then that's perfect. Well I think it is. Although... Now I wonder if I should be deleting or just nulling everything and if that will actually save space. Okay, follow up question:

If I delete a row in a table can I later update it? I presume not. I cannot later create a row with a specific Id, or can I?

You see what I'd like to do is be able to delete a row in order to save the space, but then pull that row back from offsite in the event I needed it again and somehow end up with it having the same Id. If the Id isn't reused after a delete then thats good. But now I find myself wondering how I'd put the row back in. I can't create with a specific Id can I - surely the Ids are designated for you on a create. And I can't update because the row with that Id would be gone having been deleted.

In the time its take to ask that follow up question I could have tested that and worked it out! I'm a bit dumb sometimes. Quite a lot to be honest.

Danny O'Neill wrote:

Armando, it does help so thank you!!

If the ID is not reused, if when a row is deleted the Identity is never assigned to another new row, then that's perfect. Well I think it is. Although... Now I wonder if I should be deleting or just nulling everything and if that will actually save space. Okay, follow up question:

If I delete a row in a table can I later update it? I presume not. I cannot later create a row with a specific Id, or can I?

No. If you delete the row, you can't do any action over it anymore. You can create specific rows with a specific identifier number but, as far as I'm aware, that will only work if your PK is not an autonumber.

My suggestion for your case would be something like: keep the "live" entity with the records you need and, once you don't need them anymore, move them to an history table. Once you need them again, just query that history table. Obviously, queries to the history table should be a small percentage.


Cheers!