Republic / Artiklar /

Artikeln uppdaterad 2021-04-14.
Skriven av Christofer Sandin.

Primära och främ­mande nyck­lar i MySQL

I alla rela­tions­data­bas­er används nyck­lar för att sät­ta upp rela­tion­er mel­lan oli­ka tabeller. Tabeller skall (så gott som) alltid ha en definier­ad primär nyck­el efter­som det­ta garan­ter­ar att var­je rad i tabellen är unik.

Van­li­gast är att man använ­der sig av en kol­umn med heltal (ofta kallad id) vars värde sedan automa­tiskt ökas med ett för var­je ny tupel (rad) i tabellen. Det är ock­så prak­tiskt att definiera främ­mande nyck­lar som ref­er­erar till primära nyck­lar i andra tabeller för att hål­la ihop infor­ma­tio­nen. I den­na artikel visas hur det­ta görs rent konkret.

När man nor­malis­er­ar data kom­mer det finnas flera tabeller som är relat­er­ade till varann. För att exem­pli­fiera använ­der vi återi­gen artis­ter och album som exem­pel för att konkretisera.

Innan vi bör­jar tänk­te jag tip­sa om två andra, relat­er­ade artik­lar som den­na byg­ger på:

• Kän­ner du dig osäk­er på vad nor­malis­er­ing innebär kan du läsa en kort intro­duk­tion i artikeln Lite grundläg­gande data­bas­te­ori.
• Vill du läsa en kort intro­duk­tion till MySQL som används nedan kan du läsa artikeln SQL i data­bashanter­aren MySQL.

I vårt exem­pel nedan är alla artis­ter relat­er­ade till ett eller flera album. Var­je album är däre­mot endast relat­er­ad till en artist. Så, i tabellen album är allt­så attrib­ut­et (kolum­nen) artist en främ­mande nyck­el till tabellen artis­ter för att använ­da ter­mer­na ovan.

Vi exem­pli­fier­ar nedan, men först lite kort bakgrundsinformation…

Nyck­lar i MySQL.

De finns oli­ka så kallade stor­age types i MySQL där de två pop­uläraste het­er MyISAM och Inn­oDB och för att kun­na använ­da sig av främ­mande nyck­lar i MySQL måste tabeller­na vara av typen Inn­oDB. Skill­nan­der mel­lan de oli­ka type­r­na gås inte igenom här, utan vi nöjer oss med att kon­stat­era att tabeller­na måste vara av typen Inn­oDB för att kun­na använ­da mån­ga MySQL:s mer avancer­ade funk­tion­er så som:

• For­eign keys (främ­mande nycklar)
• Trans­ac­tions (transak­tion­er)
• Auto­mat­ed Crash Recovery

Sedan MySQL 4.1 är även Inn­oDB den typ som är för­vald vid installation.

Våra tabeller och data.

För att få lite data att arbe­ta med ska­par vi föl­jande två tabeller och läg­ger in några rad­er i dessa. Lägg märke till hur vi definier­ar en främ­mande nyck­el (som beskrevs ovan) för tabellen album. I van­lig fall använ­der man ofta ett heltal som primär nyck­el även i tabeller som artis­ter, men för att illus­tr­era kon­ceptet gör vi inte det utan använ­der artist­nam­net som primär nyck­el rakt av.

mysql> CREATE TABLE artister
-> (
->   artist VARCHAR(40),
->   www VARCHAR(256),
->
->   PRIMARY KEY (artist)
-> ) TYPE=INNODB;
mysql> CREATE TABLE album
-> (
->   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
->   artist VARCHAR(40),
->   titel VARCHAR(40),
->   ar DATE,
->
->   PRIMARY KEY (id),
->   FOREIGN KEY (artist) REFERENCES artister(artist)
->     ON DELETE RESTRICT
->     ON UPDATE CASCADE
-> ) TYPE=INNODB;

Med hjälp av kom­man­dot DESCRIBE kan vi se hur resul­tatet ser ut. I det­ta fal­l­et resul­ter­ar koden ovan i föl­jande två tabeller:

mysql> DESCRIBE artister;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| artist | varchar(40)  | NO   | PRI |         |       |
| www    | varchar(256) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0,00 sec)
mysql> DESCRIBE album;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| artist | varchar(40)      | YES  | MUL | NULL    |                |
| titel  | varchar(40)      | YES  |     | NULL    |                |
| ar     | date             | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0,00 sec)

För att ha något att job­ba med läg­ger vi ock­så in lite data i tabeller­na. Det­ta gör med några van­li­ga INSERT. Notera att vi inte behöver definiera några vär­den för kolum­nen id efter­som den är satt som auto_increment.

mysql> INSERT INTO artister (artist, www) VALUES ("R.E.M.", "http://www.remhq.com");
mysql> INSERT INTO artister (artist, www) VALUES ("Live", "http://www.friendsoflive.com/");
mysql> INSERT INTO album (titel, artist, ar) VALUES ("Out of Time", "R.E.M.", "1991-03-12");
mysql> INSERT INTO album (titel, artist, ar) VALUES ("Reveal", "R.E.M.", "2001-05-07");
mysql> INSERT INTO album (titel, artist, ar) VALUES ("Mental Jewelry", "Live", "1992-03-09");

Och för att se resul­tatet kan vi använ­da två enkla SELECT:

mysql> SELECT * FROM artister;
+--------+-------------------------------+
| artist | www                           |
+--------+-------------------------------+
| Live   | http://www.friendsoflive.com/ |
| R.E.M. | http://www.remhq.com          |
+--------+-------------------------------+
2 rows in set (0,00 sec)
mysql> SELECT * FROM album;
+----+--------+----------------+------------+
| id | artist | titel          | ar         |
+----+--------+----------------+------------+
|  1 | R.E.M. | Out of Time    | 1991-03-12 |
|  2 | R.E.M. | Reveal         | 2001-05-07 |
|  3 | Live   | Mental Jewelry | 1992-03-09 |
+----+--------+----------------+------------+
3 rows in set (0,00 sec)

In action.

För att det skall gå att definiera främ­mande nyck­lar måste bland annat föl­jande krav upp­fyl­las (se hela lis­tan på i MySQL-dokumentationen):

• Båda tabeller­na måste vara InnoDB
• Båda fäl­ten måste ha sam­ma datatyp (varchar(40) i vårt fall)

Vad som hän­der när man tar bort eller upp­dat­er­ar en tupel som är ref­er­ens för en främ­mande nyck­el definier­ar man när man ska­par tabellen. Här är de tre van­li­gaste valen, med ett exem­pel på vad som hän­der om vi definier­at ON DELETE till respek­tive alter­na­tiv i tabeller­na ovan.

• CASCADE – Om vi tar bort raden med ​”Live” från artis­ter kom­mer alla rad­er som ref­er­erar till den­na ock­så att tas bort. Rad num­mer 3 i tabellen album kom­mer allt­så att tas bort med automatik.
• SET NULL – Om vi tar bort raden med ​”Live” från artis­ter kom­mer alla rad­er som ref­er­erar till den­na att få sitt värde för artist satt till NULL.
• RESTRICT – Om vi försök­er ta bort raden med ​”Live” från artis­ter kom­mer vi att få ett felmed­de­lande efter­som det fort­farande finns rad­er i tabellen album som ref­er­erar till ​”Live”. Oper­a­tio­nen genom­förs allt­så ej.

I vårt fall (med ON DELETE RESTRICT och ON UPDATE CASCADE) kom­mer vi allt­så att få föl­jande resul­tat om vi försök­er oss på att ta bort raden med ​”Live” i tabellen artister:

 mysql> DELETE FROM artister WHERE artist="Live";
 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`artiklar_mysql/album`,
 CONSTRAINT `album_ibfk_1` FOREIGN KEY (`artist`) REFERENCES `artister` (`artist`) ON UPDATE CASCADE)

… men om vi istäl­let upp­dat­er­ar värdet så slår ändrin­gen igenom:

mysql> UPDATE artister SET artist="REM" WHERE artist="R.E.M.";
mysql> SELECT * FROM artister;
+--------+-------------------------------+
| artist | www                           |
+--------+-------------------------------+
| Live   | http://www.friendsoflive.com/ |
| REM    | http://www.remhq.com          |
+--------+-------------------------------+
2 rows in set (0,00 sec)

och ser att ändrin­gen slått igenom även i den tabellen som vi inte påverkade direkt:

mysql> SELECT * FROM album;
+----+--------+----------------+------------+
| id | artist | titel          | ar         |
+----+--------+----------------+------------+
|  1 | REM    | Out of Time    | 1991-03-12 |
|  2 | REM    | Reveal         | 2001-05-07 |
|  3 | Live   | Mental Jewelry | 1992-03-09 |
+----+--------+----------------+------------+
3 rows in set (0,00 sec)

Sam­man­fat­tning.

Att använ­da primära nyck­lar är något som mer eller min­dre bor­de vara en självk­larhet för var­je tabell du ska­par när du des­ig­nar en data­bas. Att sedan definiera främ­mande nyck­lar är ock­så väldigt prak­tiskt för under­håll och struktur.

Tänk bara på att ON DELETE CASCADE är ett utmärkt sätt att hål­la dina tabeller fria från gam­mal data som inte används län­gre, men även ett per­fekt sätt att på några hun­drade­lar ta bort en väldigt mas­sa infor­ma­tion om det blir fel…

Det­ta var en väldigt kort crash course om oli­ka typer av nyck­lar i MySQL. Föru­tom nyck­lar är något som het­er index väldigt vik­ti­ga och används fram­förallt på kolum­n­er som ofta ingår i WHERE- och ORDER BY-satser, men det är ett ämne för en annan liten artikel.