实现删除主表数据时, 判断与之关联的外键表是否有数据引用, 有标志, 无则删除

原创 2006年09月18日 11:30:00

问题描述:

某个基础信息表,与系统中30多个表存在外键关系,当删除基础数据时,需要判断是否已经被用过,如果用过则更改标志位,如果没有用过则直接删除,如何能很好实现这个处理?最好能够自动适应表的变化

 

问题解决(SQL Server 2005

-- SQL Server 2005的错误处理容易控制, 因此, SQL Server 2005可以直接删除, 通过错误处理来确定是否需要更新.

 

-- 示例如下.

USE tempdb

GO

 

CREATE TABLE m(

    id int PRIMARY KEY,

    bz bit)

INSERT m SELECT 1, 0

UNION ALL SELECT 2, 0

 

CREATE TABLE c(

    id int primary key,

    a_id int references m(id)

        ON DELETE NO ACTION)

INSERT c SELECT 1, 1

GO

 

-- 删除处理存储过程

CREATE PROC dbo.p_delete

    @id int

AS

SET NOCOUNT ON

BEGIN TRY

BEGIN TRAN

    DELETE FROM m WHERE id = @id

COMMIT TRAN

END TRY

BEGIN CATCH

    ROLLBACK TRAN

    IF ERROR_NUMBER() = 547 -- 如果是外键约束错误

    BEGIN

        BEGIN TRY

        BEGIN TRAN          -- 更新标志

            UPDATE m SET bz = 1

            WHERE id = @id

        COMMIT TRAN

        END TRY

        BEGIN CATCH

            SELECT ERROR_NUMBER(), ERROR_MESSAGE()

        END CATCH

    END

    ELSE

        SELECT ERROR_NUMBER(), ERROR_MESSAGE()

END CATCH

GO

 

-- 调用

EXEC dbo.p_delete 1

EXEC dbo.p_delete 2

SELECT * FROM m

SELECT * FROM c

GO

 

DROP TABLE c, m

DROP PROC dbo.p_delete

 

问题解决(SQL Server 2000

-- SQL Server 2000 对错误处理不好控制, 一般还是建议做判断

-- 通过系统表查询系统表,可以获取某个表关联的所有外键表

 

-- 示例存储过程

CREATE PROC dbo.p_Delete

    @tbname sysname,        -- 基础数据表名

    @PkFieldName sysname,   -- 基础数据表关键字段名

    @PkValue int            -- 要删除的基础数据表关键字值

AS

SET NOCOUNT ON

DECLARE @bz bit, @s nvarchar(4000)

DECLARE tb CURSOR LOCAL

FOR

SELECT N'

SET @bz = CASE WHEN EXISTS(

        SELECT * FROM ' + QUOTENAME(@tbname)

        + N' A, ' + QUOTENAME(OBJECT_NAME(B.fkeyid))

        + N' B

        WHERE A.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.rkey AND id = B.rkeyid))

        + N' = B.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.fkey AND id = B.fkeyid))

        + N' AND A.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.rkey AND id = B.rkeyid))

        + N' = @id) THEN 1 ELSE 0 END'

FROM sysobjects A

    JOIN sysforeignkeys B

        ON A.id=  B.constid

    JOIN sysobjects C

        ON A.parent_obj = C.id

WHERE A.xtype = 'f'

    AND C.xtype = 'U'

    AND OBJECT_NAME(B.rkeyid) = @tbname

OPEN tb

FETCH tb INTO @s

WHILE @@FETCH_STATUS = 0

BEGIN

    EXEC sp_executesql @s, N'@tbname sysname, @id int, @bz bit OUT', @tbname, @PkValue, @bz OUT

    IF @bz = 1

    BEGIN

        SET @s = N'UPDATE ' + QUOTENAME(@tbname)

            + N' SET bz = 1 WHERE ' + QUOTENAME(@PkFieldName)

            + N' = @id'

        EXEC sp_executesql @s, N'@id int', @PkValue

 

        RETURN

    END

 

    FETCH tb INTO @s

END

CLOSE tb

DEALLOCATE tb

 

SET @s = N'DELETE FROM ' + QUOTENAME(@tbname)

    + N' WHERE ' + QUOTENAME(@PkFieldName)

    + N' = @id'

EXEC sp_executesql @s, N'@id int', @PkValue

GO

 

注意事项

设置表的主/外键关系的时候,不要设置级联删除(ON DELETE CASCADE

 

 

主外键关联删除(on delete set null和on delete cascade)

主外键关联,当删除的是父表数据,参照这些要删除的数据,Oracle有三种处理方式: 1、禁止删除,也是Oracle默认方法。 2、将参照要删除数据的子表对应数据置空。 3、将参照要删除数据的子表...
  • bisal
  • bisal
  • 2014年03月10日 08:58
  • 13718

使用c标签的forEach遍历展现数据库主表与从表的数据

首先,数据库有两个表,A表的ID(主键)是B表的外键,A表的一条数据可能对应B表的一条或多条数据,即A1→ B1、B2、B3... 功能需求是将A表的数据与A表对应的B表的数据显示出来,这就需要用到C...

session的传值和取值

Web中的Session指的就是用户在浏览某个网站时,从进入网站到浏览器关闭所经过的这段时间,也就是用户浏览这个网站所花费的时间。Session 是 用于保持状态的基于 Web服务器的方法。Sess...

MySQL因为外键关联无法删除数据解决方法以及修改数据库全部表中某种外键的值

mysql删除数据时因为外键关系无法删除的解决办法;批量更新数据库所有表中外键为某关联表的某条数据的值;...

【数据库】 主键,外键,主表,从表,关联表,父表,子表

一、前言     在数据库设计中,hibernate,iBatis等ORM框架的使用中经常听说主键,外键,主表,从表,关联表,父表,子表之类的术语,弄懂它们之前的区别与联系对于数据库设计和O...

浅析【数据库】 主键,外键,主表,从表,关联表,父表,子表

一、前言在数据库设计中,hibernate,iBatis等ORM框架的使用中经常听说主键,外键,主表,从表,关联表,父表,子表之类的术语,弄懂它们之前的区别与联系对于数据库设计和ORM框架的学习使用是...

主键,外键,主表,从表,关联表,父表,子表

一、前言     在数据库设计中,Hibernate,iBatis等ORM框架的使用中经常听说主键,外键,主表,从表,关联表,父表,子表之类的术语,弄懂它们之前的区别与联系对于数据库设计和...

使用sql级联删除,删除主表的一条信息,有外键关系的子表的信息也随之删除

1、建库 if exists(select * from sysobjects where name='temp') drop database temp go create database...

主表有ID关联的表找不到该ID的数据

[DPS-WORKBENCH] 15:26:18,078  WARN org.hibernate.hql.internal.ast.HqlSqlWalker,929 - [DEPRECATION] E...

SQLServer 有物理主外键关系的表在主表改变主键索引时的测试

--先试删除外键,避免后面如删除主表异常 IF OBJECT_ID('FK_MainId') IS NOT NULL BEGIN alter table Sub_Table drop constra...
  • yenange
  • yenange
  • 2014年08月17日 08:27
  • 1012
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:实现删除主表数据时, 判断与之关联的外键表是否有数据引用, 有标志, 无则删除
举报原因:
原因补充:

(最多只允许输入30个字)