Sql updating tables with foreign keys
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.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.The conflict occurred in database "Play Ground", table "dbo. In some situations, "ON DELETE CASCADE" does this beautifully.For example, if there is a table that relates multiple Users to multiple Themes, then ON DELETE CASCADE is just what you are after and any rows relating Users to the Theme just deleted will be removed.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?
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.
Obviously, those records should be deleted as well, or else you’ll have orphan records cluttering your database.
My SQL 3.23.50 and later simplifies this task by enabling you to add an clause to a foreign key definition tells My SQL that when a record is updated in the primary table (the table referenced for foreign key checks), all records using that foreign key value in the current table should also be automatically updated with the new values to ensure the consistency of the system.
For example, suppose the default Theme ID for all Users is Theme ID 1.
If we set the column's default value to 1 in our table definition, and use ON DELETE SET DEFAULT, when the foreign table has rows deleted, the column's default is used to replace the (now deleted) foreign key value.
Search for sql updating tables with foreign keys:
Primary keys serve as unique identifiers for the records in a table, while foreign keys are used to link related tables together.