SQL Server Delete - Foreign Key

Question

I have got two tables in SQL Server 2005:

• USER Table: information about user and so on.
• COUNTRY Table : Holds list of whole countries on the world.
• USER_COUNTRY Table: Which matches, which user has visited which county. It holds, UserID and CountryID.

For example, USER_COUNTRY table looks like this:

+----+--------+-----------+
| ID | UserID | CountryID |
+----+--------+-----------+
|  1 |      1 |        34 |
|  2 |      1 |         5 |
|  3 |      2 |        17 |
|  4 |      2 |        12 |
|  5 |      2 |        21 |
|  6 |      3 |        19 |
+----+--------+-----------+

My question is that: When a user is deleted in USER table, how can I make associated records in USER_COUNTRY table deleted directly. Maybe, by using Foreign Key Constaint?

Answer 1

You have to define a foreign key in USER_COUNTRY that points to USER.UserID and set cascaded deletion:

CREATE TABLE USER_COUNTRY (
    ...
    CONSTRAINT USER_COUNTRY_FK1 FOREIGN KEY (UserID)
        REFERENCES USER(UserID)
        ON DELETE CASCADE
);

Answer 2

I guess CASCADE is your only option. But do you really want to hard delete records like this? Context: I'm a data fiend.

Answer 3

Yes, you could set your foreign key relationship Delete rule to Cascade.