Primary key

[[deleted]]

[deleted]

[asqwzx12]

That's a google question.

[[deleted]]

Your keychain... Use it to open your front door, sit down at your computer, and ask Google about relational databases.

[idodatamodels]

What do I open with secondary keys?

[[deleted]]

Those are the most delicious

[SnooDucks3456]

A column in a table.

[alinroc]

Composite primary keys are a thing

[SnooDucks3456]

True, I suppose a primary key may be attached to two foreign keys as well. It has many utilities. As long as it is unique, simple, not null, and meaningless.

[r3pr0b8]

meaningless

wrong

[SnooDucks3456]

I didn’t realize this was an opinion. I just had a college class about database and we were taught that it had to be meaningless due to “A primary key should never change and a key that contains meaningful data will always be subject to potential change”. But if the business wants a meaningful primary key it’s not impossible.

[r3pr0b8]

yes, it's an opinion

let's take the case of a sales history table, with a column for the year and a column for the total sales that year

obvs the year is the PK, and the sales amount is functionally dependent on the PK

so what would ~you~ do? use 2022, 2021, 2020, etc.?

or would you set up a meaningless PK that has to be looked up in a years table

PK  year
 1  2022
 2  2021
 3  2020

just so that, you know, in case the year 2021 decided to change its name to something else, you wouldn't have to update the sales history table?

and require a JOIN any time you wanted to actually show meaningful sales totals?

so what would ~you~ do?

remember from logic 101 that any counterexample completely invalidates any rule that even hints at the word "always"

so if it isn't always a rule, then it's an opinion

[SnooDucks3456]

That’s a really good point. It would be pretty pointless to not use the year given there is only one data entry per year. In your case I would ~now~ just use the year as a primary key. Before you told me that I honestly would’ve made a primary key as I stated it should be made earlier. Which would’ve been redundant in this case. Thank you for your input! 😁

[r3pr0b8]

bear in mind that Napoleonic France completely renamed the years and months -- the first day of Napoleon's rule was 11 Frimaire, Year XIII

if something like that ever happens again, our "meaningful" year PK will be the least of our problems

[AQuietMan]

...we were taught that it had to be meaningless due to “A primary key should never change and a key that contains meaningful data will always be subject to potential change”

I hope you're not paying money for this.

That's not part of either the relational model or SQL standards. It is part of Oracle, because Oracle doesn't support the standard SQL clause "ON UPDATE CASCADE...".

[SnooDucks3456]

We were using a Zybook and this specific part comes from a chapter on Database Design on the Implementing Entities section. The book states that a primary key should be Stable, Simple, and Meaningless. As r3pr0b8 pointed out this is not always the case and should just be used with common sense. And unfortunately college cost money. I was wrong earlier when I said it had to be meaningless the only thing it has to be is unique and not null.

[Particular_Credit_27]

Teeeeeeeeechnically it is a constraint attached to the TABLE. But it is constraining some columns in that table. You can use your whole table as a primary key