Primær nøgle SQL-vejledning - Sådan defineres en primær nøgle i en database

Hver stor historie starter med en identitetskrise. Luke, den store Jedi-mester, begynder usikker - "Hvem er jeg?" - og hvordan kunne jeg være nogen vigtig? Det tager Yoda, den med styrken, at lære ham, hvordan man udnytter sine kræfter.

I dag, lad mig være din Yoda.

Vi starter med, hvordan vi vælger en primær nøgle, bekæmper en identitetskrise og slutter derefter med kodeeksempler til oprettelse af en primær nøgle i en database.

Sådan vælges en primær nøgle

Du tror måske, at Luke er den eneste med en identitetskrise, men det er ikke sandt. Når du opretter en database, er alt i en identitetskrise. Og netop derfor har vi brug for primære nøgler: de løser krisen. De fortæller os, hvordan vi kan finde alle.

Forestil dig, at du er regeringen, og du vil identificere hver enkelt af dine borgere digitalt. Så du opretter denne database med alt om dem:

First Name Last Name Passport Number

Du vælger pasnummeret som den primære nøgle - identiteten for alle. Du regner med, at det er alt hvad du behøver, da pas har adressen og alt andet. Du ved, at pasnumre er unikke, så du føler dig godt og implementerer dette system.

Så få år senere finder du ud af en grim sandhed: hele landet står over for en identitetskrise.

Hver gang en persons pas udløber, får de et nyt. Deres identitet ændres. Andre systemer bruger fortsat de gamle pasnumre, så de peger nu på spøgelsesfolk.

Unikhed er ikke nok. Værdien må ikke ændres i hele rækkenes levetid.

Og så finder du ud af, at der er nogle mennesker, der ikke engang har pas. Du kan ikke indtaste dem i dit system, da primære nøgler ikke kan være det NULL. Hvordan kan du identificere nogen med en NULLnøgle?

Hver række skal have en identifikator. NULL'er er ikke tilladt.

Den næste iteration betyder at finde en identifikator, der ikke ændrer sig over tid, og en som alle har. I Indien viser det sig at være Adhaar-kortet. I USA, Social Security Number.

Hvis du opretter en database, skal du gøre dem til dine primære nøgler.

Nogle gange har du ikke sådan en nøgle. Overvej et land, der endnu ikke har et socialsikringsnummer, og de vil oprette en digital registrering af alle borgere. De kunne oprette et nyt SSN, eller de kunne bare udnytte styrken i databaser og bruge en surrogatnøgle.

En surrogatnøgle har ingen ækvivalent i den virkelige verden. Det er bare et nummer inde i en database. Så du har denne tabel i det nye land:

userID First Name Last Name Passport Number

Pasnumre er unikke. Når du vil hente identifikatoren for en bruger, kan du få den via pasnummeret.

Bruger-ID'en ændres aldrig. Pasnummeret kan ændre sig - men det er altid unikt, så du får altid den rigtige bruger. UserID er et stedfortræder for et ikke-eksisterende socialsikringsnummer i dette land.

Sjov kendsgerning: Passnummeret her er også en kandidatnøgle. Det kunne have været den primære nøgle, hvis den aldrig ændrede sig. Dette er en forretningslogisk skelnen.

Den vigtigste afhentning er dette: Når du vælger en primær nøgle, så tænk på en identitetskrise . Er det muligt, at nogen kan ændre deres identifikator i fremtiden? Kan vi komme ind i en tilstand med flere personer, der har samme identifikator?

Jeg bruger mennesker som et eksempel, fordi det gør identiteten tydeligere - vi ved, at enhver person skal have en identitet. Overfør denne tænkning til dine databaser. Alt har en identitet, hvilket er præcis, hvorfor du har brug for primære nøgler.

Bemærk: Nogle gange er det muligt og ønskeligt at bruge flere kolonner sammen som den primære nøgle. Dette er en sammensat nøgle.

Lad os nu prøve at definere primære nøgler med reelle kodeeksempler. Der er to ting at gøre her: først identificerer du den primære nøgle. Derefter lærer du syntaksen til at definere den i en database.

Et rigtigt verdenseksempel

Lad os sige, at du kører en forsendelsesstart, ligesom Flexport. Du har pakker, der skal komme fra et sted til et andet, og skibe, der transporterer dem. Derudover har du kunder, der bestiller disse pakker.

Du regner med, at du har brug for et bord til kunderne, et til pakkerne og et til transport, der viser hvilken pakke der er lige nu.

Tænk igennem, hvilke kolonner du har brug for, og hvad der skal være den primære nøgle. Hvis du var ingeniør hos Flexport, er dette et faktisk spørgsmål, du bliver nødt til at finde ud af. Intet er givet, alt bliver opdaget i den virkelige verden.

I betragtning af disse oplysninger vil jeg designe disse tabeller således:

Customers: first_name, last_name, email, address (for deliveries to their location) Packages: weight, content Transportation: , Port, time

Vi mangler de primære nøgler. Tænk over dem, før du læser videre.

For the package, I'll choose a surrogate PackageID. I could have tried to list all the attributes of the package: weight, volume, density, age. They would uniquely identify the package, but this is very hard to do in practice. People don't care about this, they just care about the package getting from one place to another.

So, it makes sense to create a random number and use that as the ID. This is exactly why you see FedEx, UPS, and every delivery service use barcodes and IDs. These are surrogate keys generated to track packages.

For kunden vælger jeg et surrogat CustomerID. Her havde jeg igen en mulighed for at vælge f.eks. Mine kunders socialsikringsnummer. Men kunder vil ikke dele dette med mig, så jeg kan sende dem noget. Således genererer vi en nøgle internt, fortæller ikke vores kunder om denne nøgle og fortsætter med at kalde dem CustomerNo. 345681.

Sjov historie: Jeg kender et par virksomheder, hvor de eksponerede denne CustomerNo, og kunderne insisterede på, at de fik nr. 1. Det var ret morsomt - ingeniørerne måtte faktisk ændre deres front-end-kode til: if (cust == 345681) print(1);

Til transport vælger jeg en sammensat PackageID + Port + tid. Dette er lidt mere interessant. Jeg kunne også have oprettet et surrogat , og det ville fungere lige så godt.

But, here lies the magic of indexing. The Primary Keys get an index automatically, which means searching is a lot more efficient over Primary Keys.

When you're searching through this database, most queries will be of the form "where is this package?". In other words, given this PackageID, tell me the Port and Time it is at right now. I would need an extra index over PackageID if I don't have it as part of my Primary Key.

Does this sound good? Final step, let's define these 3 tables in SQL. The syntax varies slightly with the database you're using.

Defining Primary Keys in MySQL

CREATE TABLE customers ( customerID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, last_name VARCHAR(30) NOT NULL, first_name VARCHAR(25) NOT NULL, email VARCHAR(50) NOT NULL, address VARCHAR(300) );
CREATE TABLE packages ( packageID INT(15) NOT NULL AUTO_INCREMENT, weight DECIMAL (10, 2) NOT NULL, content VARCHAR(50), CONSTRAINT packages_pk PRIMARY KEY (packageID) # An alternative way to above, # when you want to name the constraint as well. );
CREATE TABLE transportation ( package INT(15) NOT NULL, port INT(15) NOT NULL, time DATE NOT NULL, PRIMARY KEY (package, port, time), FOREIGN KEY package REFERENCES packages(packageID) ON DELETE RESTRICT # It's good practice to define what should happen on deletion. In this case, I don't want things to get deleted. );

Defining Primary Keys in PostgreSQL

CREATE TABLE customers ( customerID SERIAL NOT NULL PRIMARY KEY, # In PostgreSQL SERIAL is same as AUTO_INCREMENT - it adds 1 to every new row. last_name VARCHAR(30) NOT NULL, first_name VARCHAR(25) NOT NULL, address TEXT, email VARCHAR(50) NOT NULL );
CREATE TABLE packages ( packageID SERIAL NOT NULL, weight NUMERIC NOT NULL, content TEXT, CONSTRAINT packages_pk PRIMARY KEY (packageID) # In PostgreSQL, this alternative way works too. );
CREATE TABLE transportation ( package INTEGER NOT NULL, port INT(15) NOT NULL, time DATE NOT NULL, PRIMARY KEY (package, port, time), FOREIGN KEY package REFERENCES packages(packageID) ON DELETE RESTRICT # It's good practice to define what should happen on deletion. In this case, I don't want things to get deleted. );

It's not very different, is it? Once you get the basics down, you can apply it to almost any database with just a quick look at the documentation. The key is knowing what to look for!

Good luck, young padawan.

Nød dette? Du kan måske også lide ting, jeg lærte af en senior softwareingeniør