Delete data in table having foreign key

Question

Post reply

Delete data in table having foreign key

Derek-903163

SSC Veteran

Points: 244
More actions
March 17, 2009 at 7:40 pm

#201924

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..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Leo Leong SSCommitted Points: 1615 More actions · Answer 1

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

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

Ashok.S SSC Eights! Points: 869 More actions · Answer 3

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

soulreader7 Old Hand Points: 379 More actions · Answer 4

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.

bhushanvinay SSCrazy Points: 2253 More actions · Answer 5

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

Paul White SSC Guru Points: 150467 More actions · Answer 6

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi