I've been researching foreign key constraints in SQL Server, and am curious about a certain setting. When you add a foreign key constraint to a child column, you can choose to give it the setting "ON DELETE SET DEFAULT". This means that if the parent row is deleted, and if the child column has a default constraint, the value in the child column get's set to the default constraint value.
The interesting thing is that the default value still needs to exist in the parent table! In other words, the rules of the foreign key constraint still apply: The value needs to exist in the parent table.
This seems a bit strange to me. To make this work, and still make sense, I would probably create a "dummy" row in my parent table whose value matches the default constraint value in the child column. That way, the rules of the foreign key constraint aren't broken, but my child row isn't referencing an actual valid row. Seems like the integrity of the data would be compromised otherwise.
Does anyone use ON DELETE SET DEFAULT (or even ON UPDATE SET DEFAULT)? I'm very curious to understand why this would be a good option to use with your foreign key constraints!
