Resetting the primary key to 1

Question

I have a script for microsoft sql server database which has hundreds of tables and tables contains data as well. This is the database of a web application.what I want to do is to delete the previous records and reset the primary key to 1 or 0. I have tried

`DBCC CHECKIDENT ('dbo.tbl',RESEED,0); `

but it does not work for me as in most of the tables the primary key is not identity.

I can not truncate the table as its primary key is being used as FK in many other tables. I have also tried to add the identity specification in the primary key of the table and run the checkident query and then changing it back to non-identity spec, but after adding the record again it starts from where it left. Making changes in the code is not an option for me.

please help.

I am not sure what you want to do. If I am right you have tables on which the primary key is not an identity but you want to reseed its value to 1, without modifying the data. Am I right? — S.Karras, Mar 17 '16 at 07:38
@S.Karras my purpose is just to reset the PK to 0 or 1 even after the data deletion which am not able to achieve with reseed as well. — Talib, Mar 17 '16 at 07:39
If you do not have identity columns, then the primary key takes its value from somewhere else. How is that done in your case? — S.Karras, Mar 17 '16 at 07:43
That's the problem.I don't know exactly either its coming from the code or how is it done but the problem is even the deletion of all the data when the new record is added its start from where it left... — Talib, Mar 17 '16 at 07:47
Is it possible that the primary key values use a sequence to be generated? Can you check please? — S.Karras, Mar 17 '16 at 07:52
If your database system isn't MySQL, **don't** put the [tag:mysql] tag on your question. — Damien_The_Unbeliever, Mar 17 '16 at 07:52
@Damien_The_Unbeliever its not in the mysql but ms sql and my sql are same ,the ways are same in both. — Talib, Mar 17 '16 at 07:58
No, the really aren't. Putting **irrelevant** tags on your question doesn't help anybody. — Damien_The_Unbeliever, Mar 17 '16 at 07:59
@S.Karras yes keys generated are in sequence like 66,67 but i want to start them from 1. — Talib, Mar 17 '16 at 08:00
No what I meant is, are there [sequences](https://msdn.microsoft.com/en-us/library/ff878091.aspx) in your database from which the primary key values are generated? — S.Karras, Mar 17 '16 at 09:15

score 1 · Accepted Answer · edited May 23 '17 at 12:24

According with your question I am not sure about the main objective, Why? If you need truncate a lot of tables and change their structures to have an Identity property why you can't disabled the FK? . In the past I have used an standard process for rebuild a table and migrate all the information, this represent a group of steps, I would try to help you but you should follow the next steps.

Steps:

1) Disable FK for alter the structure of your tables. You can get the solution for this task in the next link:

Temporarily disable all foreign key constraints

2) Alter the table with the new property Identity, this is a classic process of ALTER TABLE xxxxxx.

3) Execute the syntax that previously posted : DBCC CHECKIDENT ('dbo.tbl',RESEED,0);

Try to follow this path and if you have any problem only ask us.

score 0 · Answer 2 · answered Mar 17 '16 at 07:36

0

You can not truncate table that have relation. You shoud remove relation firstly.

answered Mar 17 '16 at 07:36

MahmutHAKTAN

659
2
7
16

I can't do this as it is a very big database ,I need some better solution. – Talib Mar 17 '16 at 07:37
Why you dont create tables create script to fresh database? Right click database > Task> Generate scripts. If you want to re insert some tables you may create insert script of that tables with some way. – MahmutHAKTAN Mar 17 '16 at 08:01
I generated the script and created the database all over again but it again starts with the same id from where it left. – Talib Mar 17 '16 at 08:06

score 0 · Answer 3 · answered Mar 17 '16 at 12:35

My understanding of this question: You have a database with tables that you want to empty and next have them use primary key values starting at 0 or 1. Some of these tables use an identity value and you already have a solution for those (you know you can find out which columns have an identity by using the sys.columns view? Look for the is_identity column). Some tables do not use an identity but get their pk values from an unknown source, which we can't modify.

The only solution I see, is creating an after insert trigger (or modifying) on those tables that subtracts from the new pk value. E.g.: your "hidden generator" will generate a next value 5254, but you want the next pk value to become one:

CREATE TRIGGER trg_sometable_ai
ON  sometable
AFTER   INSERT  
AS  
BEGIN
    UPDATE st
    SET    st.pk_col = st.pk_col - 5253
    FROM   sometable AS st
            INNER JOIN INSERTED AS i
            ON i.pk_col = th.pk_col
END

You'll have to determine the next value and thus the "subtract value" for each table.

If the code also inserts child records into tables with a foreign key to this table, and uses the previously generated value, you have to modify those triggers as well...

This is a "last resort" solution and something I would recommend against in any scenario that has other options. Manipulating primary key values is generally not a good idea.

Resetting the primary key to 1

3 Answers3