Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2016
    Posts
    4

    Unanswered: The INSERT statement conflicted with the FOREIGN KEY constraint

    I have four tables right now: Students, Courses, Assignments and Semesters. I have a M:N relationship between Students and Courses that is called Enrollment. I have a junction table with the same name. I would like to have a 1:M relationship between Assignments and Enrollment. The assignments table contains work that the student does which is what their calculated grade is based on. My problem is that when I try to populate the Enrollment table, I get the error The INSERT statement conflicted with the FOREIGN KEY constraint "Has Scores". The conflict occurred in database "midterm_project", table "dbo.Assignments", column 'enrollmentId'. If I understand correctly, it wants me to populate the Assignments table first... right? If this is the case, then it makes no sense to me because in real life, a student can't submit their work before they are enrolled in a course. How do I need to change my design for it to allow me to populate the Enrollment table before I populate the Assignments table?

    Here is my design:
    Click image for larger version. 

Name:	dbmodel.JPG 
Views:	8 
Size:	47.0 KB 
ID:	16796

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Try this approach

    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You did quite well on this point! I am impressed – most of the crap on SQL forums looks like 1960's FORTRAN. I know; that is where I started

    But we have to print out your E-R picture and transcribe by hand into DDL, then guess at the missing parts! This is a pain.

    I have four tables right now: Students, Courses, Assignments and Semesters. I have a M:N relationship between Students and Courses that is called Enrollment.
    Where is the DDL to enforce this? I also loved the name “enrollment”; so many people use crap like “StudentCourse” instead of thinking at a higher level of abstraction.

    I have a junction table with the same name.
    NO! A junction table is a term from network database in the 1970's. They were highly proprietary pointer structures that mimicked COBOL OCCURS clauses and let the host program assemble COBOL records.

    SQL has referenced and referencing tables, with DRI actions between PK and FK; no links (more pointer mindset!) or toehr old network stuff.

    >> I would like to have a 1:M relationship between Assignments and Enrollment. <<

    Then we need a relationship table .. Codd's Information Principle and all that jazz.

    The assignments table contains work that the student does which is what their calculated grade is based on.
    So the key is a compound MVD (student, course) →→ (assignment)? You might want to look up Armstrong's Axioms.

    My problem is that when I try to populate the Enrollment table, I get the error The INSERT statement conflicted with the FOREIGN KEY constraint "Has Scores".
    The conflict occurred in database "midterm_project", table "dbo.Assignments", column 'enrollment_id'. If I understand correctly, it wants me to populate the Assignments table first... right? If this is the case, then it makes no sense to me because in real life, a student can't submit their work before they are enrolled in a course. How do I need to change my design for it to allow me to populate the Enrollment table before I populate the Assignments table?


    Semester are limited, static and known; why waste a table on them?

    CREATE TABLE Courses
    (course_id CHAR(5) NOT NULL,
    semester_nbr CHAR(5) NOT NULL
    CHECK(semester_nbr LIKE '[12][0-9][0-9][0-9]S[1-3]'),
    PRIMARY KEY (course_id, semester_id)
    course_name VARCHAR(15) NOT NULL);

    CREATE TABLE Students
    (student_id CHAR(10) NOT NULL PRIMARY KEY,
    first_name VARCHAR (15) NOT NULL,
    last_name VARCHAR (15) NOT NULL);

    Why do you think that we need or even want redundant "enrollment_id"? We want DRI actions! Your mindset is still back in the old pointer chains and networks.

    CREATE TABLE Enrollments
    (course_id CHAR(5) NOT NULL,
    semester_nbr CHAR(5) NOT NULL,
    FOREIGN KEY (course_id, semester_nbr)
    REFERENCES Courses (course_id, semester_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    student_id CHAR(10) NOT NULL
    REFERENCES Students (student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    PRIMARY KEY (course_id, semester_id, student_id)
    );

    This next table gets tricky. Do the E-R diagram to follow the actions. Look at the compound keys!

    CREATE TABLE Assignments
    (assignment_name VARCHAR(15) NOT NULL,
    possible_points INTEGER NOT NULL
    CHECK(possible_points >= 0),
    earned_points INTEGER NOT NULL
    CHECK(earned_points >= 0),
    CHECK(possible_points >= earned_points),
    course_id CHAR(5) NOT NULL,
    semester_nbr CHAR(5) NOT NULL,
    FOREIGN KEY (course_id, semester_nbr)
    REFERENCES Courses (course_id, semester_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    student_id CHAR(10) NOT NULL
    REFERENCES Students (student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    PRIMARY KEY (course_id, semester_id, student_id)

    FOREIGN KEY(course_id, semester_id, student_id)
    REFERENCES Enrollments (course_id, semester_id, student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

Posting Permissions

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