March 22, 2009 at 5:25 am
I have drop all rows in my table... when i insert them back aggain the identity starts at 50 instead of 1.
i think i need to set identity to zero?
March 22, 2009 at 5:42 am
Truncate table mytable
go
DBCC CHECKIDENT(mytable, reseed, 50)
go
then try to insert values into the table.
March 22, 2009 at 6:17 am
i tested it but it doesnt work.. its starts with 51.52.53,54
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'lonekat' because it is being referenced by a FOREIGN KEY constraint.
Checking identity information: current identity value '37', current column value '37'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
March 22, 2009 at 7:12 am
That's the major thing in the table if identity column is specified we can go to back again unless we DROP that table compeltly & again created again.
I had also tried but it simple won't work.
In other words must drop completely table & create a new having same structure.
March 22, 2009 at 7:38 am
Hi
Here an example which shows how to reset the IDENTITY column.
USE tempdb
IF (OBJECT_ID('test') IS NOT NULL)
DROP TABLE test
GO
CREATE TABLE test (id INT IDENTITY, txt VARCHAR(100))
GO
INSERT INTO test
SELECT 'hello'
UNION ALL SELECT 'world'
SELECT * FROM test
GO
DELETE FROM test
GO
DBCC CHECKIDENT(test, reseed, 0)
GO
INSERT INTO test
SELECT 'foo'
UNION ALL SELECT 'bar'
SELECT * FROM test
Greets
Flo
March 22, 2009 at 8:10 am
Polo_Sport09
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'lonekat' because it is being referenced by a FOREIGN KEY constraint.
Some where, some one set up the Foreign Key restraint presmably for a valid logical reason. Do you want to remove this requirement to enforce referential integrity?
Although the main purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson's ID is used for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.
The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table.
March 23, 2009 at 6:50 am
dbcc checkident(Table_NAME, reseed, 0)
-------------------------
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...
March 23, 2009 at 11:02 am
You have got the error due to foriegn key constraint....
if u want to just increase the identity value by x for the table having some rows.
then us just to reseed to the just the previous value.
in ur case if u just try with
dbcc checkident(mytable, reseed, 49)...............
if u want to reset to 0....
then u have to drop the child & parent table .....
and create the table again...
March 23, 2009 at 5:10 pm
polo_sport09 (3/22/2009)
I have drop all rows in my table... when i insert them back aggain the identity starts at 50 instead of 1.i think i need to set identity to zero?
Why do you want to do this? It may make a difference.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply