primary key start at record no. 1 again

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

primary key start at record no. 1 again

Author

Topic

cristuballa
Starting Member

13 Posts

Posted - 2003-10-27 : 00:33:15

i have added 2 records to my table having ID as primary key and delete those records (clean the table) so When i add new record again the primary key number should start 1 but it does. how should i do that?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-27 : 00:39:33

quote:

When i add new record again the primary key number should start 1

No it shouldn't.

If you delete a record and insert a new one, they shouldn't have the same ID because they are different records.
If you want to reset the identity for "neatness" reasons after testing, you can use "Truncate Table" or "DBCC CHECKIDENT". The syntax for each of these is in Books Online.

Damian

Johnyalm
Starting Member

49 Posts

Posted - 2004-10-12 : 07:30:16

quote:
Originally posted by Merkin

quote:

When i add new record again the primary key number should start 1

No it shouldn't.

If you delete a record and insert a new one, they shouldn't have the same ID because they are different records.
If you want to reset the identity for "neatness" reasons after testing, you can use "Truncate Table" or "DBCC CHECKIDENT". The syntax for each of these is in Books Online.

But Damian, what about all relationships? Does DBCC CHECKIDENT also rewrite all fields in all other tables where there are a relationship (FK)??

www.mirrorgate.com

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-12 : 09:09:21

>> But Damian, what about all relationships? Does DBCC CHECKIDENT also rewrite all fields in all other tables where there are a relationship (FK)??

Not that I like supporting Damian but I think that's what he meant by "No it shouldn't".

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 09:54:37

quote:
Originally posted by nr

Not that I like supporting Damian...

And after all the support he gave after your last visit...oh, wait, that wasn't sql, it was more of vertical support variety...

You know, I should add this as a reason..there is no "cleaning". Conceptually, in the relational model, this will cause you HUGE problems...

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

Here's some rope...


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'
GO

SELECT * FROM myTable99

TRUNCATE TABLE myTable99
GO

INSERT INTO myTable99(Col2)
SELECT 'd' UNION ALL
SELECT 'e' UNION ALL
SELECT 'f'
GO

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources