
Primära och främmande nycklar i MySQL
I alla relationsdatabaser används nycklar för att sätta upp relationer mellan olika tabeller. Tabeller skall (så gott som) alltid ha en definierad primär nyckel eftersom detta garanterar att varje rad i tabellen är unik.
Vanligast är att man använder sig av en kolumn med heltal (ofta kallad id) vars värde sedan automatiskt ökas med ett för varje ny tupel (rad) i tabellen. Det är också praktiskt att definiera främmande nycklar som refererar till primära nycklar i andra tabeller för att hålla ihop informationen. I denna artikel visas hur detta görs rent konkret.
När man normaliserar data kommer det finnas flera tabeller som är relaterade till varann. För att exemplifiera använder vi återigen artister och album som exempel för att konkretisera.
Innan vi börjar tänkte jag tipsa om två andra, relaterade artiklar som denna bygger på:
- Känner du dig osäker på vad normalisering innebär kan du läsa en kort introduktion i artikeln Lite grundläggande databasteori.
- Vill du läsa en kort introduktion till MySQL som används nedan kan du läsa artikeln SQL i databashanteraren MySQL.
I vårt exempel nedan är alla artister relaterade till ett eller flera album. Varje album är däremot endast relaterad till en artist. Så, i tabellen album är alltså attributet (kolumnen) artist en främmande nyckel till tabellen artister för att använda termerna ovan.
Vi exemplifierar nedan, men först lite kort bakgrundsinformation…
Nycklar i MySQL.
De finns olika så kallade storage types i MySQL där de två populäraste heter MyISAM och InnoDB och för att kunna använda sig av främmande nycklar i MySQL måste tabellerna vara av typen InnoDB. Skillnander mellan de olika typerna gås inte igenom här, utan vi nöjer oss med att konstatera att tabellerna måste vara av typen InnoDB för att kunna använda många MySQL:s mer avancerade funktioner så som:
- Foreign keys (främmande nycklar)
- Transactions (transaktioner)
- Automated Crash Recovery
Sedan MySQL 4.1 är även InnoDB den typ som är förvald vid installation.
Våra tabeller och data.
För att få lite data att arbeta med skapar vi följande två tabeller och lägger in några rader i dessa. Lägg märke till hur vi definierar en främmande nyckel (som beskrevs ovan) för tabellen album. I vanlig fall använder man ofta ett heltal som primär nyckel även i tabeller som artister, men för att illustrera konceptet gör vi inte det utan använder artistnamnet som primär nyckel 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 kommandot DESCRIBE
kan vi se hur resultatet ser ut. I detta fallet resulterar koden ovan i följande 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 jobba med lägger vi också in lite data i tabellerna. Detta gör med några vanliga INSERT
. Notera att vi inte behöver definiera några värden för kolumnen id eftersom 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 resultatet kan vi använda 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ämmande nycklar måste bland annat följande krav uppfyllas (se hela listan på i MySQL-dokumentationen):
- Båda tabellerna måste vara InnoDB
- Båda fälten måste ha samma datatyp (varchar(40) i vårt fall)
Vad som händer när man tar bort eller uppdaterar en tupel som är referens för en främmande nyckel definierar man när man skapar tabellen. Här är de tre vanligaste valen, med ett exempel på vad som händer om vi definierat ON DELETE
till respektive alternativ i tabellerna ovan.
CASCADE
– Om vi tar bort raden med ”Live” från artister kommer alla rader som refererar till denna också att tas bort. Rad nummer 3 i tabellen album kommer alltså att tas bort med automatik.SET NULL
– Om vi tar bort raden med ”Live” från artister kommer alla rader som refererar till denna att få sitt värde för artist satt till NULL.RESTRICT
– Om vi försöker ta bort raden med ”Live” från artister kommer vi att få ett felmeddelande eftersom det fortfarande finns rader i tabellen album som refererar till ”Live”. Operationen genomförs alltså ej.
I vårt fall (med ON DELETE RESTRICT
och ON UPDATE CASCADE
) kommer vi alltså att få följande resultat om vi försöker 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ället uppdaterar värdet så slår ändringen 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 ändringen 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)
Sammanfattning.
Att använda primära nycklar är något som mer eller mindre borde vara en självklarhet för varje tabell du skapar när du designar en databas. Att sedan definiera främmande nycklar är också väldigt praktiskt för underhåll och struktur.
Tänk bara på att ON DELETE CASCADE
är ett utmärkt sätt att hålla dina tabeller fria från gammal data som inte används längre, men även ett perfekt sätt att på några hundradelar ta bort en väldigt massa information om det blir fel…
Detta var en väldigt kort crash course om olika typer av nycklar i MySQL. Förutom nycklar är något som heter index väldigt viktiga och används framförallt på kolumner som ofta ingår i WHERE
- och ORDER BY
-satser, men det är ett ämne för en annan liten artikel.
Relaterade artiklar
- Artikel
- Artikel
- Artikel