none
SQL Exception, Foreign key may cause multiple cascade path, SPECIFY ON DELETE NO ACTION RRS feed

  • Question

  • Hi, please help

    I have got error:

    SQLException was unhandled by user code

    Introducing FOREIGN KEY constraint 'FK_dbo.Prescriptions_dbo.UserProfile_UserId' on table 'Prescriptions' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

    Could not create constraint. See previous errors.

    My error I got when I call database values " var model = db.Appointments.ToList();

     public class HomeController : Controller
        {
            dataSurg db = new dataSurg();
    
            public ActionResult Index()
            {
                var model = db.Appointments.ToList();
                return View(model);
            }
    
    
            public ActionResult CheckResults(AccountController user)
            {
                ViewBag.Message = "Check Results";
    
                var model = new Test()
                {
    
                };
                return View();
            }

    I use vs2012 and MVC4.

    I create Models Appointment, Availability, Prescription etc. then I took User profile from AccountModels where UserId I want to use as a foreign

    key for other table Appointment etc. When I run project and database was automatically created I joined to (Localdb)\v11.0 database named as my DbContext Model:

    public class dataSurg : DbContext
        {
            public DbSet<UserProfile> UserProfiles { get; set; }
            public DbSet<Appointment> Appointments { get; set; }
            public DbSet<Availability> Availabilitys { get; set; }
            public DbSet<Doctor> Doctors { get; set; }
            public DbSet<Medication> Medications { get; set; }
            public DbSet<MedicationList> MedicationLists { get; set; }
            public DbSet<Prescription> Prescriptions { get; set; }
            public DbSet<Test> Tests { get; set; }
        }

    My models include foreign key plus virtual of an object UserProfile

    public class Prescription
        {
            [Key]
            [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
            public int prescriptionId { get; set; }
    
            [Display(Name = "Medication List")]
            [ForeignKey("MedicationList")]
            public int medicationListId { get; set; }
            public virtual MedicationList MedicationList { get; set; }
    
            [ForeignKey("UserProfile")]
            [DisplayFormat(NullDisplayText = "anonymous")]
            public int UserId { get; set; }
            public virtual UserProfile UserProfile { get; set; }
    
            [DataType(DataType.Text)]
            public string drug { get; set; }
            [DataType(DataType.Text)]
            public string dosage { get; set; }
            [DataType(DataType.Text)]
            public string quantity { get; set; }
            
            [Display(Name = "last issued")]
            [DataType(DataType.Text)]
            public DateTime lastIssued { get; set; }
    
            [DataType(DataType.Text)]
            public string status { get; set; }
        }

    My UserProfile Model:

    [Table("UserProfile")]
        public class UserProfile
        {
            [Key]
            [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
            public int UserId { get; set; }
            [DataType(DataType.Text)]
            public string UserName { get; set; }
        }

    My tables in design views on Server Explorer:

    CREATE TABLE [dbo].[UserProfile] (
        [UserId]   INT           IDENTITY (1, 1) NOT NULL,
        [UserName] NVARCHAR (56) NOT NULL,
        PRIMARY KEY CLUSTERED ([UserId] ASC),
        UNIQUE NONCLUSTERED ([UserName] ASC)
    );
    

    CREATE TABLE [dbo].[Prescriptions] (
        [prescriptionId]   INT            IDENTITY (1, 1) NOT NULL,
        [medicationListId] INT            NOT NULL,
        [UserId]           INT            NOT NULL,
        [drug]             NVARCHAR (MAX) NULL,
        [dosage]           NVARCHAR (MAX) NULL,
        [quantity]         NVARCHAR (MAX) NULL,
        [lastIssued]       DATETIME       NOT NULL,
        [status]           NVARCHAR (MAX) NULL,
        CONSTRAINT [PK_dbo.Prescriptions] PRIMARY KEY CLUSTERED ([prescriptionId] ASC)
    );
    

    CREATE TABLE [dbo].[Appointments] (
        [appointmentId] INT            IDENTITY (1, 1) NOT NULL,
        [startDate]     DATETIME       NOT NULL,
        [description]   NVARCHAR (MAX) NULL,
        [doctorId]      INT            NOT NULL,
        [UserId]        INT            NOT NULL,
        CONSTRAINT [PK_dbo.Appointments] PRIMARY KEY CLUSTERED ([appointmentId] ASC)
    );
    

    Wednesday, May 8, 2013 12:42 PM

Answers

  • Hi Mira,

    Could you please post other POCO entity classes you defined?

    One possible reason is that you have a cascade path from medicationListId and UserId columns in table Prescription to the same table. For example MedicationList table has a FK to UserProfile table, so both of the columns go to UserProfile. This exception is from SQL Server, in this scenario if you delete a record of UserProfile, Prescription table will appear twice in cascading referential list.

    To solve the problem, you need to turn off cascade delete on one of the FK relationship. This can only be done using fluent API, for example:

    modelBuilder.Entity<Prescription>()
                .HasRequired(p => p.UserProfile)
                .WithMany()
                .HasForeignKey(p => p.UserId)
                .WillCascadeOnDelete(false);
    Best regards,

    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Friday, May 10, 2013 8:20 AM
    Moderator