Comment ajouter 'ON DELETE CASCADE' dans l'instruction ALTER TABLE
J'ai une contrainte de clé étrangère dans ma table, je veux ajouter une CASCADE de suppression.
J'ai essayé ceci:
alter table child_table_name modify constraint fk_name foreign key (child_column_name) references parent_table_name (parent_column_name) on delete cascade;
Ne fonctionne pas.
Modifier:
La clé étrangère existe déjà, il y a des données dans la colonne de clé étrangère.
Le message d'erreur que je reçois après l'exécution de l'instruction:
ORA-02275: such a referential constraint already exists in the table
8 réponses
Vous ne pouvez pas ajouter ON DELETE CASCADE
à une contrainte déjà existante. Vous devrez drop
et re-create
la contrainte. La documentation montre que la clause MODIFY CONSTRAINT
ne peut modifier que l'état d'une contrainte (i-e: ENABLED/DISABLED
...).
Premier drop
votre clé étrangère et essayez de votre commande ci-dessus, mettez add constraint
au lieu de modify constraint
.
Maintenant, c'est la commande:
ALTER TABLE child_table_name
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column_name)
REFERENCES parent_table_name(parent_column_name)
ON DELETE CASCADE;
Ce PL * SQL écrira dans DBMS_OUTPUT un script qui supprimera chaque contrainte qui n'a pas delete cascade et la recréera avec delete cascade.
Remarque: l'exécution de la sortie de ce script est à vos risques et périls. Le meilleur pour lire le script et de le modifier avant de l'exécuter.
DECLARE
CURSOR consCols (theCons VARCHAR2, theOwner VARCHAR2) IS
select * from user_cons_columns
where constraint_name = theCons and owner = theOwner
order by position;
firstCol BOOLEAN := TRUE;
begin
-- For each constraint
FOR cons IN (select * from user_constraints
where delete_rule = 'NO ACTION'
and constraint_name not like '%MODIFIED_BY_FK' -- these constraints we do not want delete cascade
and constraint_name not like '%CREATED_BY_FK'
order by table_name)
LOOP
-- Drop the constraint
DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || cons.OWNER || '.' || cons.TABLE_NAME || ' DROP CONSTRAINT ' || cons.CONSTRAINT_NAME || ';');
-- Re-create the constraint
DBMS_OUTPUT.PUT('ALTER TABLE ' || cons.OWNER || '.' || cons.TABLE_NAME || ' ADD CONSTRAINT ' || cons.CONSTRAINT_NAME
|| ' FOREIGN KEY (');
firstCol := TRUE;
-- For each referencing column
FOR consCol IN consCols(cons.CONSTRAINT_NAME, cons.OWNER)
LOOP
IF(firstCol) THEN
firstCol := FALSE;
ELSE
DBMS_OUTPUT.PUT(',');
END IF;
DBMS_OUTPUT.PUT(consCol.COLUMN_NAME);
END LOOP;
DBMS_OUTPUT.PUT(') REFERENCES ');
firstCol := TRUE;
-- For each referenced column
FOR consCol IN consCols(cons.R_CONSTRAINT_NAME, cons.R_OWNER)
LOOP
IF(firstCol) THEN
DBMS_OUTPUT.PUT(consCol.OWNER);
DBMS_OUTPUT.PUT('.');
DBMS_OUTPUT.PUT(consCol.TABLE_NAME); -- This seems a bit of a kluge.
DBMS_OUTPUT.PUT(' (');
firstCol := FALSE;
ELSE
DBMS_OUTPUT.PUT(',');
END IF;
DBMS_OUTPUT.PUT(consCol.COLUMN_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE(') ON DELETE CASCADE ENABLE VALIDATE;');
END LOOP;
end;
Comme expliqué précédemment:
ALTER TABLE TABLEName
drop CONSTRAINT FK_CONSTRAINTNAME;
ALTER TABLE TABLENAME
ADD CONSTRAINT FK_CONSTRAINTNAME
FOREIGN KEY (FId)
REFERENCES OTHERTABLE
(Id)
ON DELETE CASCADE ON UPDATE NO ACTION;
Comme vous pouvez le voir, ces commandes doivent être séparées, d'abord en supprimant puis en ajoutant.
Pour toute personne utilisant MySQL:
Si vous vous dirigez vers votre page Web PHPMYADMIN
et accédez à la table contenant la clé étrangère que vous souhaitez mettre à jour, tout ce que vous avez à faire est de cliquer sur Relational view
situé dans l'onglet Structure
et de changer l'option de menu On delete
select en Cascade
.
Image ci-dessous:
Voici une solution pratique! Je suis à l'aide de SQL Server 2008 R2.
Comme vous souhaitez modifier la contrainte FK en ajoutant une CASCADE de suppression/mise à jour, procédez comme suit:
Numéro 1:
Cliquez-Droit sur la contrainte et cliquez pour Modifier
Numéro 2:
Choisissez votre contrainte sur le côté gauche (s'il y en a plus d'une). Ensuite, sur le côté droit, réduire" insérer et mettre à jour la spécification " point et spécifiez les actions sur Supprimer la règle ou mettre à jour la ligne de règle en fonction de vos besoins. Après cela, fermez la boîte de dialogue.
Numéro 3:
La dernière étape consiste à enregistrer ces modifications (bien sûr!)
PS: cela m'a sauvé d'un tas de travail car je veux modifier une clé primaire référencée dans une autre table.
Si vous souhaitez modifier une clé étrangère sans la laisser tomber vous pouvez faire:
ALTER TABLE child_table_name WITH CHECK ADD FOREIGN KEY(child_column_name)
REFERENCES parent_table_name (parent_column_name) ON DELETE CASCADE
Réponse pour les utilisateurs de MYSQL:
ALTER TABLE ChildTableName
DROP FOREIGN KEY `fk_table`;
ALTER TABLE ChildTableName
ADD CONSTRAINT `fk_t1_t2_tt`
FOREIGN KEY (`parentTable`)
REFERENCES parentTable (`columnName`)
ON DELETE CASCADE
ON UPDATE CASCADE;