캐스케이드 삭제

캐스케이드 삭제

---

계단식 삭제를 수행하려는 Postgresql 데이터베이스가 있습니다. 그러나 테이블은 ON DELETE CASCADE 규칙으로 설정되지 않습니다. 삭제를 수행하고 Postgresql에 단 한 번만 계단식으로 연결하도록 할 수있는 방법이 있습니까? 이에 상응하는 것

DELETE FROM some_table CASCADE;

이 오래된 질문에 대한 답변은 그러한 해결책이 존재하지 않는 것처럼 보이지만 확실하게하기 위해이 질문을 명시 적으로 요구할 것이라고 생각했습니다.

---

아니요. 캐스케이드하려는 테이블에 대해 delete 문을 작성하기 만하면됩니다.

DELETE FROM some_child_table WHERE some_fk_field IN (SELECT some_id FROM some_Table);
DELETE FROM some_table;

---

당신이 정말로 원하는 경우 DELETE FROM some_table CASCADE; 의미 " 테이블에서 모든 행을 제거some_table 당신이 사용할 수있는" TRUNCATE대신 DELETE하고 CASCADE항상 지원됩니다. 그러나 where절 과 함께 선택적 삭제를 사용하려면 TRUNCATE충분하지 않습니다.

사용과 CARE는 -이됩니다 모든 테이블의 모든 행 드롭 에 외래 키 제약 조건이 some_table등 해당 테이블에 제약이있는 모든 테이블을

포스트 그레스 지원 CASCADE과 TRUNCATE 명령 :

TRUNCATE some_table CASCADE;

이 기능은 다른 동시 트랜잭션과 완전히 분리되어 있지 않지만 다른 몇 가지주의 사항이 있지만 트랜잭션 방식 (즉, 롤백 가능)입니다. 자세한 내용은 문서를 읽으십시오.

---

기본 키를 기반으로 행을 삭제하는 (재귀) 함수를 작성했습니다. 캐스케이드를 삭제할 때 제약 조건을 만들고 싶지 않기 때문에 이것을 썼습니다. DBA로 복잡한 데이터 집합을 삭제하고 싶었지만 프로그래머가 모든 영향을 고려하지 않고 삭제를 계단식으로 만들 수는 없었습니다. 나는 여전히이 기능을 테스트하고 있으므로 버그가있을 수 있지만 DB에 다중 열 기본 (및 외래) 키가있는 경우 시도하지 마십시오. 또한 키는 모두 문자열 형식으로 표현할 수 있어야하지만 그러한 제한이없는 방식으로 작성 될 수 있습니다. 어쨌든이 기능을 매우 자주 사용하지 않습니다. 모든 데이터에 대해 계단식 제약 조건을 사용하기에 너무 많은 데이터를 중요하게 생각합니다. 기본적으로이 함수는 스키마, 테이블 이름 및 기본 값 (문자열 형식)으로 전달됩니다. 그리고 해당 테이블에서 외래 키를 찾아 시작하여 데이터가 존재하지 않는지 확인합니다. 존재하는 경우 발견 된 데이터를 재귀 적으로 호출합니다. 무한 루프를 방지하기 위해 이미 삭제 표시된 데이터 배열을 사용합니다. 그것을 테스트하고 그것이 당신을 위해 어떻게 작동하는지 알려주십시오. 참고 : 조금 느립니다. 나는 그렇게 그렇게 부른다.select delete_cascade('public','my_table','1');

create or replace function delete_cascade(p_schema varchar, p_table varchar, p_key varchar, p_recursion varchar[] default null)
 returns integer as $$
declare
    rx record;
    rd record;
    v_sql varchar;
    v_recursion_key varchar;
    recnum integer;
    v_primary_key varchar;
    v_rows integer;
begin
    recnum := 0;
    select ccu.column_name into v_primary_key
        from
        information_schema.table_constraints  tc
        join information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name and ccu.constraint_schema=tc.constraint_schema
        and tc.constraint_type='PRIMARY KEY'
        and tc.table_name=p_table
        and tc.table_schema=p_schema;

    for rx in (
        select kcu.table_name as foreign_table_name, 
        kcu.column_name as foreign_column_name, 
        kcu.table_schema foreign_table_schema,
        kcu2.column_name as foreign_table_primary_key
        from information_schema.constraint_column_usage ccu
        join information_schema.table_constraints tc on tc.constraint_name=ccu.constraint_name and tc.constraint_catalog=ccu.constraint_catalog and ccu.constraint_schema=ccu.constraint_schema 
        join information_schema.key_column_usage kcu on kcu.constraint_name=ccu.constraint_name and kcu.constraint_catalog=ccu.constraint_catalog and kcu.constraint_schema=ccu.constraint_schema
        join information_schema.table_constraints tc2 on tc2.table_name=kcu.table_name and tc2.table_schema=kcu.table_schema
        join information_schema.key_column_usage kcu2 on kcu2.constraint_name=tc2.constraint_name and kcu2.constraint_catalog=tc2.constraint_catalog and kcu2.constraint_schema=tc2.constraint_schema
        where ccu.table_name=p_table  and ccu.table_schema=p_schema
        and TC.CONSTRAINT_TYPE='FOREIGN KEY'
        and tc2.constraint_type='PRIMARY KEY'
)
    loop
        v_sql := 'select '||rx.foreign_table_primary_key||' as key from '||rx.foreign_table_schema||'.'||rx.foreign_table_name||'
            where '||rx.foreign_column_name||'='||quote_literal(p_key)||' for update';
        --raise notice '%',v_sql;
        --found a foreign key, now find the primary keys for any data that exists in any of those tables.
        for rd in execute v_sql
        loop
            v_recursion_key=rx.foreign_table_schema||'.'||rx.foreign_table_name||'.'||rx.foreign_column_name||'='||rd.key;
            if (v_recursion_key = any (p_recursion)) then
                --raise notice 'Avoiding infinite loop';
            else
                --raise notice 'Recursing to %,%',rx.foreign_table_name, rd.key;
                recnum:= recnum +delete_cascade(rx.foreign_table_schema::varchar, rx.foreign_table_name::varchar, rd.key::varchar, p_recursion||v_recursion_key);
            end if;
        end loop;
    end loop;
    begin
    --actually delete original record.
    v_sql := 'delete from '||p_schema||'.'||p_table||' where '||v_primary_key||'='||quote_literal(p_key);
    execute v_sql;
    get diagnostics v_rows= row_count;
    --raise notice 'Deleting %.% %=%',p_schema,p_table,v_primary_key,p_key;
    recnum:= recnum +v_rows;
    exception when others then recnum=0;
    end;

    return recnum;
end;
$$
language PLPGSQL;

---

올바르게 이해하면 외래 키 제약 조건을 삭제하고 새 캐스케이드 추가 (연쇄), 작업 수행 및 제한 외래 키 제약 조건을 다시 작성하여 원하는 것을 수행 할 수 있어야합니다.

예를 들면 다음과 같습니다.

testing=# create table a (id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
testing=# create table b (id integer references a);
CREATE TABLE

-- put some data in the table
testing=# insert into a values(1);
INSERT 0 1
testing=# insert into a values(2);
INSERT 0 1
testing=# insert into b values(2);
INSERT 0 1
testing=# insert into b values(1);
INSERT 0 1

-- restricting works
testing=# delete from a where id=1;
ERROR:  update or delete on table "a" violates foreign key constraint "b_id_fkey" on table "b"
DETAIL:  Key (id)=(1) is still referenced from table "b".

-- find the name of the constraint
testing=# \d b;
       Table "public.b"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
Foreign-key constraints:
    "b_id_fkey" FOREIGN KEY (id) REFERENCES a(id)

-- drop the constraint
testing=# alter table b drop constraint b_a_id_fkey;
ALTER TABLE

-- create a cascading one
testing=# alter table b add FOREIGN KEY (id) references a(id) on delete cascade; 
ALTER TABLE

testing=# delete from a where id=1;
DELETE 1
testing=# select * from a;
 id 
----
  2
(1 row)

testing=# select * from b;
 id 
----
  2
(1 row)

-- it works, do your stuff.
-- [stuff]

-- recreate the previous state
testing=# \d b;
       Table "public.b"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
Foreign-key constraints:
    "b_id_fkey" FOREIGN KEY (id) REFERENCES a(id) ON DELETE CASCADE

testing=# alter table b drop constraint b_id_fkey;
ALTER TABLE
testing=# alter table b add FOREIGN KEY (id) references a(id) on delete restrict; 
ALTER TABLE

물론 정신 건강을 위해 그런 것들을 절차로 요약해야합니다.

---

Palehorse의 답변에 댓글을 달 수 없으므로 본인의 답변을 추가했습니다. Palehorse의 논리는 괜찮지 만 빅 데이터 세트에서는 효율성이 떨어질 수 있습니다.

DELETE FROM some_child_table sct WHERE exists  (SELECT FROM some_Table st 
where sct.some_fk_fiel=st.some_id );
DELETE FROM some_table;

열에 대한 색인이 있고 데이터 세트가 소수의 레코드보다 큰 경우 더 빠릅니다.

---

이를 자동화하는 데 사용할 수 있으며로 외래 키 제약 조건을 정의 할 수 ON DELETE CASCADE있습니다. 외래 키 제약 설명서를
인용하십시오 .

CASCADE 참조 된 행이 삭제 될 때이를 참조하는 행도 자동으로 삭제되도록 지정합니다.

---

I took Joe Love's answer and rewrote it using the IN operator with sub-selects instead of = to make the function faster (according to Hubbitus's suggestion):

create or replace function delete_cascade(p_schema varchar, p_table varchar, p_keys varchar, p_subquery varchar default null, p_foreign_keys varchar[] default array[]::varchar[])
 returns integer as $$
declare

    rx record;
    rd record;
    v_sql varchar;
    v_subquery varchar;
    v_primary_key varchar;
    v_foreign_key varchar;
    v_rows integer;
    recnum integer;

begin

    recnum := 0;
    select ccu.column_name into v_primary_key
        from
        information_schema.table_constraints  tc
        join information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name and ccu.constraint_schema=tc.constraint_schema
        and tc.constraint_type='PRIMARY KEY'
        and tc.table_name=p_table
        and tc.table_schema=p_schema;

    for rx in (
        select kcu.table_name as foreign_table_name, 
        kcu.column_name as foreign_column_name, 
        kcu.table_schema foreign_table_schema,
        kcu2.column_name as foreign_table_primary_key
        from information_schema.constraint_column_usage ccu
        join information_schema.table_constraints tc on tc.constraint_name=ccu.constraint_name and tc.constraint_catalog=ccu.constraint_catalog and ccu.constraint_schema=ccu.constraint_schema 
        join information_schema.key_column_usage kcu on kcu.constraint_name=ccu.constraint_name and kcu.constraint_catalog=ccu.constraint_catalog and kcu.constraint_schema=ccu.constraint_schema
        join information_schema.table_constraints tc2 on tc2.table_name=kcu.table_name and tc2.table_schema=kcu.table_schema
        join information_schema.key_column_usage kcu2 on kcu2.constraint_name=tc2.constraint_name and kcu2.constraint_catalog=tc2.constraint_catalog and kcu2.constraint_schema=tc2.constraint_schema
        where ccu.table_name=p_table  and ccu.table_schema=p_schema
        and TC.CONSTRAINT_TYPE='FOREIGN KEY'
        and tc2.constraint_type='PRIMARY KEY'
)
    loop
        v_foreign_key := rx.foreign_table_schema||'.'||rx.foreign_table_name||'.'||rx.foreign_column_name;
        v_subquery := 'select "'||rx.foreign_table_primary_key||'" as key from '||rx.foreign_table_schema||'."'||rx.foreign_table_name||'"
             where "'||rx.foreign_column_name||'"in('||coalesce(p_keys, p_subquery)||') for update';
        if p_foreign_keys @> ARRAY[v_foreign_key] then
            --raise notice 'circular recursion detected';
        else
            p_foreign_keys := array_append(p_foreign_keys, v_foreign_key);
            recnum:= recnum + delete_cascade(rx.foreign_table_schema, rx.foreign_table_name, null, v_subquery, p_foreign_keys);
            p_foreign_keys := array_remove(p_foreign_keys, v_foreign_key);
        end if;
    end loop;

    begin
        if (coalesce(p_keys, p_subquery) <> '') then
            v_sql := 'delete from '||p_schema||'."'||p_table||'" where "'||v_primary_key||'"in('||coalesce(p_keys, p_subquery)||')';
            --raise notice '%',v_sql;
            execute v_sql;
            get diagnostics v_rows = row_count;
            recnum := recnum + v_rows;
        end if;
        exception when others then recnum=0;
    end;

    return recnum;

end;
$$
language PLPGSQL;

---

The delete with the cascade option only applied to tables with foreign keys defined. If you do a delete, and it says you cannot because it would violate the foreign key constraint, the cascade will cause it to delete the offending rows.

If you want to delete associated rows in this way, you will need to define the foreign keys first. Also, remember that unless you explicitly instruct it to begin a transaction, or you change the defaults, it will do an auto-commit, which could be very time consuming to clean up.

참고URL : https://stackoverflow.com/questions/129265/cascade-delete-just-once