MS SQL Server 7: deletion of Child records.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MS SQL Server 7: deletion of Child records.

Author

Topic

Manoj Kumar
Starting Member

9 Posts

Posted - 2006-09-16 : 02:30:18

Hello All,

I have been involved in a project wherein i have to delete old records from two table and the corresponding child records from the interlinked tables.
I try to find a SQL Procedure that deletes the child records, but that procedure uses the sysforeignkeys table for recursively deleting the child records.
When i looked at my sysoreignkeys table, it was empty(no rows).
Request you all if you can provide me the SQL Procedure that can delete record in the table with the child records.

Thanks in advance

Regards,
Manoj Kumar

Kristen
Test

22859 Posts

Posted - 2006-09-16 : 04:00:59

What does

sp_help 'MyTable'

say in the "Table is referenced by foreign key" section?

(I haven't got SQL7 here, the heading for that section might have a slightly different name, but I expect you get the idea!)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-16 : 08:17:38

Is there referential integrity between parent and child tables?
Did you mean whenever the parent record is deleted, corresponding child record should be deleted?

Madhivanan

Failing to plan is Planning to fail

Manoj Kumar
Starting Member

9 Posts

Posted - 2006-09-16 : 11:52:06

Hello Madhivanan,

There does exist refrential integrity between parent and child tables.
Yes I want to delete the records from two tables with the child records from different tables.
For an example: There is a table named CONTACT where i have to delete some old contacts. The primary key is "contact_id" and this is also a foreign key for some other tables.
So the basic purpose is to delete the records from CONTACT table and the corresponding information( orphan records)about the contcts from some other tables.

I hope this is clear now.

Thanks and Regards,
Manoj Kumar

Manoj Kumar
Starting Member

9 Posts

Posted - 2006-09-16 : 12:08:42

Hello Kristen,

Thanks for your reply.
I actually know what are the foreign keys linked to the parent table.
I am simply looking for a procedure that deletes the orphan records(child records) with the parent tables records,

Thanks and Regards,
Manoj Kumar

quote:
Originally posted by Kristen

What does

sp_help 'MyTable'

say in the "Table is referenced by foreign key" section?

(I haven't got SQL7 here, the heading for that section might have a slightly different name, but I expect you get the idea!)

Kristen

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-17 : 18:14:30

Manoj,

Check out this article: http://www.sqlteam.com/item.asp?ItemID=8595

Cheers,

Tim

Manoj Kumar
Starting Member

9 Posts

Posted - 2006-09-18 : 01:16:27

Hello Tim,

Thanks for the link who provoded.

As i have mentioned in my first mail that in my database "dbo.sysforeignkeys" is empty( no rows) and your procedure umake use of this dbo.sysforeignkeys table.

Thanks and Regards,

Manoj Kumar

Kristen
Test

22859 Posts

Posted - 2006-09-18 : 03:48:57

To the best of my knowledge you can't have sysforeignkeys empty AND for sp_help 'MyTable' to show items in its "Table is referenced by foreign key" section - which is why I suggested you try running sp_help

Kristen

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-18 : 17:53:49

Manoj,

My script assumes that you have foreign key constraints between your related tables (hence there would be entries in sysforeignkeys).

Tim

Manoj Kumar
Starting Member

9 Posts

Posted - 2006-09-20 : 00:55:20

Hello Timmy/Kristen,

Suppose there exist no foreign key constraints between the related tables. But in real world scenario i wanted to delete records from a table and the corresponding records(supposed to be child records) from other tables.

I wanted to know if suppose we do not have any entry in the sysforeignkeys table then does it means there exist no foreign key constraint between tables.

The database i am refering to is basically a reporting database(stand alone) and doesn't require any foreign key constraints to be defined.

According to my understanding I have to design my own SQL Procedure that will perform the deletion without the use of any sysforeignkey table. I have the list of field which are supposed to be thee foreign keys involved.

Thanks and Regards,
Manoj Kumar

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-20 : 01:37:59

AFAIK all foreign keys are contained in sysforeignkey. Not much use if they weren't I suppose.

You could always create your own 'clone' of sysforeignkeys and manually populate it. Have a look at BOL for details. Or you could createa copy of your database, create the FK's on there and then take a copy of sysforeignkeys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources