How to add ON DELETE CASCADE in ALTER TABLE statement

Question

I have a foreign key constraint in my table  I want to add ON DELETE CASCADE to it   I have tried this    alter table child table name   modify constraint fk name   foreign key  child column name    references parent table name  parent column name  on delete cascade    Doesn t work   EDIT  Foreign key already exists  there are data in foreign key column   The error message I get after executing the statement    ORA-02275  such a referential constraint already exists in the table

User · Answer

Here is an handy solution  I m using SQL Server 2008 R2   As you want to modify the FK constraint by adding ON DELETE UPDATE CASCADE  follow these steps   NUMBER 1   Right click on the constraint and click to Modify    NUMBER 2   Choose your constraint on the left side  if there are more than one   Then on the right side  collapse  INSERT And UPDATE Specification  point and specify the actions on Delete Rule or Update Rule row to suit your need  After that  close the dialog box     NUMBER 3   The final step is to save theses modifications  of course      PS  It s saved me from a bunch of work as I want to modify a primary key referenced in another table

User · Answer

ALTER TABLE  tbl celebrity rows  ADD CONSTRAINT  tbl celebrity rows ibfk 1  FOREIGN KEY   celebrity id    REFERENCES  tbl celebrities   id   ON DELETE CASCADE ON UPDATE RESTRICT

User · Answer

For anyone using MySQL  If you head into your PHPMYADMIN webpage and navigate to the table that has the foreign key you want to update  all you have to do is click the Relational view  located in the Structure tab and change the On delete select menu option to Cascade  Image shown below

User · Answer

As explained before   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    As you can see those have to be separated commands  first dropping then adding

User · Answer

This PL SQL will write to DBMS OUTPUT a script that will drop each constraint that does not have delete cascade and recreate it with delete cascade   NOTE  running the output of this script is AT YOUR OWN RISK   Best to read over the resulting script and edit it before executing it   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

User · Answer

If you want to change a foreign key without dropping it you can do   ALTER TABLE child table name  WITH CHECK ADD FOREIGN KEY child column name  REFERENCES parent table name  parent column name  ON DELETE CASCADE

User · Answer

First drop your foreign key and try your above command  put add constraint instead of modify constraint  Now this is the command   ALTER TABLE child table name    ADD CONSTRAINT fk name    FOREIGN KEY  child column name     REFERENCES parent table name parent column name     ON DELETE CASCADE

User · Answer

You can not add ON DELETE CASCADE to an already existing constraint  You will have to drop and re-create the constraint  The documentation shows that the MODIFY CONSTRAINT clause can only modify the state of a constraint  i-e  ENABLED DISABLED

User · Answer

Answer for MYSQL USERS   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

[sql] How to add 'ON DELETE CASCADE' in ALTER TABLE statement

NUMBER 1:

NUMBER 2:

NUMBER 3:

Examples related to sql

Examples related to oracle