Sql updating tables with foreign keys
The fundamental basis of a relational database system like My SQL is its capability to create relationships between the tables that make up the database.
Writing little scripts like I have done here is the way to go when learning about new features, so I encourage you to do the same.
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.
We simply set the foreign key's ON DELETE clause to SET NULL and it will work just as we'd expected: -- remove the existing constraint: alter table users drop constraint Users_Theme ID_FK -- This time, create it with on delete set null: alter table users add constraint Users_Theme ID_FK foreign key (Theme ID) references Themes(Theme ID) on delete set null -- Add our data back in insert into Themes (Theme ID, Theme Name) values (2,'Winter') insert into Users(User ID, User Name, Theme ID) values (3,'Mary',2) -- And now delete Theme ID 2 again: delete from Themes where Theme ID =2 -- Let's see what we've got: select * from Users User ID User Name Theme ID ----------- ----------------- ----------- 1 JSmith NULL 2 Ted 1 3 Mary NULL (3 row(s) affected) And there you go!
Mary is still there this time, and by deleting Theme ID 2, it simply set all foreign key references to that value to NULL.