问题描述:
某个基础信息表,与系统中30多个表存在外键关系,当删除基础数据时,需要判断是否已经被用过,如果用过则更改标志位,如果没有用过则直接删除,如何能很好实现这个处理?最好能够自动适应表的变化
-- SQL Server 2005的错误处理容易控制, 因此, SQL Server 2005中可以直接删除, 通过错误处理来确定是否需要更新.
-- 示例如下.
USE tempdb
GO
CREATE TABLE m(
id int PRIMARY KEY,
bz bit)
INSERT m SELECT 1, 0
<place w:st="on"><span lang="EN-US" style='FONT-SIZE: 9pt; COLOR: blue; FONT-FAMILY: "Courier New"; mso-font-kerning: 0pt; mso-no-proof: yes'>UNION</span></place> 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 对错误处理不好控制, 一般还是建议做判断
-- 通过系统表查询系统表,可以获取某个表关联的所有外键表
-- 示例存储过程
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)
分享到:
相关推荐
删除表不是特别常用,特别是对于存在外键关联的表,删除更得小心。但是在开发过程中,发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情;另外在测试的时候,也有需要重新创建数据库...
大数据工具之HIVE外键及多表关联的介绍.zip
父表中的外键,是子表中的主键。在父表中的外键,可以出现多次。主键是不能有重复的。
项目中需要把一个数据库的部分表插入到另外一个数据库,设计到外键约束,写了一个程序对主表子表进行排序,插入的时候再也不用去考虑外键约束问题了
知识点很简单,但是语句总忘,在这里写一个小例子,有助于大家记忆。mySql主外键关联例子
删除数据库的所有表、存储过程、主键、外键、索引等
使用eclipse获取mysql数据库中的表结构以及主外键
Android ORMLite框架入门用法教程二(两张表关联外键)实例Demo,详情请看博客教程
需求是这样的,一个资源表有分类r_class_id(分类表id对应的外键) 创建者r_up_creator(用户表id对应的外键) 审核者r_ver_id (用户表id对应的外键),这时候该怎么将其都查询出来呢? 首先用左连接: select ...
hibernate基于主外键的一对多/多对一关联
就是为程序员提供了一条可以查询表的主键被其他表引用为外键情况的查询
在数据库MySQL中利用外键实现级联删除、更新等方面的内容。有兴趣可以看看。。
表结构查询,主外键关联表名查询,
为mysql数据表建立主外键需要注意以下几点: 需要建立主外键关系的两个表的存储引擎必须是InnoDB。 外键列和参照列必须具有相似的数据类型,即可以隐式转换的数据类型。 外键列和参照列必须创建索引,如果外键列...
SQL Server 数据完整性介绍,主键和外键介绍,数据类型介绍
Mysql数据库如何删除某一个数据库的所有外键并不删除相应的索引,一个sql脚本解决,无需知道外键的名称。
grails生成的ddl.sql,整理出其中的外键关联关系
在MySQL中删除一张表或一条数据的时候,出现 [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...) 这是因为MySQL中设置了foreign key关联,造成无法更新或删除数据。可以通过设置...
Java~主外键关系~有图解~主外键关系~主外键关系
NULL 博文链接:https://bicashy.iteye.com/blog/1064355