-1

I have 5 tables that are connected together:

Table A
Table B
Table C
Table D
Table E
  • Table A PK is Code column.
  • Table B PK is Code column.
  • Table C PK is Number column.
  • Table D PK is Number column.
  • Table E PK is Name column.

Relationship between the tables above is next:

  • Table A PK has FK in Table B ACode column
  • Table A PK has FK in Table C ACode column

  • Table A PK has FK in Table E ACode column

  • Table B PK has FK in Table C BCode column

  • Table B PK has FK in Table E BCode column

  • Table C PK has FK in Table D CNumber column

This part above was pretty simple to organize in Microsoft SQL Server Management Studio. Now I would like to create CASCADE on Update on all FK columns. For example if PK in Table A gets updated all other FK in other columns (B,C and E) should update as well. Is this something that would be recommended and how I can achieve this in SQL Server 2008?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
espresso_coffee
  • 5,980
  • 11
  • 83
  • 193

1 Answers1

2

This is typically done when creating the PK-FK relationship but you should be able to do it later. See ALTER TABLE table_constraint

In your case, something like this ought to do it:

ALTER TABLE CONSTRAINT -- name of your PK constraint
    PRIMARY KEY CLUSTERED|NOCLUSTERED (...) FOREIGN KEY REFERENCES ...
ON DELETE CASCADE
ON UPDATE CASCADE
user1443098
  • 6,487
  • 5
  • 38
  • 67
  • It seems that I can not have On Update Cascade on two different `FK` relationships in the same table. Is there any reason why SQL doesn't let me do that? Also is there another solution for this situation? – espresso_coffee Aug 13 '18 at 19:53
  • there's a good discussion here: https://stackoverflow.com/questions/17127351/introducing-foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths – user1443098 Aug 14 '18 at 14:18
  • The solution often recommended is to use triggers for the second and subsequent FKs – user1443098 Aug 14 '18 at 14:19