How to insert multiple values in single column in MySQL

Question

I've created two simple tables in phpmyadmin. A list of "content creators" and a list of "content creator types". Each "content creator" can have multiple "content creator types" associated with them so this is a one-many relationship. In phpmyadmin I created the following relationship.

ALTER TABLE  `contentcreator` 
ADD FOREIGN KEY (  `ContCreat_TypeID` ) 
  REFERENCES `MyDatabase`.`contentcreator_types` (`ContCreat_TypeID`) 
  ON DELETE RESTRICT ON UPDATE RESTRICT ;

Now when I try to insert data (using the insert data interface in phpmyadmin) into the table "content creator" - I get a drop down to allow me to choose one of "content creator type IDs". However I want to be able to add multiple "content creator type IDs" to each content creator. How do I do this within phpmyadmin using the insert data interface?

Are you sure that this is not a many to many relationship? A creator can have many types, and a type can be on many creators — Stefan H
– Stefan H, Commented Jul 13, 2012 at 16:32
It's actually a Many-to-Many relationship. You'll need a third table that has FK references to both your content creators table's PK and your content creator types table's PK. This is often called a Junction or a Join table. — Brian Driscoll
– Brian Driscoll, Commented Jul 13, 2012 at 16:32
Thanks for pointing out my mistake. I will create a junction table as suggested. — Ansh
– Ansh, Commented Jul 13, 2012 at 16:43

Stefan H · Accepted Answer · 2012-07-13 16:41:21Z

1

It sounds like you have a many to many relationship here, not a one to many. You need to add an intersection table with foreign keys to both your types table, and your content creators table.

If you wanted to do this as a one to many relationship, the side that is many gets the foreign key, not the side that is one. Meaning you would have a foreign key to the content Creators table on the content creators type table. Then each content creator would get a new entry in the type table for each type that it had, however you would have duplication in that scenario.

Many TO Many:

ContentCreatorID CreatorName
---------------- ------------------
1                Creator1
2                Creator2
3                Creator3

CreatorTypes (intersection Table)


ContentCreatorID TypeID
---------------- ------------------
1                1
1                2
2                3
3                2


Types

TypeID            TypeName
---------------- ------------------
1                 Type1
2                 Type2
3                 Type3

edited Jul 13, 2012 at 16:41

answered Jul 13, 2012 at 16:35

Stefan H

6,6934 gold badges26 silver badges35 bronze badges

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

1 Comment

Ansh Over a year ago

Thanks for the tips! I will add the intersection table as suggested to connect the two.

Decker W Brower · Accepted Answer · 2012-07-13 16:40:59Z

0

You may want to consider creating a mapping table to associate creators with their types

    creator table
    id | creator

    type table
    id | type

    mapping table
    creator_id | type_id

answered Jul 13, 2012 at 16:40

Decker W Brower

9416 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to insert multiple values in single column in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related