SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--測試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' );

2 rows affected

--先刪除一方資料會報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'.
Msg 547 Level 16 State 0 Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_CaseLanguage_Case". The conflict occurred in database "fiddle_dde654ff1a0748a09bb3d478bfccc8e4", table "dbo.CaseLanguage", column 'CaseUid'.
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.

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

--連鎖刪除
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
insert into [Case] ([CaseUid]) values ('62338D53-E01A-4765-97CF-104A4C1BC5E3'  );
insert into [CaseLanguage] ([CaseLanguageUid],[CaseUid]) values (NEWID ( ) ,'62338D53-E01A-4765-97CF-104A4C1BC5E3' );
2 rows affected

delete [Case] where [CaseUid] = '62338D53-E01A-4765-97CF-104A4C1BC5E3' 

select * from [Case] --0 row
select * from [CaseLanguage] --0 row


CaseOid
CaseUid




CaseLanguageOid
CaseLanguageUid
CaseUid