March 17, 2009 at 7:40 pm
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?
any help would be greatly appreciated..
March 17, 2009 at 10:09 pm
for your FOREIGN KEY relationship, add ON UPDATE CASCADE
if your column is IDNETITY, you got to change it to non-identity.
after that, run an UPDATE function on your main table. your child table will get updated automatically.
when this is done, remember to set the key column to IDNETITY
March 18, 2009 at 6:03 am
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
March 18, 2009 at 6:43 am
Derek (3/17/2009)
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
February 18, 2010 at 7:18 am
guyz i am having problem in delete a record
i am using northwind database and want to delete custom.
the customer table pk is fk in order table and order tablt pk is fk in order detail table
can somebody tell me the query to delete the customer.
February 21, 2010 at 8:13 am
soulreader7 (2/18/2010)
guyz i am having problem in delete a recordi am using northwind database and want to delete custom.
the customer table pk is fk in order table and order tablt pk is fk in order detail table
can somebody tell me the query to delete the customer.
Seriously you need a book.
Search google for disable foreign key sql server find out more about it.
disable the foreign key
delete your data.
then re-enable it back if you get error dont blame me.
The idea behind the foreign key is making sure of the data integrity, so delete the orders responsible for the custormer first and then delete the customer. that way you dont violate the foreign key constraint.
Regards
Vinay
February 21, 2010 at 9:44 am
bhushanvinay (2/21/2010)
Seriously you need a book.Search google for disable foreign key sql server find out more about it.
disable the foreign key
delete your data.
then re-enable it back if you get error dont blame me.
The idea behind the foreign key is making sure of the data integrity, so delete the orders responsible for the custormer first and then delete the customer. that way you dont violate the foreign key constraint.
Seriously, you need an attitude adjustment :laugh:
If the question annoys you, move on along - there are plenty of other skilled professionals on SSC who will help.
Paul
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply