0

i Have 3 tables here is the SQL of them

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
);

i want to delete a record in the proposal table when i delete either the user record or the jobpost record but i can't put on DELETE CASCADE on both the foreign keys it says

FOREIGN KEY constraint 'FK_Proposal_ToTable' on table 'Proposal' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

i want the table to be like this

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
);

0 Answers0