| Author |
 Topic  |
|
|
cristuballa
Starting Member
Philippines
13 Posts |
 Posted - 10/27/2003 : 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!
Australia
4970 Posts |
Posted - 10/27/2003 : 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 - 10/12/2004 : 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
United Kingdom
12543 Posts |
Posted - 10/12/2004 : 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 - 10/12/2004 : 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-) |
|
|
| |
Topic |
|
primary key start at record no. 1 again
Forum Locked
