Q:

Foreign key reference for two columns

I need to set two foreign key references for two different columns to my article table.
Both of them have to reference to the same column of my material table.
But if i try to create the second i got the following error:

Meldung 1785, Ebene 16, Status 0, Zeile 3
Das Einführen der FOREIGN KEY-Einschränkung 'FK_db00_02_Artikelstamm_WST_db08_01_Werkstoffe_Bezeichnung' für die db00_02_Artikelstamm-Tabelle kann Schleifen oder mehrere Kaskadepfade verursachen. Geben Sie ON DELETE NO ACTION oder ON UPDATE NO ACTION an, oder ändern Sie andere FOREIGN KEY-Einschränkungen.
The introduction of the FOREIGN KEY constraint 'FK_db00_02_Artikelstamm_WST_db08_01_Werkstoffe_Bezeichnung' for db00_02_Artikelstamm table can cause loops or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints

I try to create them with this SQL code:
-- Fremdschlüssel db00_02_Artikelstamm.WST -> db08_Werkstoffe.Bezeichnung
-- FK_db00_02_Artikelstamm_WST_db08_Werkstoffe_Bezeichnung
ALTER TABLE [dbo].[db00_02_Artikelstamm] WITH CHECK
ADD CONSTRAINT [FK_db00_02_Artikelstamm_WST_db08_Werkstoffe_Bezeichnung] FOREIGN KEY ([WST])
    REFERENCES [dbo].[db08_Werkstoffe] ([Bezeichnung]) ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[db00_02_Artikelstamm] CHECK CONSTRAINT [FK_db00_02_Artikelstamm_WST_db08_Werkstoffe_Bezeichnung]
GO

-- Fremdschlüssel db00_02_Artikelstamm.WSTgroup -> db08_Werkstoffe.Bezeichnung
-- FK_db00_02_Artikelstamm_WSTgroup_db08_Werkstoffe_Bezeichnung
ALTER TABLE [dbo].[db00_02_Artikelstamm] WITH CHECK
ADD CONSTRAINT [FK_db00_02_Artikelstamm_WSTgroup_db08_Werkstoffe_Bezeichnung] FOREIGN KEY ([WSTgroup])
    REFERENCES [dbo].[db08_Werkstoffe] ([Bezeichnung]) ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[db00_02_Artikelstamm] CHECK CONSTRAINT [FK_db00_02_Artikelstamm_WSTgroup_db08_Werkstoffe_Bezeichnung]
GO

What i need is:

A change of the name of a material in the material table automatically updates the material in my article table
A change of a material in the article table do not change the name of a material in the material table
A change in the article table is only possible if the new material is in the material table
I should not be possible to delete a material while this material is used by an article

I use an MS SQL Server.
I hope i could explain it clearly and someone can help me.
Thanks in advance!

A:

Maybe you should use as reference an univoque id and then avoid the "on update cascade", because in that case you can modify the name of article without concern about the propagation of changes to "childs" tables. 
In this way, it is already implied the constraint about changes of material reference in article table.
The halting deleting constraint can be declarated on material table with the clause "ON DELETE NO ACTION"

update after comments..

As said, if you have a problem of manteinance and legacy code I think that you have implement some software solution, else I think that the best solution, if the reference of your "materials" table was not spread into many other tables, it is to back-up all tables that have to be modified, add a new primary key to your material table different of current unique name of material (if not already present), then delete the foreign key pointing to the materials table in the articles table, then update the value in the fk_field of each article with the primary of  each material corresponding to the current name valu contained by the fk_field, and then add the new foreign key.

