none
Referring other tables (ON DELETE)? RRS feed

  • Question

  • 

    So theoraticaly, I have this issue on hand:

    The database should be relational, so I have to ensure that changements automaticaly should be carried on to other tables.

    For example, if an article is deleted, all data referencing to it, should be replaced by null.

    Also, it would be nice to archive all changements.

    The latter issue, I am very certain I can do it with temporal tables, correct me if I'm wrong. Shoudl I look fuirther into it, or is there a more common and elegant way?

    The former issue, I figured out, I could achieve this with the CASCADE function, I read this in book

    ( Changements of referenced data "[...]pid int references persons ON DELETE SET NULL"

    Furthermore: "pid int referecnes persons ON DELETE CASCADE" )

    Alas, i have no practical experience with this stuff, can I get some script which might help me wkith this? I have merely 3 weeks experience.

    Thanks in advance!

    Wednesday, October 16, 2019 1:45 PM

Answers

  • Hi 

    As you are expecting if you are looking for cascading functionality we need the define and the constraints in SQL Server as below.

    ALTER TABLE dbo.T2
       DROP CONSTRAINT FK_T1_T2   -- or whatever it's called
    
    ALTER TABLE dbo.T2
       ADD CONSTRAINT FK_T1_T2_Cascade
       FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE

    If you are expecting this to happen in OLTP applications, this is okay but if you want to try it in OLAP Data bases it behaves in worse way - If so, look for TRIGGERS or make your code/delete scripts changed to delete the content in all the dependent tables .

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Adamantus Monday, October 21, 2019 12:40 PM
    Wednesday, October 16, 2019 1:55 PM

All replies

  • Hi 

    As you are expecting if you are looking for cascading functionality we need the define and the constraints in SQL Server as below.

    ALTER TABLE dbo.T2
       DROP CONSTRAINT FK_T1_T2   -- or whatever it's called
    
    ALTER TABLE dbo.T2
       ADD CONSTRAINT FK_T1_T2_Cascade
       FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE

    If you are expecting this to happen in OLTP applications, this is okay but if you want to try it in OLAP Data bases it behaves in worse way - If so, look for TRIGGERS or make your code/delete scripts changed to delete the content in all the dependent tables .

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Adamantus Monday, October 21, 2019 12:40 PM
    Wednesday, October 16, 2019 1:55 PM
  • If you want to keep your data logically consistent, I would suggest to avoid physical deletion/SET NULL pattern. Setting referencing columns to NULL is no different comparing to leaving garbage in the data.

    Create the attribute/column IsDeleted bit just perform logical deletion filtering deleted rows in the queries.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Wednesday, October 16, 2019 5:34 PM