CODEWITHSUNDEEP
sqlsql-serverjunction-table
newbie
newbie

Reputation: 24645

How can I create foreign keys for junction table

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.

Upvotes: 2

Views: 751

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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.

Upvotes: 1

Zephyr
Zephyr

Reputation: 7823

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.

Upvotes: 1

Related Questions