한 번만 캐스케이드 삭제
Postgresql 데이터베이스가 있어서 몇 가지 단계적 삭제를 수행할 수 있습니다.그러나 테이블이 ON DELETE CASCADE 규칙으로 설정되지 않았습니다.제가 삭제를 수행하고 Postgresql에게 이번 한 번만 캐스케이드하라고 말할 수 있는 방법이 있습니까?와 동등한 것.
DELETE FROM some_table CASCADE;
이 오래된 질문에 대한 대답은 그러한 해결책이 존재하지 않는 것처럼 보이게 하지만, 저는 확신을 갖기 위해 이 질문을 분명히 하고 싶습니다.
아니요. 한 번만 수행하려면 캐스케이드할 테이블에 대한 삭제 문을 작성하기만 하면 됩니다.
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;즉, "테이블에서 모든 행 제거"를 의미합니다.TRUNCATE에 DELETE그리고.CASCADE는 항상 지원됩니다., 를 삭나선사제와 where 절,TRUNCATE충분하지 않습니다.
주의하여 사용 - 외래 키 제약 조건이 있는 모든 테이블의 모든 행이 삭제됩니다.some_table그리고 해당 테이블에 제약 조건이 있는 모든 테이블 등.
는 포스레지를 지원합니다.CASCADETRUNCATE 명령 사용:
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
물론, 여러분의 정신 건강을 위해서 그런 것들을 절차로 추상화해야 합니다.
네, 다른 사람들이 말했듯이 편리한 'DELETE FROM my_table...'은 없습니다.CASCADE'(또는 동등).캐스케이드되지 않은 외부 키로 보호된 하위 레코드와 참조된 상위 레코드를 삭제하는 옵션은 다음과 같습니다.
- 하위 테이블부터 시작하여 모든 삭제를 한 번에 하나씩 명시적으로 수행합니다(원형 참조가 있는 경우에는 이 쿼리가 실행되지 않습니다). 또는
- 모든 삭제를 단일(대규모일 수 있음) 쿼리에서 명시적으로 수행합니다.
- 캐스케이드되지 않은 외부 키 제약 조건이 'ON DELETE NO ACTION DERERRABLE'로 생성되었다고 가정하면 모든 삭제를 한 번의 트랜잭션으로 명시적으로 수행합니다.
- 그래프에서 '무 조치' 및 '제한' 외래 키 제약 조건을 일시적으로 삭제하고, CASCADE로 다시 생성한 다음, 문제가 되는 상위 키 제약 조건을 삭제하고, 외래 키 제약 조건을 다시 삭제하고, 마지막으로 원래대로 다시 생성합니다(따라서 일시적으로 데이터의 무결성이 약화됨). 또는
- 아마 똑같이 재미있는 것일 겁니다.
외부 키 제약을 우회하는 것이 편리하지 않다는 것은 의도적인 것이라고 생각합니다. 하지만 특정 상황에서 당신이 그것을 하고 싶어하는 이유를 이해합니다.어느 정도의 빈도로 작업을 수행하고 모든 DBA의 지혜를 무시할 의사가 있다면 절차를 통해 자동화하는 것이 좋습니다.
몇 달 전에 "CASCADE DELETE once" 질문(원래는 10년 전에 질문)에 대한 답을 찾으러 왔습니다.저는 Joe Love의 영리한 솔루션(그리고 Thomas C. G. de Vilhena의 변형)에서 약간의 마일리지를 얻었지만, 결국 제 사용 사례에는 다른 접근 방식을 취하도록 강요하는 특정 요구 사항(테이블 내 순환 참조 처리)이 있었습니다.이러한 접근 방식은 결국 재귀적_삭제(10.10페이지)가 되었습니다.
저는 한동안 프로덕션에서 재귀적으로_delete를 사용해 왔으며, 마침내 아이디어를 찾는 다른 사람들이 사용할 수 있을 정도로 자신감을 갖게 되었습니다.Joe Love의 솔루션과 마찬가지로 데이터베이스의 모든 외부 키 제약 조건이 일시적으로 CASCADE로 설정된 것처럼 전체 데이터 그래프를 삭제할 수 있지만 다음과 같은 몇 가지 추가 기능을 제공합니다.
- 삭제 대상과 해당 종속자 그래프에 대한 ASCII 미리 보기를 제공합니다.
- 재귀 CTE를 사용하여 단일 쿼리에서 삭제를 수행합니다.
- 테이블 내 및 테이블 간의 순환 종속성을 처리합니다.
- 복합 키를 처리합니다.
- '기본값 설정' 및 'null 설정' 제약 조건을 건너뜁니다.
저는 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참조된 행을 삭제할 때 해당 행을 참조하는 행도 자동으로 삭제되도록 지정합니다.
나는 조 러브의 대답을 받아 그것을 다시 썼습니다.IN대신 하위 변수가 있는 연산자=(Hubbitus의 제안에 따라) 기능을 더 빠르게 하기 위해:
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;
캐스케이드 옵션을 사용한 삭제는 외부 키가 정의된 테이블에만 적용됩니다.삭제를 수행하면 외부 키 제약 조건을 위반하므로 삭제할 수 없다고 표시되면 캐스케이드로 인해 위반 행이 삭제됩니다.
이런 방식으로 연결된 행을 삭제하려면 먼저 외부 키를 정의해야 합니다.또한 트랜잭션을 시작하도록 명시적으로 지시하거나 기본값을 변경하지 않으면 자동 커밋이 수행되므로 정리하는 데 시간이 많이 걸릴 수 있습니다.
새 테이블을 만들 때 UNIKEY 또는 NOT NULL과 같은 제약 조건을 추가할 수 있으며, 다른 테이블에 참조가 있는 행을 삭제하려고 할 때 수행해야 하는 작업을 SQL에 표시할 수도 있습니다.
CREATE TABLE company (
id SERIAL PRIMARY KEY,
name VARCHAR(128),
year DATE);
CREATE TABLE employee (
id SERIAL PRIMARY KEY,
first_name VARCHAR(128) NOT NULL,
last_name VARCHAR(128) NOT NULL,
company_id INT REFERENCES company(id) ON DELETE CASCADE,
salary INT,
UNIQUE (first_name, last_name));
그런 다음 필요한 행만 삭제할 수 있습니다. 예를 들어 다음과 같습니다.
DELETE
FROM company
WHERE id = 2;
언급URL : https://stackoverflow.com/questions/129265/cascade-delete-just-once
'sourcetip' 카테고리의 다른 글
| C/C++에서 포인터 '디레퍼런스'는 무엇을 의미합니까? (0) | 2023.05.08 |
|---|---|
| Postgre가 있습니까?SQL Server 프로파일러와 동등한 SQL? (0) | 2023.05.08 |
| MongoDB는 쓰기를 어느 수준까지 잠급니까?(또는: "연결당"은 무엇을 의미합니까? (0) | 2023.05.08 |
| Git-svn은 하위 버전 저장소에서 마지막 n개의 리비전을 복제하는 방법은 무엇입니까? (0) | 2023.05.08 |
| 목록의 각 요소에 숫자를 곱하려면 어떻게 해야 합니까? (0) | 2023.05.08 |