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?
You can add on cascade delete, but understand, this will remove all the data from the child table. If that's what you intend, great, but if not, that could be a real issue. I don't understand exactly what's going on, but I'm assuming you're reloading the data for some reason? If so, you will need to, in some manner, either reload the child table data or ensure that the data in the parent table is reentered with the same keys. If you can do the latter, then you can drop the foreign key while you do the insert, then recreate it. Just make sure it recreates without NOCHECK (which prevents checking of the foreign key data)
How can i do it?
If you mean ensuring that the key values start from a particular value, look up DBCC CHECKIDENT in the books online. You can use the RESEED option to set it to a particular value
are there multiple ways of doing this?
any help would be greatly appreciated..
The only other way I can think of at the moment to reset the identity value is to alter the table (you might need to drop & recreate). When you're creating the IDENTITY setting you can tell it to start at 100.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning