Sql updating tables with foreign keys
This can be very handy in cases like this, and it can make things much easier to manage if used properly.Another option is to provide a DEFAULT value for the column, and use the ON DELETE SET DEFAULT.These relationships are managed through the use of essentially, fields that have the same meaning in all the tables in the relationship and that serve as points of commonality to link records in different tables together.A foreign key relationship could be one-to-one (a record in one table is linked to one and only one record in another table) or one-to-many (a record in one table is linked to multiple records in another table).In our situation, however, what would happen if we had used ON DELETE CASCADE in our foreign key constraint?-- remove the existing constraint: alter table users drop constraint Users_Theme ID_FK -- re-create it: alter table users add constraint Users_Theme ID_FK foreign key (Theme ID) references Themes(Theme ID)on delete cascade go delete from Themes where Theme ID =2 select * from Users User ID User Name Theme ID ----------- --------------- ----------- 1 JSmith NULL 2 Ted 1 (2 row(s) affected) happen?The following three constraints must be kept in mind when linking tables with foreign keys: the table without raising an error (even if doing so would break the foreign key relationships established earlier).In fact, in versions of My SQL earlier than 4.0.13, dropping the table was the only way to remove a foreign key.
Cascading updates and deletes and setting foreign keys to Defaults or NULL automatically are nice options to have, but it can be very dangerous since it all happens silently and automatically "behind the scenes".
My SQL 4.0.13 and later does, however, support a less drastic way of removing a foreign key from a table, via the clause?
What happens to all the records in subordinate tables that use this value as a foreign key?
There are really only two options we have if we'd like to be able to delete Themes but still maintain referential integrity.
If a Theme is deleted, for all Users using that Theme we'd like to either: a) Set their Theme ID to NULL or b) Assign a different, "default" Theme ID Setting foreign key references to NULL is very easy to do with SQL 2005 and above.