SQL server 由于外键约束，不能修改，删除表数据的解决方法

1.查询出所有外键禁用的sql

select 'ALTER TABLE [' + b.name + '] NOCHECK CONSTRAINT ' + a.name +';' as 禁用约束
from sysobjects a ,sysobjects b 
where a.xtype ='f' and a.parent_obj = b.id

执行结果如图所示:

 

 

2.查看1中的执行结果，copy你需要禁用外键的sql,如我需要禁用的外键为FK_Retail_Applyer_Retail_Applyer，则执行

ALTER TABLE [Retail_Applyer] CHECK CONSTRAINT FK_Retail_Applyer_Retail_Applyer;

 

3.然后执行插入语句

SET IDENTITY_INSERT Retail_Applyer ON; 
ALTER TABLE [Retail_Applyer] NOCHECK CONSTRAINT FK_Retail_Applyer_Retail_Applyer; INSERT INTO Retail_Applyer ([ApplyerID], [Name], [CertType], [CertNo], [Sex], [Birthday], [MarriageStatus], [DrivingLicenseHas], [DrivingLicenseNo], [Education], [SpouseID], [MailAddress], [JobIndustry], [JobCompany], [JobPosition], [JobEntryDate], [JobTitle], [JobCompIndustry], [JobCompProvince], [JobCompCity], [JobCompPhoneNo], [JobCompZip], [JobCompAddress], [JobCompAreaNo], [CurrProvince], [CurrCity], [CurrEntryDate], [CurrAddressZip], [CurrLivingCondition], [CurrAddress], [LastProvince], [LastCity], [LastEntryDate], [LastAddressZip], [LastAddress], [PhoneNo], [CellNo], [Email], [RegiProvince], [RegiCity], [RegiPhoneNo], [RegiZip], [RegiAddress], [IncomeYear], [IncomeOther], [FamilyMembers], [FamilyLivingTogether], [BankOpen], [BankBranch], [BankAccount], [BankOpenerName], [idCardCheckFlg], [CusType], [MainIncomeFrom], [OwnEstate], [PropertyOwner], [PropertyLoan], [PropertyAddType], [OtherPropertyAddress], [LastUpdBy], [LastUpdTime], [DesignaterName], [DesignaterIDNo], [DesignaterMobile], [DesignaterPhone], [PropertyLocation], [RegiDistrict], [PSBCrawlMark], [courtCrawlMark], [creditCrawlMark], [token], [DrivingLicenseDocNo], [CellNo1], [CellNo2], [CellNo3], [CellNo4], [CellNo5], [CellNo6], [CellNo7], [CellNo8], [CellNo9], [RetailApplyerTagId], [UPDTIM], [isExsitingCA], [certStartDate], [certEndDate], [isOcrIdentify], [nationality], [degree], [companyProperty], [certBackUrl], [certFrontUrl], [JobCompDistrict], [JobCompStreet], [JobCompGroup], [JobCompRoom], [ContractAddress], [jointLessees]) VALUES ('2226', N'张三', 'IdentityCard', '111111199101015618', 'Male', '19910101', 'Married', 'Has', NULL, 'Doctor', '2227', 'CurrAddr', 'GovernmentEmployee', 'Jdjdjj', 'Seniormanager', NULL, 'Juniortitle', 'AgriForestFarmFish', '340000', '340800', '60748500', NULL, '111乡111路111号111室', '6310', '340000', '340800', '20200619', '265544', 'OwnProperty', 'Kckxk', NULL, NULL, NULL, NULL, NULL, '66880000', '15900726742', '444@sina.com', '340000', '340800', '60845555', NULL, '646646', '100000', '0', NULL, NULL, 'NongYe', '农业银行上海分行', '6202130102404105099', N'张晨凯', N'N', '30', 'Salary', 'N', '', '', '', '', NULL, NULL, '', '', '', '', '', '340871', NULL, NULL, NULL, NULL, '64646646', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '973', '2020-07-07 20:04:45.047', NULL, '19960111', '20280111', 'N', NULL, NULL, NULL, NULL, NULL, '111乡', '111路', '111号', '111', 'CompAddr', NULL); 

SET IDENTITY_INSERT Retail_Applyer OFF;

4.执行完插入语句后，需要启用之前禁用掉的外键即可

ALTER TABLE [Retail_Applyer] CHECK CONSTRAINT FK_Retail_Applyer_Retail_Applyer;

注：启用所有外键的sql

select  'ALTER TABLE [' + b.name +  '] CHECK CONSTRAINT ' +  a.name +';' as  启用约束     
from  sysobjects  a ,sysobjects  b     
where  a.xtype ='f' and  a.parent_obj = b.id

 查询对应表关联的外键约束情况

select
    fk.name,fk.object_id,OBJECT_NAME(fk.parent_object_id) as referenceTableName
from sys.foreign_keys as fk
join sys.objects as o on fk.referenced_object_id=o.object_id
where o.name='Retail_Applyer'