Relationship Delete and Insert Rule
When you define the foreign keys you can include ON DELETE CASCADE to enforce the delete rule.

As for problems in the design. One immediate issue I see is that you've defined the Identity columns as "Id". This design pattern almost always leads to trouble. What you want to use instead is an ID column name that includes the table name. For example, Id in the User table should be named UserID.