Derek (3/17/2009)

---

There is one table which has a foreign key constraint.. I have to delete all the data in this and while adding new data the primary key should start with a value of 100 and then keep on increasing by 1 ( identity function ) Is there anyway of not deleting the foreign key n doing it?

How can i do it?

are there multiple ways of doing this?

Hi,

As suggested by ppls here, you can go ahead and do the same. You can use oncascase delete over the primary-foreign key constraint, also you can run delete query on the table and then run DBCC CheckIdent() over the table, with reseed option. Both will work.

And I'm giving you one more suggestion, you can use truncate table which is more faster than delete statement. Suppose if you have data in GB then delete will affect your sql performance. Hence, I suggest you to run Truncate Table "Tablename", then do DBCC CheckIdent() over the table to set the reseed value to which ever you want.

Advantage of using Truncate is it will automatically reseed the Primary key column to "1". And it shows better performance than Delete.

But, in this case you need to drop the Constraint before doing the Truncate. Since, it wont allow you to truncate the table with constraint enabled.

Regards,

Ashok S