Tillbaka till alla guider
Databasdesign··6 min läsning

En-till-många eller många-till-många: relationsmönster i SQL

Lär dig en-till-många kontra många-till-många-relationer i SQL. Se hur du implementerar varje med främmande nycklar, kopplingstabeller och verkliga exempel.

Foreign key från kund till order och kopplingstabell mellan student och kurs med en sammansatt primärnyckel
I den här guiden
  1. Förståelse för SQL-relationsmönster
  2. En-till-många-relationer
  3. Många-till-många-relationer
  4. En-till-en-relationer
  5. Hur relationer visas i ERD
  6. Att välja rätt relationsmönster
  7. Referenser och vidare läsning

SQL-exempel använder PostgreSQL såvida inte en annan dialekt anges. Korta kodsnuttar förutsätter att deras refererade tabeller redan existerar. Validera DDL i din måldatabas innan du tillämpar den.

Förståelse för SQL-relationsmönster

Relationella databaser får sin kraft från kopplingarna mellan tabeller. Oavsett om du designar en liten applikation eller ett datalager för företag, avgör valet av rätt relationsmönster hur effektivt du kan lagra, fråga och underhålla din data. De tre grundläggande mönstren—en-till-en, en-till-många, och många-till-många—täcker många vanliga associationer; rekursiva relationer och relationer av högre grad kräver ytterligare modellering.

I den här guiden bryter vi ner varje mönster med PostgreSQL-exempel, utforskar rollen för kopplingstabeller och visar hur dessa relationer visas i ett Entitet-relationsdiagram (ERD). Om du vill visualisera ditt eget schema just nu, öppna Erd Go-redigerare och klistra in stödda CREATE TABLE definitioner för att generera ett diagram.

Främmande nyckel-SQL mappad till en ERD-relation mellan kunder och beställningar
En främmande nyckel är mer än syntax: i ett ERD blir det relationslinjen som förklarar hur tabeller beror på varandra.

En-till-många-relationer

A en-till-många (1:N)-relation är det vanligaste mönstret i databasdesign. Det innebär att en enskild rad i förälder tabell kan refereras av många rader i barn tabell, och en icke-null främmande nyckel för barn pekar på en förälder. En nullbar främmande nyckel tillåter ett barn utan en förälder.

Klassiskt exempel: Avdelningar och anställda

Ett företag har många avdelningar, och varje avdelning innehåller flera anställda. Däremot tillhör varje anställd exakt en avdelning vid varje given tidpunkt.

CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    location    VARCHAR(255),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id              SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    department_id   INT NOT NULL,
    hire_date       DATE DEFAULT CURRENT_DATE,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Viktiga implementeringsdetaljer

  • Den främmande nyckeln (department_id) finns alltid på många sida—den employees tabell i detta fall.
  • ON DELETE RESTRICT förhindrar oavsiktlig borttagning av en avdelning som fortfarande har anställda tilldelade.
  • ON UPDATE CASCADE säkerställer att om en avdelnings primärnyckel ändras, uppdateras alla medarbetarrader automatiskt.
  • Lägger till ett index på department_id kan hjälpa kopplingar och avdelningsfilter; bekräfta nyttan med representativ data och en exekveringsplan.

Fråga data med en-till-många-relationer

-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';

-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;

Andra vardagliga exempel på en-till-många inkluderar författare → blogginlägg, kunder → beställningar, och kategorier → produkter.

Många-till-många-relationer

A många-till-många (M:N)-relation existerar när rader på båda sidor kan associeras med flera rader på den andra sidan. Relationella databaser kan inte representera detta direkt med en enskild främmande nyckel, så vi introducerar en kopplingstabell (även kallad en brygga, join, eller associativ tabell).

Klassiskt exempel: Studenter och kurser

En student kan skriva in sig på många kurser, och varje kurs kan ha många studenter. Kopplingstabellen enrollments sitter mellan dem och håller de främmande nycklarna till båda sidor.

CREATE TABLE students (
    id          SERIAL PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    enrolled_on DATE DEFAULT CURRENT_DATE
);

CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    credits     SMALLINT NOT NULL DEFAULT 3,
    department  VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id  INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    course_id   INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade       CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

Varför en kopplingstabell?

  • Den sammansatt primärnyckel (student_id, course_id) tillåter en rad per student-kurs-par. Om upprepad inskrivning över terminer krävs, inkludera en termin i affärsnyckeln eller modellera varje inskrivning separat.
  • Du kan bifoga extra attribut till själva relationen, såsom grade eller enrolled_at, som inte tillhör någon entitet ensam.
  • ON DELETE CASCADE på båda främmande nycklar innebär att borttagning av en student eller kurs automatiskt rensar upp registreringsraderna.

Fråga data med många-till-många-relationer

-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;

-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';

Verkliga exempel på många-till-många inkluderar användare ↔ roller, produkter ↔ taggar, aktörer ↔ filmer, och beställningar ↔ produkter (via en order_items kopplingstabell som även lagrar kvantitet och pris).

En-till-en-relationer

A en-till-en (1:1)-relation innebär att varje rad i Tabell A mappar till högst en rad i Tabell B, och vice versa. Detta mönster är mindre vanligt men användbart för att dela upp sällan åtkomliga eller känsliga kolumner i en separat tabell.

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(60) UNIQUE NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id     INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
    bio         TEXT,
    avatar_url  VARCHAR(500),
    birthdate   DATE,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Observera att user_profiles.user_id är både primärnyckel och främmande nyckel. Denna begränsning framtvingar en-till-en-mappningen på databasnivå—varje användare kan ha högst en profilrad.

Hur relationer visas i ERD

Ett entitets-relationsdiagram använder linjer och symboler mellan tabeller för att förmedla kardinalitet vid en första anblick. De två mest populära notationerna är Kråkfot och Chen notation.

RelationKråkfotsymbolHur man läser
En-till-en||—||Exakt en på varje sida
En-till-många||—<En till vänster, många till höger (kråkfotsände)
Många-till-många>—<Många på båda sidor; implementeras via kopplingstabell

Dessa textsymboler är förkortningar för maximal kardinalitet. Valbarhet kräver en extra noll- eller ett-markör; en främmande nyckel i sig kräver inte att en förälder har några barn.

När du modellerar en många-till-många-relation i ett ERD-verktyg ser du vanligtvis tre lådor – två entitetstabeller och kopplingstabellen mellan dem, var och en ansluten med en en-till-många-linje.

Klistra in valfritt SQL-exempel från denna artikel i Erd Go-redigerare för att se diagrammet genereras automatiskt, komplett med pilar för främmande nycklar och kardinalitetsindikatorer.

Att välja rätt relationsmönster

Att välja fel kardinalitet leder till dataduplicering, komplexa frågor och integritetsfel. Använd detta beslutsramverk:

  • Kan en rad på sida A relatera till många rader på sida B, men inte tvärtom? → En-till-Många. Placera den främmande nyckeln på sida B.
  • Kan rader på båda sidor relatera till många rader på den andra? → Många-till-Många. Skapa en kopplingstabell med sammansatt nyckel.
  • Är mappningen strikt 1:1 och behöver du tabellseparation? → En-till-En. Använd samma primärnyckel som främmande nyckel.

När du är osäker, skissa ditt schema först. Ett snabbt ERD avslöjar relationsproblem mycket tidigare än att stöta på begränsningsfel i produktion. Prova att bygga ditt diagram i Erd Go-redigerare—det tar sekunder och kan spara timmar av felsökning.

Referenser och vidare läsning

Fler inlägg