CODEWITHSUNDEEP
sqloracle-database
homelessDevOps
homelessDevOps

Reputation: 20726

Can someone explain me this Oracle SQL Command?

ALTER TABLE "APPLICATIONS_SRV_STORAGE"
    ADD CONSTRAINT "APPL_SRV_STORAGE_APPLICAT_FK1"
    FOREIGN KEY ("APP_ID")
    REFERENCES "APPLICATIONS" ("APP_ID")
    ON DELETE CASCADE
    ENABLE;

Does it mean: When something on table APPLICATIONS is deleted, delte altough the entry in APPLICATION_SRV_STORAGE?

Upvotes: 1

Views: 139

Answers (3)

Aaron Digulla
Aaron Digulla

Reputation: 328556

This adds a new constraint with the name APPL_SRV_STORAGE_APPLICAT_FK1 to the table APPLICATIONS_SRV_STORAGE.

The constraint itself is that for every value in the column APPLICATIONS_SRV_STORAGE.APP_ID, there must be a matching value in APPLICATIONS.APP_ID. APPLICATIONS.APP_ID can contain values not contained in APPLICATIONS_SRV_STORAGE.APP_ID.

In addition to that, if an APP_ID is deleted from the table APPLICATIONS, then all rows from APPLICATIONS_SRV_STORAGE with the same ID are deleted as well.

At creation time, the constraint is enabled (Oracle allows you to disable constraints temporarily).

Upvotes: 3

Alin P.
Alin P.

Reputation: 44346

Yes. APPLICATIONS will be considered the parent and APPLICATIONS_SRV_STORAGE will be the child, the connection being made through APP_ID. When the parent is deleted, then all the children will also be deleted (cascading).

Reference: http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php (Couldn't find the official one.)

Upvotes: 3

batwad
batwad

Reputation: 3655

The addition of DELETE CASCADE means that when a row in APPLICATIONS is deleted, any rows in APPLICATIONS_SRV_STORAGE that reference its APP_ID will also be deleted.

Upvotes: 8

Related Questions