CASCADE supprimer une seule fois

_{Utilisez avec soin - comme indiqué dans les commentaires: "cela supprimera toutes les lignes de toutes les tables qui ont une contrainte de clé étrangère sur some_table et toutes les tables qui ont des contraintes sur ces tables, etc"}

Sur Postgres, vous pouvez utiliser la commande TRUNCATE , en supposant que vous ne vouliez pas spécifier une clause WHERE:

TRUNCATE some_table CASCADE;

Handily c'est transactionnel (c'est-à-dire peut être annulé), bien qu'il ne soit pas complètement isolé des autres transactions simultanées, et a plusieurs autres mises en garde. Lisez les documents pour plus de détails.

J'ai écrit une fonction (récursive) pour supprimer n'importe quelle ligne en fonction de sa clé primaire. J'ai écrit ceci parce que je ne voulais pas créer mes contraintes comme "ON delete cascade". Je voulais pouvoir supprimer des ensembles de données complexes (en tant que DBA) mais ne pas permettre à mes programmeurs de pouvoir supprimer en cascade sans réfléchir à toutes les répercussions. Je teste toujours Cette fonction, donc il peut y avoir des bugs-mais ne l'essayez pas si votre base de données a des clés primaires multi-colonnes (et donc étrangères). Également, les clés doivent toutes pouvoir être représentées sous forme de chaîne, mais elles peuvent être écrites d'une manière qui n'a pas cette restriction. J'utilise cette fonction avec parcimonie de toute façon, j'apprécie trop mes données pour activer les contraintes en cascade sur tout. Fondamentalement, cette fonction est passée dans le schéma, le nom de la table et la valeur primaire (sous forme de chaîne), et elle commencera par trouver des clés étrangères sur cette table et s'assurera que les données n'existent pas-si c'est le cas, elle s'appelle récursivement sur le trouvé données. Il utilise un tableau de données déjà marquées pour la suppression pour empêcher les boucles infinies. Merci de le tester et laissez-moi savoir comment cela fonctionne pour vous. Remarque: Il est un peu lent. Je l'appelle comme: 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;

Si je comprends bien, vous devriez être capable de faire ce que vous voulez en supprimant la contrainte de clé étrangère, en ajoutant une nouvelle (qui va tomber en cascade), en faisant vos trucs et en recréant la contrainte de clé étrangère restrictive.

Par exemple:

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

Bien sûr, vous devriez abstraire des choses comme ça dans une procédure, pour le bien de votre santé mentale.

Je ne peux pas commenter la réponse de palehorse alors j'ai ajouté ma propre réponse. Palehorse logick est ok mais l'efficacité peut être mauvaise avec les grands ensembles de données.

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;

C'est plus rapide si vous avez des index sur des colonnes et que l'ensemble de données est plus grand que quelques enregistrements.

L'option Supprimer avec l'option cascade s'applique uniquement aux tables avec des clés étrangères définies. Si vous effectuez une suppression, et qu'il est indiqué que vous ne pouvez pas, car cela violerait la contrainte de clé étrangère, la cascade l'obligera à supprimer les lignes incriminées.

Si vous souhaitez supprimer les lignes associées de cette manière, vous devrez d'abord définir les clés étrangères. Rappelez-vous également que, sauf si vous lui demandez explicitement de commencer une transaction, ou si vous modifiez les valeurs par défaut, il effectuera une validation automatique, qui cela pourrait prendre beaucoup de temps à nettoyer.

Vous pouvez utiliser pour automatiser cela, vous pouvez définir une contrainte de clé étrangère avec ON DELETE CASCADE.
Je cite le , le manuel des contraintes de clé étrangère:

CASCADE spécifie que lorsqu'une ligne référencée est supprimée,) le référencement devrait également être automatiquement supprimé.

J'ai pris la réponse de Joe Love et l'ai réécrite en utilisant l'opérateur IN avec des sous-sélections au lieu de = pour rendre la fonction plus rapide (selon la suggestion de 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;