Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to reseed all of my tables. I delete all the values from them and then run this query:

But then when I populate this database again, id starts from the maximal id of the deleted entries. Why doesn't it work?

UPDATE:

delete queries:

delete from Car;
delete from Detail;
delete from Owner;

and then

DBCC CHECKIDENT ('Car', RESEED, 0);
DBCC CHECKIDENT ('Detail', RESEED, 0);
DBCC CHECKIDENT ('Owner', RESEED, 0);

Both Car and Detail tables has user_id foreign key which points to the User's id column.

share|improve this question
2  
If you want to delete all rows and reset the id value you can just use truncate. That said this works for me create table Car(Id int identity); insert into Car default values; insert into Car default values; select * from Car; delete from Car;DBCC CHECKIDENT ('Car', RESEED, 0);insert into Car default values;select * from Car; drop table Car –  Martin Smith Jan 12 '12 at 18:29
    
Trancate doesn't work for those tables which has foreign keys and both Car and Detail tables have foreign keys –  Sergey Jan 12 '12 at 18:38
3  
If you're going to wipe all the tables clean, why don't you create your script so that it drops the constraints and re-creates them? Or just drops the tables and re-creates them? –  Aaron Bertrand Jan 12 '12 at 18:43
    
Can you post the full script that deletes the data and reseeds the tables? What you've posted should work, assuming the tables are in the correct schema. –  Nick Chammas Jan 12 '12 at 18:45
    
That's it, it's very simlpe but I can't understand why it doesn't work –  Sergey Jan 13 '12 at 4:24
add comment

1 Answer 1

Except for any other errors, script, or settings, etc that are not described, there shouldn't be any reason why this shouldn't work. Here is a sample I've put together that would demonstrate its success.

set nocount on;
go

-- create the tables
drop table [Car];
drop table [Detail];
drop table [owner]
go

create table dbo.[Owner]
(OwnerID INT IDENTITY (1,1) NOT NULL PRIMARY KEY
, OwnerDescription varchar(35) NOT NULL
)

create table dbo.[Detail]
(DetailID INT IDENTITY (1,1) NOT NULL PRIMARY KEY
, OwnerID INT NOT NULL
, DetailDescription varchar(35) NOT NULL
)

create table dbo.[Car]
(CarID INT IDENTITY (1,1) NOT NULL PRIMARY KEY
, DetailID INT NOT NULL
, CarDescription varchar(35) NOT NULL
)
go

-- create fk constraints
ALTER TABLE dbo.[Detail]
ADD CONSTRAINT FK_Detail_Owner FOREIGN KEY (OwnerID)
    REFERENCES dbo.[Owner] (OwnerID) ;

ALTER TABLE dbo.[Car]
ADD CONSTRAINT FK_Car_Detail FOREIGN KEY (DetailID)
    REFERENCES dbo.[Detail] (DetailID) ;
GO

-- insert first set of data to consume first identity key values
INSERT INTO [dbo].[Owner] ([OwnerDescription])
VALUES ('Some Owner')
INSERT INTO [dbo].[Detail] ([DetailDescription], OwnerID)
VALUES ('Some Detail', 1)
INSERT INTO [dbo].[Car] ([CarDescription], DetailID)
VALUES ('Some Car', 1)
go

-- examine the data
select * from [Owner]
select * from [Detail]
select * from [Car]
go

-- remove the data with standard delete (because truncate won't work with FKs)
delete from [Car];
delete from [Detail]
delete from [Owner]
go

-- insert second set of data to consume second identity key values
INSERT INTO [dbo].[Owner] ([OwnerDescription])
VALUES ('Some Owner')
INSERT INTO [dbo].[Detail] ([DetailDescription], OwnerID)
VALUES ('Some Detail', 2)
INSERT INTO [dbo].[Car] ([CarDescription], DetailID)
VALUES ('Some Car', 2)
go

-- examine the data
select * from [Owner]
select * from [Detail]
select * from [Car]
go

-- remove the data with standard delete (because truncate won't work with FKs)
delete from [Car];
delete from [Detail]
delete from [Owner]
go

-- reseed the values to 0
dbcc checkident ('Owner', reseed, 0)
dbcc checkident ('Detail', reseed, 0)
dbcc checkident ('Car', reseed, 0)
go

-- reinsert to demonstrate the reseed works
INSERT INTO [dbo].[Owner] ([OwnerDescription])
VALUES ('Some Owner')
INSERT INTO [dbo].[Detail] ([DetailDescription], OwnerID)
VALUES ('Some Detail', 1)
INSERT INTO [dbo].[Car] ([CarDescription], DetailID)
VALUES ('Some Car', 1)
go

-- examine the data
select * from [Owner]
select * from [Detail]
select * from [Car]
go
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.