0

I am using MySQL workbench to manage my a database that was handed down to me for a development task. Unfortunately, the schema is a nightmare: no primary keys for numerous tables, lots of column duplication, etc.

First off, I wanted to add some uniqueness so that I can begin normalizing somehow. I have a 'students' table where a student (with an ID) works on a project that belongs to a specific term (Fall 2014, Spring 2015, etc.)

Since the same student can work on the same project two semesters in a row for whatever reason, that only way to tell them apart would be to have a (student ID, term) PK. So I actually do need that composite PK.

How might I alter the existing tables and set a composite PK?

EDIT:

To clarify: the schema contains a users table with actual student information (First/Last name, Email, Term). The students table would more aptly be named projects, as it references only the students by ID and then lists the project they worked on, in the semester that they worked on it. So at the very least, students.id would also be a FK from users.

I know the above doesn't quite make any sense; I'm just trying to keep this to one step at a time because the application depends on the schema and I don't want to introduce any new bugs at this point.

To clarify even further, here is how the users and students tables look like:

students

id        project        termInitiated        ...
20        XYZ            Summer 2013
20        XYZ            Fall 2013
23        ABC            Fall 2013
24        ABC            Fall 2014
...

users

studentId        firstName        lastName        termInitiated
20               A                AA              Summer 2013
20               A                AA              Fall 2013
23               Z                ZZ              Fall 2013
24               Y                YY              Fall 2014
...

Unfortunately, due to the way it is setup, I cannot have studentId be a PK by itself as the same student could be working on the same project multiple semesters in a row.

The best fix to this would be a globally unique identifier that could refer to the same student in different terms, but this would introduce a huge amount of bugs right now that I do not have the time to fix. Thus, I think that a composite PK would be the best solution given my limitations.

filpa
  • 3,651
  • 8
  • 52
  • 91
  • Your approach does not make sense. If your `students` table contains *foreign* data then split it into a separate table. – PM 77-1 Feb 06 '15 at 05:35
  • Please see my edit, as it was rather long to put as a comment. – filpa Feb 06 '15 at 05:41
  • You may want to check adrian's answer here: http://stackoverflow.com/questions/8859353/alter-table-to-add-a-composite-primary-key – Syed Osama Maruf Feb 06 '15 at 05:47
  • @SyedOsamaMaruf I did attempt that same SQL code, but it does not seem that it has worked. As my table was created with no PKs, it was set to read-only mode. Since I tried to add a composite PK using Adrian's code, it is still in read-only mode despite having the PK. – filpa Feb 06 '15 at 05:52
  • @SyedOsamaMaruf Nevermind! It seems there was a slight hiccup with Workbench, Adrian's answer did indeed work. If you wish, you can post something similar (or even a link to his answer) and I'll pick your answer. Otherwise, I'll close this as a duplicate as soon as I can. Thanks! – filpa Feb 06 '15 at 06:00

2 Answers2

2

You may need to grant yourself the alter privilege if the composite key is not being added take a look at this: https://kb.mediatemple.net/questions/788/How+do+I+grant+privileges+in+MySQL%3F#dv Here is adrian's link :ALTER TABLE to add a composite primary key

Community
  • 1
  • 1
Syed Osama Maruf
  • 1,895
  • 2
  • 20
  • 37
0

My suggestion is that, add a new field in your table for primary key with the unique combination of two fields. For example in your case, add a field suppose pid in students table:

ALTER TABLE students
ADD pid VARCHAR(100) UNIQUE PRIMARY KEY;

And then update this table:

UPDATE students SET pid=CONCAT(id, '-', termInitiated);

So you will have unique combination of id and termInitiated in pid field. You can use it as primary key and if you want to select it or join it with other table, you can reference it with combination of two field. For example, If you want to join student table with users, you can join it like:

SELECT * FROM users
INNER JOIN students
ON CONCAT(users.studentId, '-', termInitiated) = student.pid);

I hope this will work for you. Please correct/suggest me, I am wrong.

Thank you.

BabyDuck
  • 1,249
  • 1
  • 9
  • 22