Composite Primary Keys example in MySQL

Question

I have encountered MySQL itself recently and the topic of Composite Primary Keys in MySQL, especially how it is useful and what are its pros and cons from this site

I wanted to play with that, so I have created three tables in this fashion:

CREATE TABLE person(
    personId INT(11) NOT NULL,
    personName VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId)
)

CREATE TABLE language(
    languageId INT(11) NOT NULL,
    languageName VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId)
)

CREATE TABLE personLanguage(
    personId INT(11) NOT NULL,
    languageId INT(11) NOT NULL,
    description VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId, languageId),
    FOREIGN KEY (personId) REFERENCES person(personId) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (languageId) REFERENCES language(languageId) ON UPDATE CASCADE ON DELETE CASCADE
)

I can insert data into the person and language tables-straight forward, my questions are:

1. For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well

2. Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table

3. How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys

Lots of questions bugging my mind and I could not really find a whole working example to check the exact pros and cons of using composite primary keys. In case if somebody could elaborate this using my example, would be really helpful.

I know I have sort of asked some basic, some what makes no sense question, but please do bear me and throw some good light on this topic

Solution 1

For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well

Yes, you will need to insert all three of the columns to be completely valid. Otherwise the DB won't know what person or language you are trying to tie this record to.

Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table

You could do this via an insert trigger, but it might not make any sense. So, let's say that you just entered a new language - say French. You shouldn't need to enter any values at all into the personLanguage table because your existing users might not want to get information in French. The same situation would be for creating a new person. You might have many languages. Most people won't speak most of the languages, so again, you wouldn't want to enter a record into the personLanguage table automatically.

As for updating the records in person and language, the KEYS shouldn't change. This is why you would do something like this. Once Bob or Alice is assigned a personId, they are that Id. Once French is assigned a langaugeId, it should always be that languageId.

How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys

Well, this is the tricky question. If you are trying to get ALL the languages personId=1 speaks, the join is pretty easy.

select pl.personId, l.languageId, l.languageName 
  from personLanguage pl
    join language l on l.languageId = pl.languageId
    where pl.personId = 1

It gets more complicated if you are trying to figure out which language you should communicate with the person, since there is a chance that the person might not have any personLanguages defined. If you can accept null values, you can use an outer join, but you would want to define the query so that you only return a single language.

Solution 2

1. For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well

   You can only insert values into personLanguage if there are existing keys in the referenced tables. This means you must populate person and language prior to inserting values into personLanguage. However, if you have a NULLable field, you could do this, but it would violate the unique composite key.

2. Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table

   The constraint that you have specified (ON UPDATE CASCADE) means that when there is a change to a referenced value in either person or language it will automatically update those values in personLanguage. However, there cannot be a violation of of the PRIMARY KEY constraint on personLanguage.

3. How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys

   Since this is a basic example, there wouldn't really be a need for this. In an extended form, you could use explicit JOINs to fetch data between the tables.

Just a few more thoughts...

Composite keys are generally used for referencing in tuples (or sets). This means that when you have a composite key (col1, col2) on table1, this references a composite key (col1, col2) on table2.