首页 文章

如何在SQL Server中使用级联删除?

提问于
浏览
268

我有2个表:T1和T2,它们是带有数据的现有表 . 我们在T1和T2之间有一对多的关系 . 当删除T1中的记录时,如何更改表定义以在SQL Server中执行级联删除,T2中的所有相关记录也将被删除 .

他们之间存在外来约束 . 我不想删除表或创建触发器来删除T2 . 例如,当我删除员工时,所有审核记录也应该消失 .

T1 - 员工,

Employee ID      
Name
Status

T2 - 性能评测,

Employee ID - 2009 Review
Employee ID - 2010 Review

7 回答

  • 13

    你需要,

    • 删除现有外键约束,

    • 添加一个启用了 ON DELETE CASCADE 设置的新设置 .

    就像是:

    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
    
  • 141

    要将“级联删除”添加到SQL Server Management Studio中的现有外键:

    首先,选择您的外键,并在新的查询窗口中打开它的“DROP和Create To ..” .

    enter image description here

    然后,只需将 ON DELETE CASCADE 添加到 ADD CONSTRAINT 命令:

    n
    并点击"Execute"按钮运行此查询 .

    顺便说一句,要获取外键列表,并查看哪些已打开“级联删除”,您可以运行此脚本:

    SELECT 
       OBJECT_NAME(f.parent_object_id) AS 'Table name',
       COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Field name',
       delete_referential_action_desc AS 'On Delete'
    FROM sys.foreign_keys AS f,
         sys.foreign_key_columns AS fc,
         sys.tables t 
    WHERE f.OBJECT_ID = fc.constraint_object_id
    AND t.OBJECT_ID = fc.referenced_object_id
    ORDER BY 1
    

    如果你发现由于外键约束你不能 DROP 特定的表,但你无法解决哪个FK导致问题,那么你可以运行这个命令:

    sp_help 'TableName'
    

    该文章中的SQL列出了引用特定表的所有FK .

    希望这一切都有帮助 .

    道歉为长指 . 我只想说明一点 .

  • 312

    您可以使用SQL Server Management Studio执行此操作 .

    →右键单击表格设计并转到“关系”并选择左侧窗格和右侧窗格中的外键,展开“INSERT和UPDATE规范”菜单,然后选择“级联”作为删除规则 .

    SQL Server Management Studio

  • 231

    使用类似的东西

    ALTER TABLE T2
    ADD CONSTRAINT fk_employee
    FOREIGN KEY (employeeID)
    REFERENCES T1 (employeeID)
    ON DELETE CASCADE;
    

    填写正确的列名称,您应该设置 . 正如mark_s正确陈述的那样,如果你已经有了外键约束,你可能需要先删除旧的约束,然后再创建一个旧的约束 .

  • 12

    First To Enable ONCascade property:

    1.Drop现有的外键约束

    2.在启用ON DELETE CASCADE设置的情况下添加新的

    例如:

    IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.Response'))
     BEGIN 
    
    ALTER TABLE [dbo].[Response] DROP CONSTRAINT [FK_Response_Request]  
    
    ALTER TABLE [dbo].[Response] WITH CHECK ADD CONSTRAINT [FK_Response_Request]  FOREIGN KEY([RequestId])
    REFERENCES [dbo].[Request] ([RequestId])
    ON DELETE CASCADE
    END
    
    ELSE
    
     BEGIN 
     ALTER TABLE [dbo].[Response] WITH CHECK ADD CONSTRAINT [FK_Response_Request]  FOREIGN KEY([RequestId])
    REFERENCES [dbo].[Request] ([RequestId])
    ON DELETE CASCADE
    END
    

    Second To Disable ONCascade property:

    1.Drop现有的外键约束

    2.在启用ON DELETE NO ACTION设置的情况下添加新的设置

    例如:

    IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.Response'))
     BEGIN 
    ALTER TABLE [dbo].[Response] DROP CONSTRAINT [FK_Response_Request]  
    
    ALTER TABLE [dbo].[Response] WITH CHECK ADD CONSTRAINT [FK_Response_Request]  FOREIGN KEY([RequestId])
    REFERENCES [dbo].[Request] ([RequestId])
    ON DELETE CASCADE
    END
    
    ELSE
    
     BEGIN 
     ALTER TABLE [dbo].[Response] WITH CHECK ADD CONSTRAINT [FK_Response_Request]  FOREIGN KEY([RequestId])
    REFERENCES [dbo].[Request] ([RequestId])
    ON DELETE NO ACTION 
    END
    
  • -7

    ON DELETE CASCADE
    它指定删除父数据时删除子数据 .

    CREATE TABLE products
    ( product_id INT PRIMARY KEY,
      product_name VARCHAR(50) NOT NULL,
      category VARCHAR(25)
    );
    
    CREATE TABLE inventory
    ( inventory_id INT PRIMARY KEY,
      product_id INT NOT NULL,
      quantity INT,
      min_level INT,
      max_level INT,
      CONSTRAINT fk_inv_product_id
        FOREIGN KEY (product_id)
        REFERENCES products (product_id)
        ON DELETE CASCADE
    );
    

    对于这个外键,我们指定了 ON DELETE CASCADE 子句,它告诉SQL Server删除父表中的数据时删除子表中的相应记录 . 因此,在此示例中,如果从products表中删除product_id值,则还将删除使用此product_id的库存表中的相应记录 .

  • 40

    我认为你不能只删除表属性,如果这是实际的 生产环境 数据,只需删除不影响表模式的内容 .

相关问题