Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete data in table having foreign key Expand / Collapse
Author
Message
Posted Tuesday, March 17, 2009 7:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 1:04 PM
Points: 2, Visits: 63
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..
Post #678044
Posted Tuesday, March 17, 2009 10:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:51 AM
Points: 106, Visits: 327
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
Post #678083
Posted Wednesday, March 18, 2009 6:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:31 AM
Points: 14,788, Visits: 27,263
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #678313
Posted Wednesday, March 18, 2009 6:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 27, 2011 10:42 PM
Points: 309, Visits: 135
[quote]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?
[quote]

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
Post #678361
Posted Thursday, February 18, 2010 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 03, 2010 5:22 AM
Points: 11, Visits: 9
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.
Post #868022
Posted Sunday, February 21, 2010 8:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 02, 2013 9:39 AM
Points: 329, Visits: 195
soulreader7 (2/18/2010)
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.


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
Post #869842
Posted Sunday, February 21, 2010 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:32 AM
Points: 11,168, Visits: 10,926
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

If the question annoys you, move on along - there are plenty of other skilled professionals on SSC who will help.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #869853
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse