[please] primary key definition

Hello everyone,
Please help me to be clear about the definition of primary key. When I work with Chinook.db, related to schema diagram, DQ provided the definition as follows:

Each table has one or more columns with shading, which indicates those columns are  primary keys. We'll learn more about primary keys in a later lesson, but for now all you need to know is that each row's primary key must be unique.

Screen Link:
https://app.dataquest.io/m/189/intermediate-joins-in-sql/1/working-with-larger-databases


If we look at the playlist_track table, we see that there are 2 columns with shading, so I understood that these are primary key (i.e. the values must be unique). However, when I print first 5 rows of playlist_track I received the output like this:

image

The values in playlist_id column are not unique. So why is the name of this column highlighted in schema diagram?

Thank you in advance.

1 Like

Wow, nice catch! I agree with you…it doesn’t make sense for playlist_id to be shaded in the schema under playlist_track since it’s obviously not unique.

Please submit a ticket so that the DataQuest team can take a look at fixing this for future learners. To submit a ticket, click on ? in the top-right of your screen and select Send in a ticket.

1 Like

Hi Mike,
Sorry but I can’t find it

The above diagram shows that the playlist and track table have many to many relationships and playlist_track is just a reference table. To highlight this in playlist_track that both columns are the primary keys of other tables were shaded.

2 Likes

Hello @ sanikamal223,
But if you look at the invoice_line table, for instance you can realize that invoice_id is the PK of invoice table too as I know we can call it FK (foreign key), but it is not highlighted.

2 Likes

No worries, maybe a screenshot would be more helpful:

2 Likes

Thank you I sent it.

2 Likes

As @TrnTrMi points out, if what you propose were true, we would see other tables with more shading in them as well. I could easily be wrong here but I still agree with @TrnTrMi that it’s likely a “typo” in the diagram and that the DQ team should take a look at it.

1 Like

It can shaded with another color instead of removing. It is just my personal opinion​:thinking::thinking:

1 Like

@mathmike314 @sanikamal223

Are you sure this is an actual issue?

Both the columns being highlighted means that the primary key consists of those 2 columns. That means that the combination of values in both the columns should be unique and not just the values in one column.

So, while individually there might be duplicate values in either column, they both together can’t have duplicate values. So, something like this shouldn’t be possible in that table -

playlist_id track_id
1 3402
1 3402

Which would make sense to have a reference table for playlist and track ids, I think. A playlist can have multiple tracks, but not multiple duplicate tracks. And the same track can be in multiple playlists.

Also, MySQL Primary Key

A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.

This is not so much of an issue with there not being unique values in a single column, as it is an issue with the Content not clarifying what happens when multiple columns are used to create a primary key.

4 Likes

Thank you @the_doctor . You are right, I count the number of distinct rows when group by both columns and the number of rows the same as the number of rows of playlist_track.

1 Like

playlist_id is truely unique for table playlist, also playlist_track is association of tables in which each table are really unique primary key for each of them. It’s why you have several playlist_id=1 with in playlist_track but unique value of the combination playlist_id+track_id. The principle of unicity of primary key is still respected. To masterize that you need to learn globally the methods “MERISE” or UML for software and Database conception.
I hope this helpful! I’m a french speaker, so excuse my bad english.

1 Like

Oui, bonjour,
T’as raison.
J’habite en France aussi. C’est très utile pour moi. Merci beaucoup.

1 Like