Sql updating tables with foreign keys
Be sure that you really test and completely understand how these options work before using them.
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.
As always, with any sort of ON DELETE or ON UPDATE clause, use with care -- referential integrity is there for a reason, and having an error message tell you that you cannot UPDATE or DELETE something due to a foreign key reference is one of the great features of a relational database, even though it may seem "annoying" sometimes; it really helps to ensure that your data is clean, consistent and valid.
What happens to all the records in subordinate tables that use this value as a foreign key?Let's try it out; we'll just delete the Users table and re-create it, since that's probably easiest at this point: drop table Users go create table Users ( User ID int primary key, User Name varchar(100), Theme ID int default 1 constraint Users_Theme ID_FK references Themes(Theme ID) on delete set default ) go -- Add Theme ID 2 back in: insert into Themes (Theme ID, Theme Name) values (2,'Winter') -- Re-create our users again: insert into Users(User ID, User Name, Theme ID) values (1,'JSmith',null) insert into Users(User ID, User Name, Theme ID) values (2,'Ted',1) insert into Users(User ID, User Name, Theme ID) values (3,'ARod',2) -- Now, delete Theme ID 2: delete from Themes where Theme ID = 2 -- And let's see what we've got: select * from Users User ID User Name Theme ID ----------- --------------- ----------- 1 JSmith 1 2 Ted 1 3 Mary 1 Notice this time that Mary's Theme has been set to the default value -- 1 -- as soon as the Theme she had been assigned was deleted. We've only looked at ON DELETE, but ON UPDATE supports the same options.I don't see the ON UPDATE situations being as useful, since CASCADE seems to make the most sense when updating a foreign key value.The Users table has a "Theme ID" column that stores the User's Theme preference.Note that a User is not required to have a Theme selected; if they don't, they just get the application's default look.
Search for sql updating tables with foreign keys:
The conflict occurred in database "Play Ground", table "dbo. In some situations, "ON DELETE CASCADE" does this beautifully.