The tables that I synched to did not have enough constraint. There were many duplicate records in the enrollments table, and even some in the users table.
Eventually, I ended up punting the existing data to the curb and recreating the two tables but this time with proper constraints to prohibit duplicate records. Garbage in, garbage out.
Initially, I tried adding another primary key to the table, but I had trouble. I tried selected two of the existing fields in the table and making them a compound primary key, which would have worked, had I not already had a primary key on the table.
I tried for awhile, deleting all the duplicate records that had been added, but that, while oddly satisfying was not the solution.
Eventually, I realized what I wanted to do to both tables was to leave the existing primary key alone and add a UNIQUE INDEX rule to the DB. This rule would state the content of this field and that field must be unique. All ways so obvious in hindsight.
MySQL Command - syntax
ALTER TABLE tablename ADD UNIQUE INDEX(fieldname);
I actually used a 3 field combination like this:ALTER TABLE tablename ADD UNIQUE INDEX(username,courseid,role);
Now, when my customer tries to add a new enrollment to a course, and there is already an enrollment for that name in that course in that role, the DB will fail quietly, meaning is simply will not add the record.This is the alter view of the enrollments table.
Using MySQL workbench, you can see the index name, notice the Indexes tab selected along the bottom of image, then the index name and type along the top left. In the center pain, you can see the 3 columns from the table, username, courseid and role. Together, they comprise the unique index "rule" for this table.
In the other table, it looks like this. (In MySQL workbench), right click the table, choose Alter table.
I actually created two Unique Indexes on the table. each index is named the name of the column that it is indexing.
No comments:
Post a Comment