SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete data in table having foreign key


Delete data in table having foreign key

Author
Message
Derek-903163
Derek-903163
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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..
Leo Leong
Leo Leong
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 328
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
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99653 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Ashok.S
Ashok.S
SSC-Addicted
SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)

Group: General Forum Members
Points: 451 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
soulreader7
soulreader7
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 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.
bhushanvinay
bhushanvinay
SSChasing Mays
SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

Group: General Forum Members
Points: 637 Visits: 196
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
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36136 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search