想請問這個問題該如何解決

sqlserver

tenno081 2019-03-04 11:00:30 ‧ 1870 瀏覽

是這樣的，我想刪除資料庫中的一筆資料，可是卻跑出這個問題

請問這個該如何處理?

這是我欲刪除資料的table

CREATE TABLE [dbo].[Case] (
    [CaseOid]             BIGINT           IDENTITY (1, 1) NOT NULL,
    [CaseUid]             UNIQUEIDENTIFIER NOT NULL,
    [CustomerUid]         UNIQUEIDENTIFIER NOT NULL,
    [CaseId]              VARCHAR (50)     CONSTRAINT [DF_Case_CaseId] DEFAULT ('') NOT NULL,
    [CaseName]            NVARCHAR (50)    CONSTRAINT [DF_Case_CaseName] DEFAULT ('') NOT NULL,
    [IdNo]                VARCHAR (30)     CONSTRAINT [DF_Case_IdNo] DEFAULT ('') NOT NULL,
    [PatNo]               VARCHAR (30)     CONSTRAINT [DF_Case_PatNo] DEFAULT ('') NOT NULL,
    [CaseNo]              VARCHAR (20)     NULL,
    [Birth]               DATE             CONSTRAINT [DF_Case_Birth] DEFAULT ('1900/01/01') NOT NULL,
    [Age]                 AS               (datediff(year,[Birth],getdate())-case when datediff(day,dateadd(year,datediff(year,[Birth],getdate()),[Birth]),getdate())<(0) then (1) else (0) end),
    [Gender]              VARCHAR (1)      CONSTRAINT [DF_Case_Sex] DEFAULT ('') NOT NULL,
    [Education]           VARCHAR (1)      CONSTRAINT [DF_Case_Education] DEFAULT ('') NULL,
    [Religion]            VARCHAR (1)      CONSTRAINT [DF_Case_Religion] DEFAULT ('') NULL,
    [ReligionNote]        NVARCHAR (20)    NULL,
    [GovCity]             NVARCHAR (10)    CONSTRAINT [DF_Case_GovCity] DEFAULT ('') NOT NULL,
    [GovTownship]         NVARCHAR (10)    CONSTRAINT [DF_Case_GovTownship] DEFAULT ('') NOT NULL,
    [GovAddress]          NVARCHAR (500)   CONSTRAINT [DF_Case_GovAddress] DEFAULT ('') NOT NULL,
    [City]                NVARCHAR (10)    CONSTRAINT [DF_Case_City] DEFAULT ('') NOT NULL,
    [Township]            NVARCHAR (10)    CONSTRAINT [DF_Case_Township] DEFAULT ('') NOT NULL,
    [Address]             NVARCHAR (500)   CONSTRAINT [DF_Case_Address] DEFAULT ('') NOT NULL,
    [Tel]                 VARCHAR (50)     CONSTRAINT [DF_Case_Tel] DEFAULT ('') NOT NULL,
    [Mobile]              VARCHAR (50)     CONSTRAINT [DF_Case_Mobile] DEFAULT ('') NULL,
    [IsMountainAborigine] VARCHAR (1)      NULL,
    [ElderMonthPay]       NVARCHAR (20)    NULL,
    [IncomeSource]        NVARCHAR (50)    NULL,
    [GovAllowance]        NVARCHAR (50)    NULL,
    [OtherIncome]         NVARCHAR (50)    NULL,
    [Note]                NVARCHAR (500)   NULL,
    [Status]              CHAR (1)         CONSTRAINT [DF_Case_Status] DEFAULT ('') NOT NULL,
    [CaseStatus]          VARCHAR (5)      CONSTRAINT [DF_Case_CaseStatus] DEFAULT ('') NOT NULL,
    [CaseType]            VARCHAR (5)      CONSTRAINT [DF_Case_CareType] DEFAULT ('') NOT NULL,
    [LastCaseIpdNo]       VARCHAR (11)     CONSTRAINT [DF_Case_LastCaseIpdNo] DEFAULT ('') NULL,
    [LastCaseIpdUnit]     VARCHAR (10)     CONSTRAINT [DF_Case_LastCaseIpdUnit] DEFAULT ('') NULL,
    [LastCaseStaName]     NVARCHAR (20)    CONSTRAINT [DF_Case_LastCaseStaName] DEFAULT ('') NULL,
    [LastCaseBed]         NVARCHAR (20)    CONSTRAINT [DF_Case_LastCaseBed] DEFAULT ('') NULL,
    [ChronicDiseases]     VARCHAR (2)      NULL,
    [TakeMedicine]        VARCHAR (2)      NULL,
    [Allergies]           VARCHAR (2)      NULL,
    [AttackBehavior]      VARCHAR (2)      NULL,
    [AttackBehaviorNote]  NVARCHAR (50)    NULL,
    [Suicidal]            VARCHAR (2)      NULL,
    [SuicidalNote]        NVARCHAR (50)    NULL,
    [LostRisk]            VARCHAR (2)      NULL,
    [LostRiskNote]        NVARCHAR (50)    NULL,
    [LastCaseUnit]        VARCHAR (5)      CONSTRAINT [DF_Case_LastCaseUnit] DEFAULT ('') NULL,
    [DateofApplication]   DATE             NULL,
    [OpeningDate]         DATE             NULL,
    [CreateTime]          DATETIME         NOT NULL,
    [CreateUserUid]       UNIQUEIDENTIFIER NOT NULL,
    [ModifyTime]          DATETIME         NULL,
    [ModifyUserUid]       UNIQUEIDENTIFIER NULL,
    CONSTRAINT [PK_Case] PRIMARY KEY NONCLUSTERED ([CaseUid] ASC),
    CONSTRAINT [CI_Case] UNIQUE CLUSTERED ([CaseOid] ASC)
);


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'教育程度', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Case', @level2type = N'COLUMN', @level2name = N'Education';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'是否山地原住民(''Y''or''N'')', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Case', @level2type = N'COLUMN', @level2name = N'IsMountainAborigine';

上面顯示起衝突的table

CREATE TABLE [dbo].[CaseLanguage] (
    [CaseLanguageOid] BIGINT           IDENTITY (1, 1) NOT NULL,
    [CaseLanguageUid] UNIQUEIDENTIFIER NOT NULL,
    [CaseUid]         UNIQUEIDENTIFIER NOT NULL,
    [LanguageId]      NVARCHAR (10)    NOT NULL,
    [LanguageName]    NVARCHAR (10)    NOT NULL,
    [Note]            NVARCHAR (500)   NULL,
    [CreateTime]      DATETIME         NOT NULL,
    [CreateUserUid]   UNIQUEIDENTIFIER NOT NULL,
    [ModifyTime]      DATETIME         NULL,
    [ModifyUserUid]   UNIQUEIDENTIFIER NULL,
    CONSTRAINT [PK_CaseLanguage] PRIMARY KEY NONCLUSTERED ([CaseLanguageUid] ASC),
    CONSTRAINT [CI_CaseLanguage] UNIQUE CLUSTERED ([CaseLanguageOid] ASC),
    CONSTRAINT [FK_CaseLanguage_Case] FOREIGN KEY ([CaseUid]) REFERENCES [dbo].[Case] ([CaseUid])
);

dennis124599 iT邦新手 5 級 ‧ 2019-03-04 11:19:48 檢舉

HI! 你可以試試 ALERT 這個TABLE 把 [FK_CaseLanguage_Case] 的 FOREIGN KEY移除再刪除看看。

暐翰 iT邦大師 1 級 ‧ 2019-03-04 11:25:08 檢舉

沒有特殊原因不建議這樣做,這樣會造成髒資料

dennis124599 iT邦新手 5 級 ‧ 2019-03-04 16:18:39 檢舉

SORRY，發現自己沒有看清楚問題，謝前輩糾正。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2019-03-04 11:41:55

最佳解答

根據這篇的話
是修改(drop-add)CaseLanguage的constraint
詳細如下

ALTER TABLE dbo.CaseLanguage
   DROP CONSTRAINT FK_CaseLanguage_Case

ALTER TABLE dbo.CaseLanguage
   ADD CONSTRAINT FK_CaseLanguage_Case
   FOREIGN KEY (CaseUid) REFERENCES dbo.Case(CaseUid) ON DELETE CASCADE

修改完成之後
只要 Delete dbo.Case 時
就會連帶 delete CaseLanguage 裡對應的那一筆一併刪除

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

tenno081 iT邦研究生 4 級 ‧ 2019-03-04 12:00:34 檢舉

想請問一下，第一段是移除掉我這個FK

然後第二段是再次新增FK然後加入條件這樣嗎?

暐翰 iT邦大師 1 級 ‧ 2019-03-04 12:13:27 檢舉

海綿寶寶大大的方式可以解決問題
這邊幫忙補上線上測試連結 : db<>fiddle

在我印象中不會這樣做,加上delete cascade很容易刪除表中的所有數據。

舉例
有部門表格跟員工表格,假如加上delete cascade
有人不小心下delete 部門,員工表格資料會跟著刪除,不會有報錯提醒

海綿寶寶 iT邦大神 1 級 ‧ 2019-03-04 15:40:28 檢舉

想請問一下，第一段是移除掉我這個FK
然後第二段是再次新增FK然後加入條件這樣嗎?

就是這個意思

tenno081 iT邦研究生 4 級 ‧ 2019-03-04 17:28:08 檢舉

我懂了，感謝

登入發表回應

暐翰

iT邦大師 1 級 ‧ 2019-03-04 11:20:06

提供我個人習慣給大大參考:

新增資料邏輯需要先新增一方資料才能新增多方資料
刪除資料邏輯跟新增資料相反,要先刪除多方資料,才能刪除一方資料,假如沒有這麼做,刪除時有FOREIGN KEY欄位會此錯誤,資料庫這樣設計就是要防止髒資料。

目前看起來你的一個case會有多個語言,所以才額外設計CaseLanguage多方表格
所以想要刪除case資料,你要先從CaseLanguage表格資料刪除再刪除case表格資料

另外小補充,記得下select into備份資料,避免之後使用者反悔,臨時又要把資料找回情況,做一個保險。

以下是測試Script

--測試DDL
CREATE TABLE [dbo].[Case] (
    [CaseOid]             BIGINT           IDENTITY (1, 1) NOT NULL,
    [CaseUid]             UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Case] PRIMARY KEY NONCLUSTERED ([CaseUid] ASC),
    CONSTRAINT [CI_Case] UNIQUE CLUSTERED ([CaseOid] ASC)
);

CREATE TABLE [dbo].[CaseLanguage] (
    [CaseLanguageOid] BIGINT           IDENTITY (1, 1) NOT NULL,
    [CaseLanguageUid] UNIQUEIDENTIFIER NOT NULL,
    [CaseUid]         UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_CaseLanguage] PRIMARY KEY NONCLUSTERED ([CaseLanguageUid] ASC),
    CONSTRAINT [CI_CaseLanguage] UNIQUE CLUSTERED ([CaseLanguageOid] ASC),
    CONSTRAINT [FK_CaseLanguage_Case] FOREIGN KEY ([CaseUid]) REFERENCES [dbo].[Case] ([CaseUid])
);

insert into [Case] ([CaseUid]) values ('62338D53-E01A-4765-97CF-104A4C1BC5E3'  );
insert into [CaseLanguage] ([CaseLanguageUid],[CaseUid]) values (NEWID ( ) ,'62338D53-E01A-4765-97CF-104A4C1BC5E3' );


--先刪除一方資料會報547錯誤
delete [Case] where [CaseUid] = '62338D53-E01A-4765-97CF-104A4C1BC5E3' --The DELETE statement conflicted with the REFERENCE constraint "FK_CaseLanguage_Case". The conflict occurred in database "TEST", table "dbo.CaseLanguage", column 'CaseUid'.

--正常刪除,從多方資料刪除後再刪除一方資料
delete [CaseLanguage] where [CaseUid] = '62338D53-E01A-4765-97CF-104A4C1BC5E3' 
delete [Case] where [CaseUid] = '62338D53-E01A-4765-97CF-104A4C1BC5E3'