postgresql - ¿Cómo agregar restricciones de "on delete cascade"?
constraints cascading-deletes (2)
Estoy bastante seguro de que no puede simplemente agregar on delete cascade a una restricción de clave externa existente. Primero debe soltar la restricción y luego agregar la versión correcta. En SQL estándar, creo que la forma más fácil de hacerlo es
- comenzar una transacción,
- soltar la clave externa,
- agregar una clave foránea
on delete cascade, y finalmente - comprometer la transacción
Repita para cada tecla externa que quiera cambiar.
Pero PostgreSQL tiene una extensión no estándar que le permite usar múltiples cláusulas de restricción en una sola instrucción SQL. Por ejemplo
alter table public.pref_scores
drop constraint pref_scores_gid_fkey,
add constraint pref_scores_gid_fkey
foreign key (gid)
references pref_games(gid)
on delete cascade;
Si no conoce el nombre de la restricción de clave externa que desea eliminar, puede buscarla en pgAdminIII (simplemente haga clic en el nombre de la tabla y mire el DDL, o expanda la jerarquía hasta que vea "Restricciones"), o puede consultar el esquema de información .
select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null
En PostgreSQL 8, ¿es posible agregar "on delete cascades" a las dos claves foráneas en la siguiente tabla sin descartar este último?
# /d pref_scores
Table "public.pref_scores"
Column | Type | Modifiers
---------+-----------------------+-----------
id | character varying(32) |
gid | integer |
money | integer | not null
quit | boolean |
last_ip | inet |
Foreign-key constraints:
"pref_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES pref_games(gid)
"pref_scores_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
Ambas tablas referenciadas están debajo - aquí:
# /d pref_games
Table "public.pref_games"
Column | Type | Modifiers
----------+-----------------------------+----------------------------------------------------------
gid | integer | not null default nextval(''pref_games_gid_seq''::regclass)
rounds | integer | not null
finished | timestamp without time zone | default now()
Indexes:
"pref_games_pkey" PRIMARY KEY, btree (gid)
Referenced by:
TABLE "pref_scores" CONSTRAINT "pref_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES pref_games(gid)
Y aquí:
# /d pref_users
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
first_name | character varying(64) |
last_name | character varying(64) |
female | boolean |
avatar | character varying(128) |
city | character varying(64) |
login | timestamp without time zone | default now()
last_ip | inet |
logout | timestamp without time zone |
vip | timestamp without time zone |
mail | character varying(254) |
Indexes:
"pref_users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "pref_cards" CONSTRAINT "pref_cards_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_catch" CONSTRAINT "pref_catch_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_chat" CONSTRAINT "pref_chat_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_game" CONSTRAINT "pref_game_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_hand" CONSTRAINT "pref_hand_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_match" CONSTRAINT "pref_match_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_payment" CONSTRAINT "pref_payment_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_rep" CONSTRAINT "pref_rep_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id)
TABLE "pref_rep" CONSTRAINT "pref_rep_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_scores" CONSTRAINT "pref_scores_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
TABLE "pref_status" CONSTRAINT "pref_status_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
Y también me pregunto si tiene sentido agregar 2 índices a la tabla anterior.
ACTUALIZACIÓN: Gracias, y también tengo el consejo en la lista de correo, que podría administrarlo en 1 estado y así no necesitaría una transacción:
ALTER TABLE public.pref_scores
DROP CONSTRAINT pref_scores_gid_fkey,
ADD CONSTRAINT pref_scores_gid_fkey
FOREIGN KEY (gid)
REFERENCES pref_games(gid)
ON DELETE CASCADE;
Uso:
select replace_foreign_key(''user_rates_posts'', ''post_id'', ''ON DELETE CASCADE'');
Función:
CREATE OR REPLACE FUNCTION
replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR)
RETURNS VARCHAR
AS $$
DECLARE constraint_name varchar;
DECLARE reftable varchar;
DECLARE refcolumn varchar;
BEGIN
SELECT tc.constraint_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = ''FOREIGN KEY''
AND tc.table_name= f_table AND kcu.column_name= f_column
INTO constraint_name, reftable, refcolumn;
EXECUTE ''alter table '' || f_table || '' drop constraint '' || constraint_name ||
'', ADD CONSTRAINT '' || constraint_name || '' FOREIGN KEY ('' || f_column || '') '' ||
'' REFERENCES '' || reftable || ''('' || refcolumn || '') '' || new_options || '';'';
RETURN ''Constraint replaced: '' || constraint_name || '' ('' || f_table || ''.'' || f_column ||
'' -> '' || reftable || ''.'' || refcolumn || ''); New options: '' || new_options;
END;
$$ LANGUAGE plpgsql;
Tenga en cuenta que esta función no copiará los atributos de la clave externa inicial. Solo toma el nombre de la tabla / nombre de columna foránea, descarta la clave actual y la reemplaza por una nueva.