Framtvinga dataintegritet med begränsningar

12 minuter

Begränsningar och sekvensobjekt är designval som förhindrar dataproblem innan de inträffar. Ett sekundärnyckelvillkor som saknas innebär att överblivna poster kanske redan finns i databasen. Att lägga till sekvensobjekt senare för att ersätta identitetskolumner kräver ändringar i alla program. Programkod kan verifiera data, men användarna kan kringgå dem via massimporter, direkta frågor eller nya program som hoppar över validering.

Databasbegränsningar tillämpar regler på motornivå, så de gäller alltid. De beslut du fattar under designen, till exempel vilka regler som ska tillämpas i databasen och om du vill använda identitetskolumner eller sekvenser, påverkar datakvaliteten under programmets livslängd.

Förstå när du ska använda begränsningar

Datakvalitetsproblem är dyra. Dålig datakvalitet leder till felaktiga affärsbeslut, misslyckade integreringar och efterlevnadsöverträdelser. Till skillnad från validering på programnivå som kan vara inkonsekvent i olika program som har åtkomst till samma databas, tillämpar begränsningar regler på databasmotornivå där de inte kan kringgås av programkod, ad hoc-frågor, direkta SQL-skript eller massimporter. Varje INSERTåtgärd , UPDATEoch DELETE måste uppfylla alla definierade begränsningar innan databasmotorn genomför ändringen.

Tillämpa databasbegränsningar

Begränsningar förhindrar datakvalitetsproblem innan de skadar databasen. I följande tabell visas hur varje villkorstyp hanterar specifika problem med dataintegritet:

Bekymmer	Begränsning	Example
Överblivna poster	FRÄMMANDE NYCKEL	Förhindrar beställningar utan giltiga kunder
Duplicera data	UNIQUE	Stoppar duplicerade e-postregistreringar
Ogiltiga data	CHECK	Avvisar negativa priser eller framtida födelsedatum
Viktiga data saknas	IKKE NULL	Förhindrar ofullständiga register
Referensinkonsekvens	främmande nyckel	Upprätthåller dataintegritet mellan tabeller

Överväg ett detaljhandelsföretag som inte definierade en unik begränsning för kundens e-postkolumn. Med tiden registrerades samma kunder flera gånger med identiska e-postadresser. När marknadsföringen skickade kampanjkampanjer fick vissa kunder tre kopior av samma e-post, vilket ökade kostnader och skadade kundernas förtroende. Att lägga UNIQUE (EmailAddress) till i tabelldefinitionen skulle ha hindrat dessa dubbletter från att någonsin infogas.

Begränsningar tillämpar regler på databasmotornivå, vilket säkerställer datakvalitet oavsett hur data kommer in i systemet. Programvalidering kan kringgås, varierar beroende på program och är svårare att underhålla. Databasbegränsningar tillämpas alltid, centraliseras och ger en sanningskälla.

Begränsningar säkerställer datakvalitet och konsekvens på databasnivå.

Använda primärnyckelbegränsningar

Primärnyckelbegränsningar garanterar unika data och framtvingar entitetsintegritet. När du anger ett primärnyckelvillkor skapar databasmotorn automatiskt ett unikt index för primärnyckelkolumnerna. En tabell kan bara innehålla en primärnyckelbegränsning och alla kolumner som definierats inom en primärnyckelvillkor måste definieras som NOT NULL.

Du kan skapa en primärnyckel med hjälp av villkoret PRIMARY KEY . Här är ett exempel:

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    EmailAddress NVARCHAR(100) NOT NULL
);

Använda begränsningar för främmande nyckel

Begränsningar för sekundärnyckel framtvingar referensintegritet genom att kontrollera de data som kan lagras i sekundärnyckeltabellen. En begränsning för sekundärnyckel förhindrar ändringar av data i primärnyckeltabellen om ändringarna ogiltigförklarar länken till data i tabellen med sekundärnyckel.

Du kan definiera sammanhängande referensåtgärder som , CASCADEeller SET NULL för att ange vad som SET DEFAULThänder när en användare försöker ta bort eller uppdatera en nyckel som befintliga sekundärnycklar pekar på. Även om det inte krävs att skapa ett index för sekundärnyckelkolumner manuellt är det ofta användbart eftersom sekundärnyckelkolumner ofta används i kopplingsvillkor.

Du kan skapa en främmande nyckel genom att använda begränsningen FOREIGN KEY med en REFERENCES-sats. Här är ett exempel:

CREATE TABLE Order (
    OrderID INT PRIMARY KEY IDENTITY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Använda unika begränsningar

Unika begränsningar säkerställer att inga duplicerade värden anges i specifika kolumner som inte deltar i en primärnyckel. Till skillnad från PRIMARY KEY begränsningar tillåter UNIQUE begränsningar värdet NULL. Men precis som med alla värden som deltar i ett UNIQUE villkor tillåts endast ett null-värde per kolumn. Databasmotorn skapar automatiskt ett unikt icke-grupperat index för att framtvinga kravet på unikhet.

Du kan skapa en unik begränsning med hjälp av nyckelordet UNIQUE . Här är ett exempel:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    SKU NVARCHAR(50) UNIQUE,
    ProductName NVARCHAR(100)
);

Använda kontrollbegränsningar

Begränsningar främjar domänintegritet genom att begränsa de värden som accepteras av en eller flera kolumner. Du kan skapa en CHECK begränsning med alla logiska uttryck som returnerar TRUE eller FALSE baseras på logiska operatorer. Du kan använda flera CHECK begränsningar för en enskild kolumn eller tillämpa en enda CHECK begränsning på flera kolumner.

Eftersom null-värden utvärderas till UNKNOWNkan deras närvaro i uttryck åsidosätta en begränsning. En begränsning på en MyColumn = 10-kolumn tillåter fortfarande att NULL infogas eftersom NULL inte utvärderas till FALSE.

Du kan skapa en CHECK-begränsning med hjälp av nyckelordet CHECK med ett logiskt uttryck. Här är ett exempel:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    HireDate DATE,
    Salary DECIMAL(10,2),
    CHECK (Salary >= 20000),
    CHECK (HireDate <= GETDATE())
);

Använd standardbegränsningar

Standardbegränsningar ger standardvärden när inget värde anges under INSERT åtgärderna. När du arbetar med databasprojekt rekommenderar vi att du skapar begränsningar med explicita namn i stället för att tillåta systemgenererade namn, som skiljer sig åt mellan olika miljöer.

Du kan skapa en STANDARD-begränsning med hjälp av nyckelordet DEFAULT . Här är ett exempel:

CREATE TABLE Activity (
    ActivityID INT PRIMARY KEY IDENTITY,
    Description NVARCHAR(200),
    CreatedDate DATETIME2 CONSTRAINT DF_Activity_CreatedDate DEFAULT GETUTCDATE(),
    IsActive BIT CONSTRAINT DF_Activity_IsActive DEFAULT 1
);

Använda sekvensobjekt

Ett sekvensobjekt är ett användardefinierat schemabundet objekt som genererar en sekvens med numeriska värden enligt specifikationen som sekvensen skapades med. Till skillnad från identitetskolumner associeras inte sekvenser med specifika tabeller. Program refererar till ett sekvensobjekt för att hämta nästa värde och relationen mellan sekvenser och tabeller styrs av programmet.

Identitetskolumner fungerar bra när du behöver automatisk numrering för en enskild tabell. De är dock begränsade till den där tabellen. Du kan inte dela talen i flera tabeller, hämta nästa värde innan du infogar en rad eller enkelt återställa räknaren. Sekvensobjekt löser dessa problem genom att generera tal oberoende av en tabell.

Förstå när sekvenser ska användas

Använd sekvenser i stället för identitetskolumner i följande scenarier:

Serie med delat nummer – Programmet kräver att du delar en enda serie tal mellan flera tabeller eller flera kolumner i en tabell.
Cykelnummerserie – Programmet måste starta om nummerserien när ett angivet tal har nåtts. När du till exempel har tilldelat värdena 1 till 10 börjar programmet tilldela värden 1 till 10 igen.
Sorterade sekvensvärden – Programmet kräver att sekvensvärden sorteras efter ett annat fält. Funktionen NEXT VALUE FOR kan tillämpa OVER -satsen, vilket garanterar att de värden som returneras genereras i satsens ORDER BY ordning.
Reservera flera tal – Ett program måste reservera flera sekventiella tal samtidigt. Att begära unika identitetsvärden kan leda till luckor om andra processer samtidigt tilldelades nummer. Anrop sp_sequence_get_range hämtar flera tal i sekvensen samtidigt.
Ändringsbar specifikation – Du måste ändra specifikationen för sekvensen, till exempel inkrementsvärdet, när du har skapat den.

Sekvensobjekt kan ge mer flexibilitet än identitetskolumner:

Egenskap	Sekvens	Identitet
Kopplad till tabellen	Nej.	Ja
Delas mellan tabeller eller kolumner	Ja	Nej.
Hämta nästa värde före infogningsåtgärden	Ja	Nej.
Anpassade min/max-värden	Ja	Begränsad
Hämta flera tal samtidigt	Ja	Nej.
Cykla/omstart vid ett angivet antal	Ja	Nej.
Sortera värden efter ett annat fält	Ja	Nej.
Ändra ökning efter att du har skapat	Ja	Nej.

Använd en identitetskolumn när du behöver en enkel primärnyckel för automatisk inmatning för en enskild tabell och inte behöver dela samma nummerserie i flera tabeller eller hämta nästa värde innan du infogar raden.

Använd en sekvens när programmet kräver ett tal innan infogningen görs, måste dela en serie mellan flera tabeller, måste starta om nummerserien när ett angivet tal har nåtts eller måste reservera flera sekventiella tal samtidigt.

Förstå sekvensbegränsningar

Till skillnad från identitetskolumner skyddas inte sekvensvärden automatiskt efter infogning i en tabell. Dessutom tillämpas inte unikhet automatiskt för sekvensvärden. Om sekvensvärdena i en tabell måste vara unika skapar du en unik begränsning för kolumnen.

Sekvensnummer genereras utanför den aktuella transaktionens omfång. De används oavsett om transaktionen med sekvensnumret har begåtts eller återställts.

Du kan skapa ett sekvensobjekt med hjälp av -instruktionen CREATE SEQUENCE med valfria parametrar för start, inkrement och intervall. Här är ett exempel:

-- Create sequence
CREATE SEQUENCE OrderNumber 
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999
    NO CYCLE;

-- Use sequence in INSERT with NEXT VALUE FOR function
INSERT INTO Order (OrderID, CustomerID, OrderNumber, OrderDate)
VALUES (1, 100, NEXT VALUE FOR OrderNumber, GETDATE());

-- Get next value before INSERT
DECLARE @NextOrderNum INT = NEXT VALUE FOR OrderNumber;
SELECT @NextOrderNum;

-- Get multiple sequence numbers at once for batch processing
DECLARE @FirstSeq INT, @LastSeq INT;
EXEC sp_sequence_get_range 
    @sequence_name = N'OrderNumber',
    @range_size = 100,
    @range_first_value = @FirstSeq OUTPUT,
    @range_last_value = @LastSeq OUTPUT;

-- Reset sequence
ALTER SEQUENCE OrderNumber RESTART WITH 1000;

Det här exemplet skapar en sekvens med namnet OrderNumber som börjar vid 1000, ökar med 1 och stannar vid 999999 utan att cykla tillbaka. Funktionen NEXT VALUE FOR hämtar nästa tillgängliga nummer, antingen infogat under en INSERT -instruktion eller tilldelad till en variabel före infogningen när programmet måste referera till värdet först. För batchåtgärder som kräver flera sekventiella tal samtidigt reserverar sp_sequence_get_range ett block med 100 tal och returnerar de första och sista värdena i intervallet. -instruktionen ALTER SEQUENCE återställer sekvensen till 1 000 när det behövs.

Begränsningar är arkitektoniska beslut som förhindrar problem innan de uppstår. En begränsning som saknas CHECK gör att ogiltiga data kan skada databasen i tysthet. Att välja identitetskolumner när du behöver numrering över flera tabeller kräver anpassningar på applikationsnivå. Begränsningar som definieras på databasnivå skyddar datakvaliteten oavsett vilket program, verktyg eller skript som kommer åt databasen. Dessa beslut formar dina dataintegritetsgarantier för programmets livslängd.

Feedback

Var den här sidan till hjälp?