Solved

How to delete record with foreign keys?

Posted on 2008-10-26
9
1,886 Views
Last Modified: 2013-12-09
I have a table that has records that may or may not have a foreign key to another record in the same table.  How do I delete the records with foreign keys if I don't know they exist?  (it's like a backward reference and there is no forward reference).
0
Comment
Question by:rmmarsh
  • 4
  • 4
9 Comments
 
LVL 4

Expert Comment

by:dprochownik
ID: 22810427
The  simplest way is to create proper on delete behaviour for foreign key reference.
For example:
if you have table
 test1 (
    id     integer not null,
    data   varchar(10),
    idref  integer
);

where idref is self reference to field ID then:
  1. If you want delete only choosen record, without refenced one, then you can create foreign key like that:
    alter table test1 add constraint fk_test1_1 foreign key (idref) references test1 (id) on delete set null;

  2. if you want delete choosen record and all referenced to it, then you have to create foreign key like that:
    alter table test1 add constraint fk_test1_1 foreign key (idref) references test1 (id) on delete cascade;
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 22810501
you can also just issue the delete anyway, it will simply not delete anything
0
 

Author Comment

by:rmmarsh
ID: 22812022
Hmmm...  I need to clarify this:  some of the records have foreign keys, some don't.  I want to delete records with foreign keys, but I don't know if they exist (the foreign keys) or not... like I said, they are backward references...
0
 
LVL 4

Expert Comment

by:dprochownik
ID: 22812126
If you want to delete only this records, which are referenced by other records, than you can use subquery in delete query:

Delete from Test1 a
where
  exists(select first 1 b.id from Test1 b where b.idref=a.id)

This query will delete all record from table Test1, which are referenced by other records.
If you want only enumerate this records, for manual delete, you can use

select a.ID
from
  Test1 a
where
  exists(select first 1 b.id from Test1 b where b.idref=a.id)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:dprochownik
ID: 22812170
If you want to check if there are defined such foreign references at all (in database structure), than you have to tell which SQL server are you using, because it requires database system tables searching
0
 

Author Comment

by:rmmarsh
ID: 22813002
dprochownik:  your post doesn't make any sense to me... "select first 1 b.id"?  what is "ID"?
0
 
LVL 4

Accepted Solution

by:
dprochownik earned 500 total points
ID: 22815362
Sorry for that
I based that sample on my erlier post. You didn't give any describtion of yours table, so I assumed that there is some table called TEST1 with SQL definition:

test1 (
    id     integer not null,
    data   varchar(10),
    idref  integer
);
alter table test1 add constraint fk1 foreign key (idref) references test1 (id) on delete set null;

where field IDREF is a self reference to the same table, on the field ID.

If you want delete only these records, which have records referenced to them, you have to write simple subquery in delete where section. That subquery will check if for currently deleting recrod there are any references. You can also use exists funciton which returns true if a subquery has any results. For above table it will be:

Delete from Test1 a
where
  exists(select b.id from Test1 b where b.idref=a.id)

It will delete from table A only these records, for which given subquery will return any results. This subquery is executed for every record from table A, and it's results are records from table B (in this case it is the same table), referenced to processing record from table A.

Is it clear right now?
0
 

Author Comment

by:rmmarsh
ID: 22815761
Thank you for explaining it.. I'll give it a try this evening when I get home from work, and let you know...
0
 

Author Comment

by:rmmarsh
ID: 23160428
Sorry for the delay... I'll try to get to it this weekend...
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now