约束-更新-级联 [英] Constrains - Update - Cascade

问题描述

嗨.我有麻烦了我已经完成了一些数据库，并且已经完成了许多FK的表(没有更新级联的选项).因此，我有一些多余的数据(例如状态，该状态不存在)，并且此多余的数据与其他表有关.因此，我需要为一些默认密钥(999)更改多余数据的主键.哪一种是最好的方法?修改每个约束?写50个更新句子?还是?

Hi. I have some trouble. I have some database done and it has some table done with many FK (without option of update cascade). So i have some excess data (for example state, that doesn''t exists) and this excess data is related with other tables. So i need to change primary key for excess data for some default key (999). Which would be the best way to do it? Modify every constraint? Write 50 update sentences? Or?

推荐答案

已解决.首先，我得到有关FK的sysinfo.像这样:

Solved. First i get sysinfo about FK. Like this:

SELECT     A.FOREIGN_KEY_NAME, B.TABLE_NAME, B.COLUMN_NAME, A.REFERENCED_TABLE, A.REFERENCED_COLUMN FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS B RIGHT OUTER JOIN (SELECT DISTINCT so.name AS FOREIGN_KEY_NAME, OBJECT_NAME(sf.rkeyid) AS REFERENCED_TABLE, sc2.name AS REFERENCED_COLUMN FROM sysobjects AS so INNER JOIN sysforeignkeys AS sf ON so.id = sf.constid INNER JOIN syscolumns AS sc1 ON sf.fkeyid = sc1.id INNER JOIN syscolumns AS sc2 ON sf.rkeyid = sc2.id WHERE (so.xtype = ''F'') AND (OBJECT_NAME(sf.rkeyid) = ''ESTADO'') AND (sc2.name = ''ESTADO'')) AS A ON B.CONSTRAINT_NAME = A.FOREIGN_KEY_NAME

然后，对于目录的每一行，我检查它是好"还是坏"，如果不好，那么我会进行foreach循环:

Then, for every row of my catalog i check if it''s "good" or "bad", if it''s bad, than i make foreach cycle:

foreach (DataRow DR_fk in DT_state_relation.Rows)
 {
  cmd = new SqlCommand("UPDATE bts." + DR_fk["TABLE_NAME"] + " SET " + DR_fk["COLUMN_NAME"] + " = 99 WHERE " + DR_fk["COLUMN_NAME"] + " = " + DR["ESTADO"], con);
 cmd.ExecuteNonQuery();
 }
 cmd = new SqlCommand("DELETE FROM bts.ESTADO WHERE ESTADO = " + DR["ESTADO"]);
 cmd.ExecuteNonQuery();

这篇关于约束-更新-级联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！