Is it possible to have a MySQL column containing multiple values as foreign keys?

Question

I am learning MySQL and have MariaDB installed in Fedora 19.

I have a scenario where I require a column to contain multiple values in order to reduce possible redundancy of column allocation.

In the example below, is it possible to have each value in the tags column of the log table reference the tag_id column in the tags table?

users

user_id        |
1              |

activities

activitity_id  |
1

log

user_id        |    activity_id    | tags
1              |    1              | 1,3,5 # multiple foreign keys?

tags

tag_id         |
1              |
2              |
3              |
4              |
5              |

If it is not possible, could anyone provide the logic for the most feasible solution based on the data scenario above?

Similar Questions:

Are multiple foreign keys in a single field possible?

MySQL foreign key having multiple (conditional) possible values

it is possible to reference one column as multiple foreign keys

Patrick Evans · Accepted Answer · 2013-07-29 16:09:05Z

2

If you do not wish to make up a "middle man" table for linking the two tables you can have a comma separated value in the field, you would just need to use the find_in_set mysql function when doing queries

USING find_in_set

SELECT
   log.user_id, log.activity_id, log.tags,
   GROUP_CONCAT(tags.name) as taggedNames //This assumes there is a field called `name` in tags table
FROM
   log
LEFT JOIN tags
ON
   FIND_IN_SET(tags.tag_id,log.tags)
GROUP BY
   log.activity_id

GROUP_CONCAT will group together a field and separate them by a deliminator, default is ,

edited Jul 29, 2013 at 16:09

answered Jul 29, 2013 at 15:02

Patrick Evans

42.8k6 gold badges81 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1063287 Over a year ago

ERROR 1054 (42S22): Unknown column 'log.user_id' in 'field list'

Patrick Evans Over a year ago

@user1063287 forgot to add the log table to the FROM clause

user1063287 Over a year ago

Empty set (0.01 sec) - do i need to add further definition to the above query? The contents of record 1 in log.tags is '1,2' (VARCHAR(30)).

Patrick Evans Over a year ago

It might be that you need a left join instead, (edited answer to show), my server is down so i cant test properly

Patrick Evans Over a year ago

Yea if you want them ordered you will have to do an order by on the tag names. But do it like order by log.activity_id, tags.name or similar order on some part of the main row as otherwise if you do it simply by tags.name the log results may be out of order

|

Collectives™ on Stack Overflow

Is it possible to have a MySQL column containing multiple values as foreign keys?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related