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