SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot //andrew-p//'s Avatar
    Join Date
    Dec 2002
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    "Cascading Deletes" in Access

    I have an Access database (that serves as the backend for a web application) that has a number of relationships in it. I have drawn them all in the Relationships window in Access, but I now want to enable the "Enforce referential integrity" feature to cascade all deletes in a parent table to all child records in another table. Here is a snippet of my database structure:
    Code:
    Students
    -----------
    StudentID
    FirstName
    LastName
     
    Courses
    -----------
    CourseID
    CourseName
    CourseNumber
     
    CR_StudentsCourses
    --------------------
    CourseID (fk to Courses.CourseID)
    StudentID (fk to Students.StudentID)
     
    Averages
    -------------
    StudentID (fk to CR_StudentsCourses.StudentID)
    CourseID (fk to CR_StudentsCourses.CourseID)
    Average
    My problem is this: I want to I use cascading deletes so that when I delete a record from CR_StudentsCourses, all records for that StudentID and CourseID combination are removed from the Averages table. As it stands, I only know how to enforce integrity based on a single related row, so I fear that a delete from CR_StudentsCourses will remove all Averages records for that StudentID, regardless of the CourseID.

    Any help/suggestions needed badly, if you follow me...
    "When the ship misses the harbor, it is seldom the fault of the ship, and it is never the fault of the harbor..."
    //think of that when you get an error

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you have to do is make sure the primary key of CR_StudentsCourses is the combination of CourseID and StudentID, and that the Averages table is related to that primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot //andrew-p//'s Avatar
    Join Date
    Dec 2002
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm...sorry for my stupidity, but how do you do that?
    "When the ship misses the harbor, it is seldom the fault of the ship, and it is never the fault of the harbor..."
    //think of that when you get an error

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    open each table in access design view, and make sure it has the right primary key

    looks to me like both CR_StudentsCourses and Averages have the same, composite pk (why, by the way? seems like there's an extra table in there -- can the same student have more than one average in the same course?)

    in the Relationship tool, highlight both columns in CR_StudentsCourses, then click and drag and drop onto the Averages table

    the Relationships window pops up, make sure each column is paired with the right column, then check the Enforce Relational Integrity checkbox

    rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •