0

I want to delete all the rows from a table in the Oracle DB i.e.Table Name:Address.

The table is used as the foreign key in other tables for example in Customers.

what i want is , When i delete all the rows of the table Address, All rows of other tables which are referencing these records should also be deleted.

NOTE I have not provided "on delete cascade" at the time of creating table.

Any help is appreciated.

juergen d
  • 201,996
  • 37
  • 293
  • 362
Awadesh
  • 3,530
  • 2
  • 20
  • 32

2 Answers2

0

That really depends on what you mean.

By your description you probably mean a cascading delete.

But that makes no sense, since your table is a foriegn key, so every "customer" would have an AddressID (int) column, and probably a NOT NULL column as well. so deleting all addresses would be ... deleting the entire customer table? or maybe DELETE FROM customer WHERE AddressID IS NOT NULL ? either way, that does not make sense.

LongChalk
  • 783
  • 8
  • 13
0

Oh, I get it now. you are testing the boundaries of your ability. That actually makes sense in DEV environment. But make sure you don't do stuff like that in production. A couple of principles which I have found very good practice -

  1. Don't delete. If you want to "delete" - simply add a column IsDeleted bit NOT NULL DEFAULT (0) because once a row is gone, it is gone forever. changing the row from IsDeleted=0 to IsDeleted=1 is always easily reversable.
  2. Seperate in your mind with a clear line DML (data manipulation language - the act of changing data) with DDL (data definition language - the act of changing the table definitions etc. , aka - changing the schema). To delete all lines is to "reset" the table. Use Truncate TABLE Customers or DROP AND CREATE syntax. (but really - don't try this at home kids).
LongChalk
  • 783
  • 8
  • 13