Search code examples
sqloracleconstraintssql-delete

SQL Delete Rows with Constraints

I am doing some Exercises and for that I want to delete a Row which is referenced with a Primary/Foreign Key.

Is it possible to delete those Rows without deleting the ones which it has a Relationship with? (like On Delete Cascade)

I also know that I can Disable the constraints, however I want to try deleting without disabling the Constraints.

Solution

  • A row can always be deleted unless its primary key is referenced as a foreign key in a row from another table. In this case, attempting to delete rows will result in an error when the primary/foreign key relationship against data is encountered. The ON DELETE CASCADE option can be used to work around this and delete records from the child table when rows are removed from the parent table.

    If you only want to delete the rows that do not have a relationship, then you can exclude the rows that do have a relationship from the delete operation using NOT EXISTS:

    DELETE FROM table1 a
WHERE NOT EXISTS (
   SELECT FROM table2 b
   WHERE b.table1Id = a.id
)