1

Topic: Question about delete and fk

There is table A with field A.id
There is table B with fields id, header_id, a_id
Is FK B.a_id => A.id
It is necessary to delete records from A request of a type
delete from A where A.id in (select a_id from b where header_id=1);
update B set a_id=null where header_id=1;
But on delete works FK.
Whether it is possible such trivial task to solve SQL without temporary tables and PL/SQL?

2

Re: Question about delete and fk

Leonid Kudryavtsev wrote:

but on delete works FK.

SET NULL?

3

Re: Question about delete and fk

Leonid Kudryavtsev , on delete set null ?

4

Re: Question about delete and fk

It is clear that it is possible FK to make ON DELETE SET NULL
But the need looks painfully standard. I can that did not watch and it is possible purely SQL to represent?

5

Re: Question about delete and fk

DDL to change for performance of simple operation in a script - somehow from a gun on sparrows
To write PL/SQL a cycle - not a problem
Simply thought, can eat any artful commands of type MERGE))), and I dark do not know about them
To delete from several tables at once like it is possible, and here to delete and smooth out - did not find

6

Re: Question about delete and fk

Leonid Kudryavtsev wrote:

But the need looks painfully standard.

Aha. For SET NULL.

Leonid Kudryavtsev wrote:

I Can that did not watch and it is possible purely SQL to represent?

Preferring go the way can to try a trick :

update tab n set a_id = null where... returning (select o.a_id from tab o where o.rowid = a.rowid) bulk collect into...;
delete...;

7

Re: Question about delete and fk

Leonid Kudryavtsev wrote:

DDL to change for performance of simple operation in a script - somehow from a gun on sparrows

AND set constraint deferred ?

8

Re: Question about delete and fk

n0rd1c.c0ld wrote:

it is passed...
AND set constraint deferred ?

Nonsense wrote.

9

Re: Question about delete and fk

n0rd1c.c0ld wrote:

it is passed...
AND set constraint deferred ?

ORA-02447 cannot defer a constraint that is not deferrable
))) Everywhere a rake. Made on PL/SQL. But thanks for the information. Neither about ON DELETE SET NULL, nor about SET DEFERRED did not know

for c in (select....)
update...
delete.
end loop;

10

Re: Question about delete and fk

-2;
I can not understand what for IN?
update B set a_id=null where a_id in (select a_id from B where header_id=1);
.....
stax

11

Re: Question about delete and fk

Yes not what for, it is simple copy-past without looking)))

12

Re: Question about delete and fk

Stax wrote:

I can not understand what for IN?
update B set a_id=null where a_id in (select a_id from B where header_id=1);

compare in (... header_id = 1) and immediate header_id = 1 [CSV =,] id, a_id, header_id
101, 10, 0
101, 10, 1

13

Re: Question about delete and fk

two lines wrote:

it is passed...
Compare in (... header_id = 1) and immediate header_id = 1 [CSV =,] id, a_id, header_id
101, 10, 0
101, 10, 1

Clearly, couples missed (stepped)  the moment
Though, judging by the first post, whether still a question it is necessary  Leonid Kudryavtsev
....
stax