How can I create foreign keys for junction table

Question

I have Object1 and junction table and Object2. Object2 is table that has many junction tables, but can have only one junction table torefrencing to it. When table Object1 is removed, then junction table and Object2 should be removed. How can I make foreign keys in this situation? But when Object2 is removed, then only junction table should be removed, not Object1. I'm using SQL Server 2008.

Answer 1

I suggest:

1. When row in Table 1 is removed then junction table (JT) and row in Table 2 should be removed. Answer: Set up FK in JT referencing Table 1 with ON DELETE CASCADE set to on. Set up FK in Table 2 referencing JT with ON DELETE CASCADE set to on.

2. When row in Table 2 is removed then row in JT should be removed. Answer: You might have to set up a trigger for this.

Answer 2

You could

• apply CASCADING DELETES to your foreign key from your junction table to Object2.
• add a Trigger to Object1, check for deletion and delete the corresponding records in your junction table and Object2.