?
Solved

How to delete record with foreign keys?

Posted on 2008-10-26
9
Medium Priority
?
1,906 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

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!

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
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 …
Suggested Courses

765 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