Каскадное удаление только один раз

Question

Каскадное удаление только один раз

У меня есть база данных Postgresql, на которой я хочу сделать несколько каскадных удалений. Однако таблицы не настроены с помощью правила on DELETE CASCADE. Есть ли способ, которым я могу выполнить удаление и сказать Postgresql каскадировать его только один раз? Что-то эквивалентное

DELETE FROM some_table CASCADE;

ответы на этот старый вопрос заставляют его казаться, что такого решения не существует, но я решил, что задам этот вопрос явно, чтобы быть уверенным.

445 8

postgresql

8 ответов:

Comments

Ничего не найдено.

palehorse · Accepted Answer · 2014-02-05 01:35:01

нет. Сделать это только один раз вы можете просто написать инструкцию delete для таблицы, которую вы хотите каскада.
DELETE FROM some_child_table WHERE some_fk_field IN (SELECT some_id FROM some_Table);
DELETE FROM some_table;

DanC · Accepted Answer · 2016-12-06 21:33:50

_{ИСПОЛЬЗУЙТЕ С ОСТОРОЖНОСТЬЮ - как указано в комментариях:"это отбросит все строки всех таблиц, которые имеют ограничение внешнего ключа на some_table и все таблицы, которые имеют ограничения на эти таблицы и т. д"}

на Postgres вы можете использовать усечь команду, предполагая, что вы не хотите указывать предложение WHERE:
TRUNCATE some_table CASCADE;
удобно это транзакционный (т. е. может быть откат), хотя это не полностью изолирован от других параллельных транзакций и имеет несколько других предостережений. Прочитайте документы для получения дополнительной информации.

Joe Love · Accepted Answer · 2016-03-11 18:05:16

Я написал (рекурсивную) функцию для удаления любой строки на основе ее первичного ключа. Я написал это, потому что я не хотел создавать свои ограничения как "на delete cascade". Я хотел иметь возможность удалять сложные наборы данных (как DBA), но не позволять моим программистам иметь возможность каскадного удаления, не думая обо всех последствиях. Я все еще тестирую эту функцию, поэтому в ней могут быть ошибки, но, пожалуйста, не пытайтесь, если ваша БД имеет несколько столбцов первичных (и, следовательно, внешних) ключей. Также, все ключи должны быть представлены в Строковой форме, но она может быть написана таким образом, что не имеет этого ограничения. Я использую эту функцию очень экономно в любом случае, я слишком ценю свои данные, чтобы включить каскадные ограничения на все. В основном эта функция передается в схеме, имени таблицы и первичном значении (в Строковой форме), и она начнет с поиска любых внешних ключей в этой таблице и гарантирует, что данные не существуют - если это так, она рекурсивно вызывает себя на найденном данные. Он использует массив данных, уже помеченных для удаления, чтобы предотвратить бесконечные циклы. Пожалуйста, проверьте его и дайте мне знать, как это работает для вас. Примечание: это немного медленно. Я называю это так: 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;

Ryszard Szopa · Accepted Answer · 2008-10-02 03:43:04

Если я правильно понимаю, вы должны быть в состоянии сделать то, что вы хотите, отбросив ограничение внешнего ключа, добавив новый (который будет каскадировать), делая свой материал и воссоздавая ограничение внешнего ключа ограничения.

например:

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

конечно, вы должны абстрагировать такие вещи в процедуру, ради вашего психического здоровья.

Grzegorz Grabek · Accepted Answer · 2017-07-20 11:45:24

Я не могу прокомментировать ответ palehorse, поэтому я добавил свой собственный ответ. Palehorse logick в порядке, но эффективность может быть плохой с большими наборами данных.
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;
это быстрее, если у вас есть индексы на столбцы и набор данных больше, чем несколько записей.

Grant Johnson · Accepted Answer · 2008-09-25 22:49:55

параметр Удалить с каскадом применяется только к таблицам с определенными внешними ключами. Если вы сделаете удаление, и он говорит, что вы не можете, потому что это нарушит ограничение внешнего ключа, каскад заставит его удалить оскорбительные строки.

Если вы хотите удалить связанные строки таким образом, вам нужно будет сначала определить внешние ключи. Кроме того, помните, что если вы явно не укажете ему начать транзакцию или не измените значения по умолчанию, он выполнит автоматическая фиксация, которая может быть очень трудоемкой для очистки.

atiruz · Accepted Answer · 2016-05-11 17:17:24

вы можете использовать для автоматизации этого, вы можете определить ограничение внешнего ключа с помощью ON DELETE CASCADE.
Я цитирую руководство по ограничениям внешнего ключа:

CASCADE указывает, что при удалении ссылочной строки, строка(ы) ссылка должна быть автоматически удалены.

Thomas C. G. de Vilhena · Accepted Answer · 2018-06-14 00:19:55

Я взял ответ Джо Лав и переписал его с помощью IN оператор с sub-selects вместо = чтобы сделать функцию быстрее (согласно предложению Хаббита):

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;