sql - 将 ON DELETE CASCADE 添加到同一个表中的 2 个外键

Question

我这里有 3 个表是它们的 SQL

CREATE TABLE [dbo].[User]
(
    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY , 
    [Email] VARCHAR(50) NOT NULL unique, 
    [UserName] VARCHAR (50) NOT NULL Unique,
    [Password] VARCHAR(50) NOT NULL, 
    [FirstName] VARCHAR(50) NOT NULL, 
    [LastName] VARCHAR(50) NOT NULL, 
    [PhoneNumber] VARCHAR(50) NOT NULL unique, 
    [Photo] VARCHAR(MAX) NULL, 
    [UserType] VARCHAR(50) NOT NULL, 
);


CREATE TABLE [dbo].[JobPost]
(
    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [ClientId] INT NOT NULL, 
    [JobType] VARCHAR(50) NOT NULL, 
    [JobTitle] VARCHAR(50) NOT NULL, 
    [JobBudget] INT NOT NULL, 
    [JobDate] VARCHAR(50) NOT NULL, 
    [JobDes] VARCHAR(500) NOT NULL, 
    [PropNum] INT NOT NULL DEFAULT 0, 
    [Rate] FLOAT NOT NULL DEFAULT 0,
    [Approved] INT NOT NULL DEFAULT 0,
    [Taken] INT NOT NULL DEFAULT 0,
    CONSTRAINT [FK_JobPost_ToTable] FOREIGN KEY ([ClientId]) REFERENCES [User]([Id]) ON DELETE CASCADE

    
);


CREATE TABLE [dbo].[Proposal] (
    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [JobId]        INT NOT NULL,
    [FreelancerId] INT NOT NULL,
    [Approved] INT NOT NULL DEFAULT 0,
    CONSTRAINT [FK_Proposal_ToTable] FOREIGN KEY ([JobId]) REFERENCES [dbo].[JobPost] ([Id]),
    CONSTRAINT [FK_Proposal_ToTable_1] FOREIGN KEY ([FreelancerId]) REFERENCES [dbo].[User] ([Id]) ON DELETE CASCADE
);

当我删除用户记录或职位发布记录时，我想删除提案表中的一条记录，但我不能在它说的两个外键上都使用 DELETE CASCADE

表 'Proposal' 上的 FOREIGN KEY 约束 'FK_Proposal_ToTable' 可能会导致循环或多个级联路径。指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束。

我希望桌子是这样的

CREATE TABLE [dbo].[Proposal] (
    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [JobId]        INT NOT NULL,
    [FreelancerId] INT NOT NULL,
    [Approved] INT NOT NULL DEFAULT 0,
    CONSTRAINT [FK_Proposal_ToTable] FOREIGN KEY ([JobId]) REFERENCES [dbo].[JobPost] ([Id]),
    CONSTRAINT [FK_Proposal_ToTable_1] FOREIGN KEY ([FreelancerId]) REFERENCES [dbo].[User] ([Id]) ON DELETE CASCADE
);

标签： sqlsql-serversql-delete