SQL Server Delete - Froregin 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

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

Answer 2

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 3

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