0

I have a table called 'movie2person' with 3 columns: movieID, personID and role. I use this table to connect betwen the 'movies' table and the 'persons' table... many-to-many relationship..

I have selected movieID and personID both as primary keys... The problem is that sometimes I need to enter the same personID for the same movieID several times and I can't do it because only one combination of the same movieID and personID is permited...

How can I do it??

Thanks..

Improve this question

5 Answers 5

Reset to default
2

Either include role in the primary key, or add a new artificial key to the table and use this new column as the primary key, which you wouldn't use outside of this table.

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

5 Comments

It doesn't affect performance to have 3 columns with lot of info (role) as primary keys??
If role is a large column, use an artificial key instead. It need only be an auto-incremented integer. Then you can define a constraint on the remaining columns if still need uniqueness.
role is VARCHAR 80, but I need movieID and personID to be primary so I can refer to them from another tables..
You can always refer to things even if they aren't primary, though without an index there would be a performance hit. I don't know how MySQL handles strings as primary keys, so I can't tell you which way is better. Can you put roles in another table and store references to that instead of strings?
I can put roles in another table but I will be using to many tables for a too simple thing, so I will go with the 3 primary keys.. Thanks!!
1

Based on some comments you've made, I think you need more normalization. Create a role table and adjust your lookup table accordingly

movie
+----+------------+
| id | title      |
+----+------------+
|  1 | Braveheart |
+----+------------+

person
+----+------------+
| id | name       |
+----+------------+
|  4 | Mel Gibson |
+----+------------+

role
+----+------------+
| id | title      |
+----+------------+
|  1 | Director   |
|  2 | Actor      |
+----+------------+

movie2person
+---------+----------+--------+
| movieID | personID | roleID |
+---------+----------+--------+
|       1 |        4 |      1 |
|       1 |        4 |      2 |
+---------+----------+--------+

With this setup you'd have a three-column composite primary key on movie2person.

Improve this answer

1 Comment

Role is not just the title of the role, but the person the actor play in the movie.. for example: Rusell Crowe role in Gladiator is Maximus so theres no need for a role table because the field will always be unique
0

Primary keys are meant to mean "This is the unique identifier of this row".

If you're inserting many rows with the same values, then that's not your primary key.

If you plan to insert exact duplicates for rows, then you don't have a primary key at all and you should drop it for good.

If, however, you plan to insert different roles to each (movieID, personID) pair, then you could just add the role to the primary key and you're good to go.

Improve this answer

2 Comments

It doesn't affect performance to have 3 columns with lot of info (role) as primary keys??
Just what are you storing in role? What is its datatype?
0

Male all three columns as primary key.

Improve this answer

Comments

0

I am suggesting some changes to your design:

  1. change the name of your table from Movie2Person to MoviePerson_xref. It is usually standard to name in sucha format and exlude numbers from your table naming conventions.

  2. This table should have its own primary key called movieperson_xrefID.

  3. You can then save all combinations of movie ID's and person ID's.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.