Post reply

referential integrity for two foreign key based on same primary key

  • April 4, 2005 at 12:40 am

    #67374

    I have two table, let's say T01 (ID,...) and T02(..., ID01, ID02), where ID01, ID02 are foreign key based on primary key on T01.

    I want to implemet referential integrity with cascade update and delete (for both ID01, ID02) and i can't do it.

    I try to create a diagram based on those 2 tables:

    - I checked referential integrity (for both foreign keys) - ok!

    - I checked Update and Delete in cascade (for one foreign key) - ok!

    If I save now the diagram there is no problem!

    - I checked Update and Delete in cascade (for the other foreign key) - ok!

    If I try to save now the diagram there is a big problem: MS SQL Server doesn't allow me to do this and give me an error!

    Can you give me an advice?

    Thanks in advance!

  • April 4, 2005 at 6:27 am

    #550288

    Tried on my machine too. Looks like ms forbids you to do this as it could become some kind of infinte loop or something like that. Maybe someone else could shed some light on this.

  • April 5, 2005 at 4:57 am

    #550476

    Hello!

    I tested also with 'alter' command. It works if you not use 'on update/delete cascade' (it enforces the constraints), so I think you could 'cascade' (propagate) the appropriate changes  trough a trigger.

    HTH,

    Emanuel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply