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
Solved

How to delete record with foreign keys?

Posted on 2008-10-26
9
1,898 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

839 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