Compleja restricción de key externa en SQLAlchemy

Tengo dos tablas, SystemVariables y VariableOptions . SystemVariables debe ser autoexplicativo, y VariableOptions contiene todas las opciones posibles para todas las variables.

VariableOptions tiene una key foránea, variable_id , que indica para qué variable es una opción. SystemVariables tiene una key externa, choice_id , que indica qué opción es la seleccionada actualmente.

He use_alter relación circular usando use_alter en choice_id y post_update en la relación de choice SystemVariables . Sin embargo, me gustaría agregar una restricción de database adicional que garantizará que choice_id sea ​​válido (es decir, se refiere a una opción que hace reference a él).

La lógica que necesito, suponiendo que sysVar represente una fila en la tabla SystemVariables , es básicamente:

 VariableOptions[sysVar.choice_id].variable_id == sysVar.id 

Pero no sé cómo build este tipo de restricciones usando SQL, declarativo o cualquier otro método. De ser necesario, podría validar esto a nivel de aplicación, pero me gustaría tenerlo en la database si es posible. Estoy usando Postgres 9.1.

es posible?

Puedes implementar eso sin trucos sucios . Simplemente extienda la key foránea que hace reference a la opción elegida para include variable_id además de choice_id .

Aquí hay una demostración funcional. Tablas temporales, para que pueda jugar fácilmente con ella:

 CREATE TEMP TABLE systemvariables ( variable_id integer PRIMARY KEY , variable text , choice_id integer ); INSERT INTO systemvariables(variable_id, variable) VALUES (1, 'var1') , (2, 'var2') , (3, 'var3'); CREATE TEMP TABLE variableoptions ( option_id integer PRIMARY KEY , option text , variable_id integer REFERENCES systemvariables(variable_id) ON UPDATE CASCADE ON DELETE CASCADE , UNIQUE (option_id, variable_id) -- needed for the foreign key ); ALTER TABLE systemvariables ADD CONSTRAINT systemvariables_choice_id_fk FOREIGN KEY (choice_id, variable_id) REFERENCES variableoptions(option_id, variable_id); INSERT INTO variableoptions VALUES (1, 'var1_op1', 1) , (2, 'var1_op2', 1) , (3, 'var1_op3', 1) , (4, 'var2_op1', 2) , (5, 'var2_op2', 2) , (6, 'var3_op1', 3); 

Elegir una opción asociada está permitido:

 UPDATE systemvariables SET choice_id = 2 WHERE variable_id = 1; UPDATE systemvariables SET choice_id = 5 WHERE variable_id = 2; UPDATE systemvariables SET choice_id = 6 WHERE variable_id = 3; 

Pero no hay forma de salirse de la línea:

 UPDATE systemvariables SET choice_id = 7 WHERE variable_id = 3; UPDATE systemvariables SET choice_id = 4 WHERE variable_id = 1; 
 ERROR: insert or update on table "systemvariables" violates foreign key constraint "systemvariables_choice_id_fk" DETAIL: Key (choice_id,variable_id)=(4,1) is not present in table "variableoptions". 

Voilá . Exactamente lo que querías


Todas las columnas key NO NULL

Creo que encontré una mejor solución en esta respuesta posterior:

  • Cómo lidiar con insertos mutuamente recursivos

Al abordar la pregunta de @ ypercube en los comentarios , para evitar las inputs con una asociación desconocida, todas las columnas key NOT NULL SON NULAS , incluidas las keys externas.

La dependencia circular normalmente lo haría imposible. Es el problema clásico del huevo de gallina : uno de los dos tiene que estar allí primero para engendrar el otro. Pero la naturaleza encontró una forma de evitarlo, y también lo hizo Postgres: restricciones de keys extranjeras diferibles .

 CREATE TEMP TABLE systemvariables ( variable_id integer PRIMARY KEY , variable text , choice_id integer NOT NULL ); CREATE TEMP TABLE variableoptions ( option_id integer PRIMARY KEY , option text , variable_id integer NOT NULL REFERENCES systemvariables(variable_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED , UNIQUE (option_id, variable_id) -- needed for the foreign key ); ALTER TABLE systemvariables ADD CONSTRAINT systemvariables_choice_id_fk FOREIGN KEY (choice_id, variable_id) REFERENCES variableoptions(option_id, variable_id) DEFERRABLE INITIALLY DEFERRED; -- no CASCADING here! 

Las nuevas variables y las opciones asociadas deben insertse en la misma transacción:

 BEGIN; INSERT INTO systemvariables (variable_id, variable, choice_id) VALUES (1, 'var1', 2) , (2, 'var2', 5) , (3, 'var3', 6); INSERT INTO variableoptions (option_id, option, variable_id) VALUES (1, 'var1_op1', 1) , (2, 'var1_op2', 1) , (3, 'var1_op3', 1) , (4, 'var2_op1', 2) , (5, 'var2_op2', 2) , (6, 'var3_op1', 3); END; 

La restricción NOT NULL no se puede aplazar, se aplica de inmediato. Pero la restricción de key foránea puede , porque la definimos de esa manera. Se verifica al final de la transacción, lo que evita el problema del huevo de gallina.

En este escenario editado , ambas foreign keys son diferidas . Puede ingresar variables y opciones en secuencia arbitraria.

Puede haber notado que la primera restricción de key externa no tiene modificador CASCADE . (No tendría sentido permitir cambios a variableoptions.variable_id para retroceder en cascada.

Por otro lado, la segunda key externa tiene un modificador CASCADE y se define como diferible. Esto conlleva algunas limitaciones. El manual :

Las acciones referenceles distintas de la verificación NO ACTION no se pueden aplazar, incluso si la restricción se declara diferible.

NO ACTION es el pnetworkingeterminado.

Por lo tanto, las comprobaciones de integridad referencel en INSERT se difieren, pero las acciones en cascada declaradas en DELETE y UPDATE no lo son. Lo siguiente no está permitido en PostgreSQL 9.0 o 9.1 porque las restricciones se aplican después de cada statement:

 UPDATE option SET var_id = 4 WHERE var_id = 5; DELETE FROM var WHERE var_id = 5; 

Detalles:

  • ¿Restricción definida DEFERRABLE INICIALMENTE INMEDIATA todavía está DEFERIDO?

Por extraño que parezca, lo mismo funciona en PostgreSQL 8.4 , mientras que la documentation afirma el mismo comportamiento. Parece un error en la versión anterior, incluso si parece ser beneficioso en lugar de dañino a primera vista. Debe haber sido arreglado para versiones más nuevas.

EDITAR: La versión 0.7.4 de SQLAlchemy (lanzada el mismo día en que comencé a preguntar sobre este tema, autoincrement / '11!), Contiene un nuevo valor de autoincrement para keys primarias que también forman parte de keys externas, ignore_fk . La documentation también se ha ampliado para include un buen ejemplo de lo que originalmente intentaba lograr.

Todo ahora se explica bien aquí .

Si desea ver el código que se me ocurrió antes de la versión anterior, consulte el historial de revisión de esta respuesta.

Realmente no me gustan las references circulares. Usualmente hay una manera de evitarlos. Aquí hay un enfoque:

 SystemVariables --------------- variable_id PRIMARY KEY (variable_id) VariableOptions --------------- option_id variable_id PRIMARY KEY (option_id) UNIQUE KEY (variable_id, option_id) FOREIGN KEY (variable_id) REFERENCES SystemVariables(variable_id) CurrentOptions -------------- variable_id option_id PRIMARY KEY (variable_id) FOREIGN KEY (variable_id, option_id) REFERENCES VariableOptions(variable_id, option_id)