Dois-je désactiver temporairement les contraintes de clé étrangère? Comment?

J'ai deux tables:

person:
    id serial primary key,
    name varchar(64) not null

task:
    tenant_id   integer not null references person (id) on delete cascade,
    customer_id integer not null references person (id) on delete restrict

(Ils ont beaucoup plus de colonnes que ça, mais le reste ne sont pas pertinentes pour la question.)

Le problème est, je veux cascade-supprimer un task lorsque son locataire person est supprimé. Mais lorsque le locataire et le client sont la même personne, le customer_id contrainte de clé étrangère va limiter la suppression.

Ma question est en deux parties:

Est de désactiver temporairement le deuxième clé étrangère ma seule option?
Si oui, alors comment dois-je faire dans PostgreSQL?

la table personne est auto référencé? ou j'ai mal compris?
oui, j'ai oublié... j'ai mis à jour la question en conséquence
viens le problème quand id = tenant_id? ou avec tout autre registre qui doit être supprimé sur la cascade? Désolé, je suis vraiment pas sûr de Postgres, mais ai eu un problème similaire avec Ms sql server, sql-server ne pas permettre à l'ensemble on delete cascade quand est auto référencé... j'ai fait un cte pour obtenir tous les regs qui serait supprimé sur la cascade et la suppression de tous sur la même instruction delete.. de cette façon, le FK na pas restreindre la cause n'registre seraient supprimés en même temps que...
Veuillez préciser le rôle de person.tenant_id. Ne semble pas pertinent pour la question? Aussi, vous parlez d'une première et d'une deuxième partie de la question, que je ne peux identifier. Je vois une seule question. Et de déclarer la version de Postgres en cours d'utilisation.
Si vous voulez des suppressions en cascade, pourquoi avez-vous on delete restrict sur le customer_id contrainte de clé étrangère? Ah, je vois, tant pis.

OriginalL'auteur clapas | 2013-02-21

12

Efficacement vous créez un condition de course avec contredire les règles.

Mon premier réflexe a été de vérifier si une DIFFÉRÉS contrainte de l'aide. Mais il est logique qu'il ne fait aucune différence.

J'ai trouvé que le FK contrainte qui vient d'abord dans le CREATE TABLE script est le gagnant de cette course. Si le ON DELETE CASCADE vient en premier, la supprimer est en cascade, si ON DELETE RESTRICT vient en premier, l'opération est abandonnée.

Envisager la démo sur SQL Violon.

Cela semble en corrélation avec une plus petite oid dans le catalogue de la table pg_constraint:
```
SELECT oid, * FROM pg_constraint WHERE conrelid = 'task'::regclass
```
Mais vos commentaires indique, ce n'est pas la cause. Peut-être pg_attribute.attnum décide de la course. De toute façon, tant qu'il n'est pas documentée comportement vous ne pouvez pas compter sur elle pour rester dans la prochaine version majeure. Peut-être la peine de poser une question sur [email protected].

Indépendant de tout cela, vous devez tenir compte des autres lignes: même si CASCADE allait passer pour une ligne en task qui a à la fois tenant_id et customer_id pointant vers un person, il sera toujours l'objet de restrictions si aucune ligne n'a qu' customer_id référencement person.

Un autre SQL Violon démontrant le cas.

Comment désactiver la contrainte?

Votre meilleur pari est de supprimer et de le recréer. Tout faire à l'intérieur d'une transaction, assurez-vous de ne pas endommager l'intégrité référentielle.
```
BEGIN;

ALTER TABLE task DROP CONSTRAINT task_customer_id_fkey;

DELETE FROM person WHERE id = 3;

ALTER TABLE task ADD CONSTRAINT task_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES person (id) ON DELETE RESTRICT;

COMMIT;
```
Cela verrouille la table exclusivement et ne sont pas adaptés pour l'utilisation de routine dans un environnement multi-utilisateur.

Comment je sais que la nom de la contrainte? Je l'ai pris de pg_constraint comme démontré ci-dessus. Peut-être plus facile d'utiliser un nom de contrainte explicite pour commencer:
```
CREATE TEMP TABLE task (
    customer_id integer NOT NULL
   ,tenant_id integer NOT NULL REFERENCES person (id) ON DELETE CASCADE
   ,CONSTRAINT task_customer_id_fkey FOREIGN KEY (customer_id)
    REFERENCES person (id) ON DELETE RESTRICT
);
```
Il est également
```
ALTER TABLE task DISABLE trigger ALL;
```
De plus, dans le manuel ici. Mais ce serait de désactiver tous déclencheurs. Je n'avais aucune chance en essayant de désactiver uniquement le déclencheur créé par le système pour mettre en œuvre une seule FK contrainte.

D'autres solutions serait de mettre en place votre régime avec les déclencheurs ou les règles. Qui fonctionne très bien, mais ceux qui ne sont pas appliquées de manière aussi stricte que les clés étrangères.

J'ai déjà essayé quelque chose de similaire et, pour une raison quelconque, il n'a pas de travail. J'ai eu larguaient tenant_id de clé étrangère et recréé par la suite, de sorte que son oid est plus grande (je peux dire avec le sélectionner à partir de pg_constraint que vous l'avez souligné). J'ai copié le code dans votre violon et il fonctionne en local, donc cela peut être un moyen pour atteindre mon objectif, mais je pense que la raison pour laquelle il pourrait d'autre que l'oid être plus grand. J'ai essayer le DEFERRED chose. Je ne comprends pas la dernière partie de votre réponse: les deux tenant_id et customer_id ont not null contraintes.
Envisager la mise à jour de ma réponse.
Ahh! Maintenant, je comprends ce que tu veux! Le scénario de la deuxième sqlfiddle ne se fait pas dans mon système, parce que les locataires ne peuvent pas partager un client commun, c'est à dire le locataire définit un champ d'application. Mon mal de ne pas le mentionner. Je suppose que c'est mieux de ne pas s'appuyer sur un sans-papiers comportement, comme vous le dites, je vais donc désactiver temporairement ou des contraintes de mettre en œuvre la solution avec des déclencheurs ou des règles, je n'ai pas encore décidé. En tout cas, merci beaucoup pour votre aide.
En passant, j'ai lu dans la création de tableau dans la section de Postgres manuel "Référentiel des actions autres que l'ABSENCE d'ACTION de la vérification ne peut pas être différée, même si la contrainte est déclaré non urgent."
Je suis conscient de cela et a couru mes tests avec NO ACTION qui aurait servi de remplacement pour RESTRICT, mais en vain, comme indiqué dans ma réponse.

OriginalL'auteur Erwin Brandstetter

Vous devez vous connecter pour publier un commentaire.

Comment désactiver la contrainte?