?
Solved

How to delete record with foreign keys?

Posted on 2008-10-26
9
Medium Priority
?
1,913 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

612 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