Does The Foreign Keys Automatically Get Updated As Primary Table Is Updated?

Above is my simple database design, just wanted to gain information about how things happen as I'm really new at database. Following are my questions: as I update wall_id in wall

Solution 1:

This feature is called cascading referential integrity. It's optional when you define a foreign key constraint. The syntax to enable it is described here (Micorosoft SQL but the syntax is standard and most DBMSs support it):

http://technet.microsoft.com/en-us/library/ms186973(v=sql.105).aspx

Solution 2:

In postgresql I write like this

ALTERTABLE a_table
ADDCONSTRAINT fk_a_b FOREIGN KEY (a_id)
REFERENCES b_table (b_some_id) 
ONDELETE CASCADE ONUPDATE CASCADE;

and get that fileds are updated and deleted with cascaded way.

Solution 3:

No the foreign key is not updated automatically. You need to update the foreign key in the tables in which it is referenced by yourself else it would result in referential integrity exception.

For updating the foreign key automatically you may use TRIGGERS.

EDIT:-

As suggested by sqlvogel in comments if your DBMS supports cascading updates the you dont need to create TRIGGERS for that.

In that case it would be better if you have Cascading Referential Integrity Constraints

By using cascading referential integrity constraints, you can define the actions that the SQL Server takes when a user tries to delete or update a key to which existing foreign keys point.

Solution 4:

1. As I update wall_id in walls table, does the wall_id in wall_categories table also get updated? as the wall_id in wall_categories table references to wall_id in walls table.

2. same with desktop_id in walls table since it is a foreign key referencing to desktop_id in desktop_wall table, so when I update desktop_id in walls table does the desktop_id in deskotp_wall also gets updated?

No, the updates won't happen automatically. In fact, you would get a SQLException because you're trying to break referential integrity and hence your updates would fail. This assuming you have valid Constraints applied to the tables.

---

There's an ON UPDATE CASCADE too but it's not supported on all databases. While, a cascading update should obviously be preferred; if your database doesn't support it you would need to implement an after-rowTrigger.

For example, in Oracle you would implement this as:

CREATEOR REPLACE TRIGGER walls_wall_upd_trg
 AFTER UPDATEOF wall_id ON walls FOREACHROWBEGINUPDATE wall_categories
       SET wall_id = :new.wall_id
     WHERE wall_id = :old.wall_id;

    UPDATE wall_comments
       SET wall_id = :new.wall_id
     WHERE wall_id = :old.wall_id;
END;

CREATEOR REPLACE TRIGGER walls_desk_upd_trg
 AFTER UPDATEOF desktop_id ON walls FOREACHROWBEGINUPDATE desktop_wall
       SET desktop_id = :new.desktop_id
     WHERE desktop_id = :old.desktop_id;
END;