Does the foreign keys automatically get updated as primary table is updated?

Question

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:

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?

3. if it does not update by default, how can this be done?

Thanks a lot!

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

ALTER TABLE a_table
ADD CONSTRAINT fk_a_b FOREIGN KEY (a_id)
REFERENCES b_table (b_some_id) 
ON DELETE CASCADE ON UPDATE 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-row Trigger.

For example, in Oracle you would implement this as:

CREATE OR REPLACE TRIGGER walls_wall_upd_trg
 AFTER UPDATE OF wall_id ON walls FOR EACH ROW
BEGIN
    UPDATE 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;

CREATE OR REPLACE TRIGGER walls_desk_upd_trg
 AFTER UPDATE OF desktop_id ON walls FOR EACH ROW
BEGIN
    UPDATE desktop_wall
       SET desktop_id = :new.desktop_id
     WHERE desktop_id = :old.desktop_id;
END;

View more solutions

Comments

[vep temp]

are those ON UPATE, ON DELETE triggers? *oh I think those are constraints.

[Rahul Tripathi]

You can create Triggers on columns so as to get the foreign key updated automatically. So whenever the column will be changed the foreign key will be updated automatically!

[baltov]

I am not sure that that is true. ON DELETE CASCADE should be executed only on DELETE command, if you change a ID, maybe you have a exception but the records can not be deleted.

[Ravi K Thapliyal]

There's an ON UPDATE CASCADE too but it's not supported on all databases. What's your target database?

[Bill Karwin]

In fact, if you have a foreign key without ON UPDATE CASCADE, then you can't update the parent first, nor can you update the child first. Both result in conflicts. Unless your database supports deferred constraint checking.

[Rahul Tripathi]

@sqlvogel:- Yes that is correct but since OP has not mentioned anything about it so I have just provided an alternative

[Ravi K Thapliyal]

@baltov You're right. It seems most databases fail on updates. Thanks for pointing that out.

[Rahul Tripathi]

@BillKarwin:- I didnt get that very much. Could you please tell me if I am missing something? Thanks in advance