postgresql - CASCADE DELETE solo una vez

delete in cascade postgresql

Question

Tengo una base de datos Postgresql en la que quiero hacer algunas eliminaciones en cascada. Sin embargo, las tablas no están configuradas con la regla ON DELETE CASCADE. ¿Hay alguna manera de que pueda realizar una eliminación y decirle a Postgresql que lo haga en cascada solo por esta vez? Algo equivalente a

DELETE FROM some_table CASCADE;

Las respuestas a esta pregunta anterior hacen que parezca que no existe tal solución, pero pensé que haría esta pregunta explícitamente solo para estar seguro.

Answer 1

Escribí una función (recursiva) para eliminar cualquier fila en función de su clave principal. Escribí esto porque no quería crear mis restricciones como "en cascada de eliminación". Quería poder eliminar conjuntos complejos de datos (como un DBA) pero no permitir que mis programadores puedan eliminar en cascada sin pensar en todas las repercusiones. Todavía estoy probando esta función, por lo que puede haber errores en ella, pero por favor no intente si su DB tiene claves primarias de múltiples columnas (y por lo tanto extranjeras). Además, todas las claves deben poder representarse en forma de cadena, pero podrían escribirse de una manera que no tenga esa restricción. Utilizo esta función MUY ESPERAMENTE, de todos modos, valoro demasiado mis datos para permitir las restricciones en cascada en todo. Básicamente, esta función se pasa en el esquema, el nombre de la tabla y el valor primario (en forma de cadena), y se iniciará al encontrar las claves externas en esa tabla y se asegura de que los datos no existan. Si lo hace, llama de manera recursiva sí mismo en los datos encontrados. Utiliza una matriz de datos ya marcados para su eliminación para evitar bucles infinitos. Por favor pruébelo y hágamelo saber cómo funciona para usted. Nota: es un poco lento. Lo llamo así: 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;

Answer 2

La opción de eliminar con la cascada solo se aplica a las tablas con claves foráneas definidas. Si realiza una eliminación y dice que no puede hacerlo porque violaría la restricción de clave externa, la cascada hará que elimine las filas ofensivas.

Si desea eliminar filas asociadas de esta manera, primero deberá definir las claves foráneas. Además, recuerde que, a menos que le indique explícitamente que comience una transacción, o cambie los valores predeterminados, se realizará un autocompromiso, lo que podría tomar mucho tiempo en limpiar.

Answer 3

No puedo comentar la respuesta de Palehorse, así que agregué mi propia respuesta. Palehorse logick está bien, pero la eficacia puede ser mala con los grandes conjuntos de datos.

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;

Es más rápido si tiene índices en columnas y el conjunto de datos es más grande que pocos registros.

Answer 4

No. Para hacerlo solo una vez, simplemente escribiría la declaración de eliminación para la tabla que desea conectar en cascada.

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

Answer 5

Puede usarlo para automatizar esto, puede definir la restricción de clave externa con ON DELETE CASCADE .
Cito el manual de restricciones de clave externa :

CASCADE especifica que cuando se elimina una fila referenciada, las filas que hacen referencia a ella también deben eliminarse automáticamente.

Answer 6

Si lo entiendo correctamente, debería poder hacer lo que quiera descartando la restricción de la clave externa, agregando una nueva (que irá en cascada), haciendo sus cosas y recreando la restricción de la restricción de la clave externa.

Por ejemplo:

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

Por supuesto, debe abstraer cosas así en un procedimiento, por el bien de su salud mental.

Answer 7

_{USO CON CUIDADO - Como se señala en los comentarios: " Esto eliminará todas las filas de todas las tablas que tengan una restricción de clave externa en some_table y todas las tablas que tengan restricciones en esas tablas, etc. "}

En Postgres puede usar el comando TRUNCATE , suponiendo que no desee especificar una cláusula WHERE:

TRUNCATE some_table CASCADE;

Prácticamente esto es transaccional (es decir, se puede retrotraer), aunque no está completamente aislado de otras transacciones simultáneas, y tiene varias otras advertencias. Lea los documentos para más detalles.