references - relations postgresql

¿Cómo desactivo la integridad referencial en Postgres 8.2?

Question

Los resultados de Google en este son un poco delgados, pero sugieren que no es fácilmente posible.

Mi problema específico es que necesito volver a numerar los ID en dos tablas que están relacionadas entre sí de manera que la tabla B tenga una columna "table_a_id". No puedo volver a numerar la tabla A porque luego sus hijos en B apuntan a las identificaciones anteriores. No puedo volver a numerar la tabla B porque entonces apuntarían a las nuevas ID antes de que se crearan. Ahora repite para tres o cuatro tablas.

Realmente no quiero tener que jugar con las relaciones individuales cuando podría simplemente "iniciar la transacción, deshabilitar la integridad del ref, ordenar los ID, volver a habilitar la integridad del ref, comprometer la transacción". Mysql y MSSQL proporcionan esta funcionalidad IIRC, así que me sorprendería si Postgres no lo hiciera.

¡Gracias!

Answer 1

Aquí hay una secuencia de comandos de Python que eliminará todas las restricciones en una transacción, ejecutará algunas consultas y luego volverá a crear todas esas restricciones. pg_get_constraintdef hace esto súper fácil:

class no_constraints(object):
def __init__(self, connection):
self.connection = connection
def __enter__(self):
self.transaction = self.connection.begin()
try:
self._drop_constraints()
except:
self.transaction.rollback()
raise
def __exit__(self, exc_type, exc_value, traceback):
if exc_type is not None:
self.transaction.rollback()
else:
try:
self._create_constraints()
self.transaction.commit()
except:
self.transaction.rollback()
raise
def _drop_constraints(self):
self._constraints = self._all_constraints()
for schemaname, tablename, name, def_ in self._constraints:
self.connection.execute(''ALTER TABLE "%s.%s" DROP CONSTRAINT %s'' % (schemaname, tablename, name))
def _create_constraints(self):
for schemaname, tablename, name, def_ in self._constraints:
self.connection.execute(''ALTER TABLE "%s.%s" ADD CONSTRAINT %s %s'' % (schamename, tablename, name, def_))
def _all_constraints(self):
return self.connection.execute("""
SELECT n.nspname AS schemaname, c.relname, conname, pg_get_constraintdef(r.oid, false) as condef
FROM pg_constraint r, pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE r.contype = ''f''
and r.conrelid=c.oid
""").fetchall()
if __name__ == ''__main__'':
# example usage
from sqlalchemy import create_engine
engine = create_engine(''postgresql://user:pass@host/dbname'', echo=True)
conn = engine.connect()
with no_contraints(conn):
r = conn.execute("delete from table1")
print "%d rows affected" % r.rowcount
r = conn.execute("delete from table2")
print "%d rows affected" % r.rowcount

Answer 2

Creo que necesita hacer una lista de sus restricciones de clave externa, soltarlas, hacer los cambios, y luego agregar las restricciones nuevamente. Compruebe la documentación para ver la alter table drop constraint alter table add constraint y alter table add constraint .

Answer 3

Creo que una solución fácil sería crear columnas "temporales" asociando donde quieres que estén.

actualizar los valores con las claves externas a las nuevas columnas

soltar las columnas iniciales

cambie el nombre a las nuevas columnas "temporales" con los mismos nombres que los iniciales.

Answer 4

Encontré estos 2 excelentes scripts que generan el sql para eliminar las restricciones y luego volver a crearlas. aquí están:

Para eliminar las restricciones

SELECT ''ALTER TABLE "''||nspname||''"."''||relname||''" DROP CONSTRAINT "''||conname||''";''
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype=''f'' THEN 0 ELSE 1 END,contype,nspname,relname,conname

Para recrearlos

SELECT ''ALTER TABLE "''||nspname||''"."''||relname||''" ADD CONSTRAINT "''||conname||''" ''|| pg_get_constraintdef(pg_constraint.oid)||'';''
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype=''f'' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC;

Ejecute estas consultas y la salida serán los scripts sql que necesita para descartar y crear las restricciones.

Una vez que suelta las restricciones, puede hacer todo lo que quiera con las tablas. Cuando termines, vuelve a introducirlos.

Answer 5

Hay dos cosas que puedes hacer (estas son complementarias, no alternativas):

• Cree sus restricciones de clave externa como DEFERRABLE. Luego, llame a "SET CONSTRAINTS DEFERRED;", lo que ocasionará que las restricciones de clave externa no se verifiquen hasta el final de la transacción. Tenga en cuenta que el valor predeterminado si no especifica nada NO ES DEFERENTE (molestamente).
• Llame a "ALTER TABLE mytable DISABLE TRIGGER ALL;", que evita que se ejecuten disparadores mientras carga datos, luego "ALTER TABLE mytable ENABLE TRIGGER ALL;" cuando haya terminado de volver a habilitarlos.

Answer 6

No parece posible. Otras sugerencias casi siempre se refieren a eliminar las restricciones y volver a crearlas después de que se haya terminado el trabajo.

Sin embargo, parece que puede hacer que las restricciones sean DEFERRABLE , de modo que no se verifiquen hasta el final de una transacción. Consulte la documentación de PostgreSQL para CREATE TABLE (busque "diferible", está en el medio de la página).

Answer 7

Si las restricciones son DEFERRABLE , esto es realmente fácil. Solo use un bloque de transacción y establezca sus restricciones FK para diferir al comienzo de la transacción.

De http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html :

SET CONSTRAINTS establece el comportamiento de la comprobación de restricciones dentro de la transacción actual. Las restricciones INMEDIATAS se verifican al final de cada declaración. Las restricciones DIFERIDAS no se verifican hasta la confirmación de la transacción.

Entonces podrías hacer:

BEGIN;
SET CONSTRAINTS
table_1_parent_id_foreign,
table_2_parent_id_foreign,
-- etc
DEFERRED;
-- do all your renumbering
COMMIT;

Desafortunadamente, parece que Postgres predetermina todas las restricciones a NOT DEFERRABLE , a menos que DEFERRABLE se establezca explícitamente. (Supongo que esto es por motivos de rendimiento, pero no estoy seguro). A partir de Postgres 9.4, no es demasiado difícil alterar las restricciones para hacerlas diferibles si es necesario:

ALTER TABLE table_1 ALTER CONSTRAINT table_1_parent_id_foreign DEFERRABLE;

(Ver http://www.postgresql.org/docs/9.4/static/sql-altertable.html .)

Creo que este enfoque sería preferible a descartar y volver a crear sus restricciones como algunos han descrito, o desactivar todos los activadores (o todos los usuarios) hasta el final de la transacción, que requiere privilegios de superusuario, como se señaló en un comentario anterior de @clapas .