Powrót do wszystkich przewodników
SQL··7 min czytania

Ograniczenia SQL: NOT NULL, UNIQUE, CHECK, DEFAULT i inne

Opanuj ograniczenia SQL, aby chronić integralność danych. Poznaj NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY i FOREIGN KEY z jasnymi przykładami.

Tabela produktów z NOT NULL, UNIQUE, CHECK dla nieujemnej ceny i DEFAULT zero
W tym przewodniku
  1. Czym są więzy SQL?
  2. NOT NULL
  3. UNIQUE
  4. CHECK
  5. DEFAULT
  6. Ograniczenia PRIMARY KEY i FOREIGN KEY
  7. Ograniczenia na poziomie kolumny vs na poziomie tabeli
  8. Nazwane ograniczenia i dlaczego są ważne
  9. Łączenie ograniczeń i typowych wzorców
  10. Typowe pułapki
  11. Referencje i dalsza lektura

Przykłady SQL używają PostgreSQL, chyba że wymieniono inny dialekt. Krótkie fragmenty zakładają, że tabele, do których się odwołują, już istnieją. Zweryfikuj DDL w docelowej bazie danych przed jego zastosowaniem.

Czym są więzy SQL?

Ograniczenia to reguły wymuszane przez silnik bazy danych w celu zagwarantowania dokładności i niezawodności danych w tabeli. Działają jak barierki ochronne — zapobiegając nieprawidłowym wstawieniom, aktualizacjom i usunięciom na granicy bazy danych. Chronią zadeklarowane reguły; walidacja aplikacji i projektowanie transakcji nadal mają znaczenie.

SQL definiuje kilka typów ograniczeń. W tym przewodniku omówimy każdy z nich szczegółowo, pokażemy, kiedy go używać, i wskażemy typowe pułapki.

NOT NULL

Ten NOT NULL ograniczenie gwarantuje, że kolumna zawsze posiada wartość. Każda INSERT lub UPDATE która próbuje ustawić kolumnę na NULL zostanie odrzucony. Pusty ciąg znaków to nadal wartość; dodaj CHECK, gdy pusty tekst jest zabroniony.

CREATE TABLE products (
    id    SERIAL       PRIMARY KEY,
    name  VARCHAR(150) NOT NULL,   -- every product MUST have a name
    price NUMERIC(10,2) NOT NULL   -- every product MUST have a price
);

-- This will fail:
INSERT INTO products (name, price) VALUES (NULL, 9.99);
-- ERROR: null value in column "name" violates not-null constraint

Wskazówka: Domyślnie na NOT NULL dla każdej kolumny i usuwaj go tylko wtedy, gdy masz wyraźny powód biznesowy, aby zezwolić na wartości null. Wartości null wprowadzają logikę trójwartościową do zapytań i są częstym źródłem subtelnych błędów.

UNIQUE

A UNIQUE ograniczenie gwarantuje, że żadne dwa wiersze nie zawierają tej samej wartości w ograniczonej kolumnie (lub kombinacji kolumn). W przeciwieństwie do klucza głównego, UNIQUE kolumna może zawierać NULL. PostgreSQL i MySQL zazwyczaj pozwalają na wiele wartości null w kolumnie unikalnej; SQL Server pozwala na jedną w ograniczeniu unikalności pojedynczej kolumny. Jest to reguła unikalności, a nie wynik ewaluacji NULL = NULL.

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

-- Multi-column unique constraint
CREATE TABLE reservations (
    id        SERIAL PRIMARY KEY,
    room_id   INT    NOT NULL,
    date      DATE   NOT NULL,
    CONSTRAINT uq_room_date UNIQUE (room_id, date)
);

Wielokolumnowy UNIQUE powyższe ograniczenie gwarantuje, że ten sam pokój nie może zostać zarezerwowany dwukrotnie w tym samym terminie.

CHECK

CHECK ograniczenia sprawdzają, czy wartość spełnia wyrażenie logiczne przed jej zapisaniem. Wymuszają one reguły na poziomie wiersza, których sam system typów nie jest w stanie wyrazić. CHECK akceptuje TRUE lub UNKNOWN; połącz go z NOT NULL, gdy wartość jest wymagana.

CREATE TABLE employees (
    id     SERIAL       PRIMARY KEY,
    name   VARCHAR(100) NOT NULL,
    age    INT          NOT NULL CHECK (age >= 18 AND age <= 120),
    salary NUMERIC(12,2) NOT NULL CHECK (salary > 0)
);

-- Named CHECK constraint with multiple columns
CREATE TABLE events (
    id         SERIAL      PRIMARY KEY,
    start_date DATE        NOT NULL,
    end_date   DATE        NOT NULL,
    CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

Pułapka: W wersjach MySQL starszych niż 8.0.16,CHECK ograniczenia są analizowane, ale po cichu zignorowane. Zawsze sprawdzaj, czy wersja Twojej bazy danych faktycznie je wymusza.

DEFAULT

Ten DEFAULT dostarcza wartość, gdy kolumna zostanie pominięta w INSERT lub gdy jawnie zażądano DEFAULT. Jawne NULL nie wywołuje wartości domyślnej. Wartości domyślne redukują powtarzalny kod w aplikacji i sprawiają, że schematy dokumentują się same.

CREATE TABLE orders (
    id         SERIAL       PRIMARY KEY,
    status     VARCHAR(20)  NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ  NOT NULL DEFAULT now(),
    quantity   INT          NOT NULL DEFAULT 1
);

-- Inserting without specifying status, created_at, or quantity
INSERT INTO orders DEFAULT VALUES;
-- Result: status='pending', created_at=<current time>, quantity=1

DEFAULT z wyrażeniami

PostgreSQL i SQL Server pozwalają na wyrażenia i wywołania funkcji w wartościach domyślnych. MySQL obsługuje to od wersji 8.0.13 dla niektórych typów.

-- PostgreSQL: UUID default
CREATE TABLE tokens (
    id    UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    value TEXT NOT NULL
);

Ograniczenia PRIMARY KEY i FOREIGN KEY

Są to najbardziej strukturalne ograniczenia w relacyjnych bazach danych. PRIMARY KEY jednoznacznie identyfikuje każdy wiersz i pośrednio dodaje NOT NULL + UNIQUE. A FOREIGN KEY łączy jedną tabelę z drugą i wymusza integralność referencyjną.

CREATE TABLE departments (
    id   SERIAL      PRIMARY KEY,
    name VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id            SERIAL      PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INT          NOT NULL,
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (department_id)
        REFERENCES departments (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Zobacz nasz szczegółowy przewodnik po Klucze główne a klucze obce dla akcji kaskadowych, kluczy złożonych i najlepszych praktyk. Możesz również modelować te relacje wizualnie w Edytor Erd Go.

Ograniczenia na poziomie kolumny vs na poziomie tabeli

Ograniczenia mogą być zdefiniowane wewnątrzlinowy (na poziomie kolumny) lub jako oddzielna klauzula po wszystkich kolumnach (poziom tabeli). Wynik jest identyczny — ale poziom tabeli jest wymagany, gdy ograniczenie obejmuje wiele kolumn.

-- Column-level (single column)
CREATE TABLE items (
    id    SERIAL PRIMARY KEY,
    code  VARCHAR(20) NOT NULL UNIQUE,
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);

-- Table-level (multi-column constraint)
CREATE TABLE shipments (
    id          SERIAL PRIMARY KEY,
    origin      VARCHAR(3) NOT NULL,
    destination VARCHAR(3) NOT NULL,
    CONSTRAINT chk_different_locations CHECK (origin <> destination),
    CONSTRAINT uq_route UNIQUE (origin, destination)
);

Nazwane ograniczenia i dlaczego są ważne

Zawsze nazywaj swoje ograniczenia jawnie, używając CONSTRAINT słowo kluczowe. W przypadku naruszenia ograniczenia baza danych zwraca nazwę ograniczenia w komunikacie o błędzie — znaczącą nazwę, taką jak chk_positive_salary jest nieskończenie bardziej pomocne niż automatycznie wygenerowany hash.

-- Good: named constraint
ALTER TABLE products
    ADD CONSTRAINT chk_positive_price CHECK (price > 0);

-- You can also drop a constraint by name
ALTER TABLE products
    DROP CONSTRAINT chk_positive_price;

Spójna konwencja nazewnictwa sprawia, że migracje, debugowanie i wdrażanie zespołu są znacznie płynniejsze. Typowym wzorcem jest:

  • pk_<table> — klucze główne
  • fk_<table>_<column> — klucze obce
  • uq_<table>_<column(s)> — ograniczenia unikalności
  • chk_<table>_<rule> — ograniczenia CHECK

Łączenie ograniczeń i typowych wzorców

W praktyce kolumny niosą ze sobą kilka ograniczeń naraz. Oto wzorce, których będziesz używać wielokrotnie:

CREATE TABLE accounts (
    id         SERIAL       PRIMARY KEY,
    email      VARCHAR(255) NOT NULL,
    role       VARCHAR(20)  NOT NULL DEFAULT 'user'
                   CHECK (role IN ('user', 'admin', 'moderator')),
    balance    NUMERIC(12,2) NOT NULL DEFAULT 0.00
                   CHECK (balance >= 0),
    is_active  BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ           -- nullable: NULL means "not deleted"
);

Wzorzec miękkiego usuwania (Soft-Delete)

Ten deleted_at powyższa kolumna implementuje miękkie usuwanie. Zamiast usuwać wiersze, ustawiasz deleted_at = now(). Połącz to z częściowym indeksem unikalnym, aby zapobiec „aktywnym” duplikatom, jednocześnie zezwalając na usunięte wiersze z tym samym adresem e-mail. Tabela celowo nie posiada globalnego ograniczenia UNIQUE na e-mail; utrzymanie go odrzucałoby również duplikaty wśród usuniętych wierszy:

-- PostgreSQL: unique email only among active accounts
CREATE UNIQUE INDEX uq_accounts_active_email
    ON accounts (email)
    WHERE deleted_at IS NULL;

Typowe pułapki

  • NULL i UNIQUE: Większość baz danych pozwala na wiele NULL wartości w UNIQUE kolumna, ponieważ NULL nie jest równe niczemu — włączając w to siebie samego. SQL Server jest wyjątkiem i traktuje NULL domyślnie jako duplikat; użyj indeksu filtrowanego, aby to obejść.
  • CHECK w starszych wersjach MySQL: Jak wspomniano powyżej, MySQL przed wersją 8.0.16 analizuje, ale ignoruje CHECK ograniczenia. Sprawdź swoją wersję MySQL lub wymuś reguły w kodzie aplikacji.
  • Odroczone ograniczenia: PostgreSQL obsługuje odroczone sprawdzanie kluczy obcych, unikalności, kluczy głównych i ograniczeń wykluczających zadeklarowanych DEFERRABLE INITIALLY DEFERRED. CHECK i NOT NULL nie mogą być odroczone. Jest to przydatne w przypadku odwołań cyklicznych, ale może utrudnić debugowanie.
  • Wydajność CHECK: Prosty CHECK ograniczenia mają znikomy narzut. Unikaj osadzania podzapytań lub wywołań funkcji w CHECK — mogą nawet nie być obsługiwane i mogą ukrywać powolną logikę.
  • Zmiana ograniczeń w dużych tabelach: Dodawanie NOT NULL ograniczenie w PostgreSQL może wymagać skanowania tabeli, chyba że istniejące zweryfikowane ograniczenie CHECK udowadnia, że kolumna nie zawiera wartości null. W przypadku bardzo dużych tabel rozważ dodanie CHECK (column IS NOT NULL) NOT VALID najpierw, a następnie oddzielna walidacja.

Przejrzyj ograniczenia w SQL i na diagramie razem w Edytor SQL do ERD, a następnie zweryfikuj ich zachowanie w docelowej bazie danych. Diagram nie wymusza integralności danych.

Referencje i dalsza lektura

Więcej postów