Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    4

    Question Forgin Key Constraints

    I'm having a problem deleting from a database. Here are the tables:
    (Not all fields are shown)

    Student 1 ------ M Enrolled M ------ 1 Course

    Student(mcp, name, phone etc..)

    Enrolled(mcp,abbr,passfail)

    Course(abbr,start_date,end_date)

    Enrolled is a bridge entity for the many to many relationship between Student and Course. I want to delete Student records but keep all info in the Enrolled table intact. I cant delete because Enrolled.mcp is referencing Student.mcp. Is there a wasy around this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Forgin Key Constraints

    Originally posted by alzoid
    I'm having a problem deleting from a database. Here are the tables:
    (Not all fields are shown)

    Student 1 ------ M Enrolled M ------ 1 Course

    Student(mcp, name, phone etc..)

    Enrolled(mcp,abbr,passfail)

    Course(abbr,start_date,end_date)

    Enrolled is a bridge entity for the many to many relationship between Student and Course. I want to delete Student records but keep all info in the Enrolled table intact. I cant delete because Enrolled.mcp is referencing Student.mcp. Is there a wasy around this?
    You have a choice with foreign keys:
    (by default) -- prevents deletion of master when details exist
    ON DELETE CASCADE -- delete the details as well as the master
    ON DELETE SET NULL -- set the FK column to NULL in the detail table(s) and delete the master

    What you cannot do is delete student 123 from Student and leave the value 123 in Enrolled.mcp - well you can, if you don't define a foreign key at all! That would be a little unconventional though!

  3. #3
    Join Date
    Apr 2003
    Posts
    4
    Should I use a field to record students/courses I dont want any more? Something like "Inactive"

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by alzoid
    Should I use a field to record students/courses I dont want any more? Something like "Inactive"
    Sounds like a good idea. Since you want to keep the Enrollment data, you need to keep the Student and Course data it relates to. The Inactive column would enable queries against current students/courses only. Alternatively, you could have an END_DATE in each table to show when they left / ceased to be taught.

  5. #5
    Join Date
    Apr 2003
    Posts
    4
    Thank you for you help and quick replies

    Al

Posting Permissions

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