MYSQL: Creating a list of integers (IDs) which can use foreign key constraints for each integer element?

I was wondering if there is a way to create a foreign key for a list of integers that responds on single integer elements of the list.

For example, I have an "exercises" table. On that table, I would like to maintain two related columns: "exercisesID" "relatedExerciseIDs"

However, "relatedExerciseIDs" is a VARCHAR containing comma-delimited "exerciseID"s. On a deletion of an exercise from the table, any exercises with the deleted "exerciseID" in their "relatedExerciseIDs" list should remove it.

Is this possible? How can I do this?

Thanks for your opinions! I would also be interested in using a column type othe开发者_开发问答r than a VARCHAR if shown possible =)

---

All column values should be atomic.

You should not have a list of anything you want to query inside a single value.

The way to relate exercises to other exercises is with a second table. It will have two columns, each holding an exercise ID, where both columns are a foreign reference the exercises table.

---

The only way to do that is either via a stored procedure or in your language of choice. When you delete the record, you would then have to do a LIKE query to find any record with the value in your delimited field. It's really not the best way to do things, and would be slow as hell since you can't index that field the way you need to.

Your absolute best bet would be to create another table to define the relationships between exercises, using just two exercise id fields (exercise_id, related_exercise_id).

You should read up on Database Normalization.