Håndhev dataintegritet med begrensninger

12 minutter

Begrensninger og sekvensobjekter er designvalg som forhindrer dataproblemer før de oppstår. En manglende fremmednøkkelbegrensning betyr at foreldreløse poster allerede kan eksistere i databasen din. Å legge til sekvensobjekter senere for å erstatte identitetskolonner krever endringer på tvers av alle applikasjoner. Applikasjonskode kan validere data, men brukere kan omgå det gjennom masseimport, direkte spørringer eller nye applikasjoner som hopper over validering.

Databasebegrensninger håndhever regler på motornivå, så de gjelder alltid. Avgjørelsene du tar under designet, som hvilke regler du skal håndheve i databasen og om du skal bruke identitetskolonner eller sekvenser, påvirker datakvaliteten din gjennom hele applikasjonens levetid.

Forstå når du skal bruke begrensninger

Problemer med datakvalitet er kostbare. Dårlig datakvalitet fører til feilaktige forretningsbeslutninger, mislykkede integrasjoner og brudd på samsvar. I motsetning til validering på applikasjonsnivå, som kan være inkonsistent mellom ulike applikasjoner som får tilgang til samme database, håndhever begrensninger regler på databasemotornivå hvor de ikke kan omgås av applikasjonskode, ad hoc-spørringer, direkte SQL-skript eller bulkimport. Hver INSERT, UPDATE, og-operasjon DELETE må oppfylle alle definerte begrensninger før databasemotoren gjennomfører endringen.

Bruk databasebegrensninger

Begrensninger forhindrer datakvalitetsproblemer før de ødelegger databasen din. Tabellen nedenfor viser hvordan hver begrensningstype adresserer spesifikke problemer med dataintegritet:

Problem	Begrensning	Eksempel
Foreldreløse opptegnelser	FREMMEDNØKKEL	Forhindrer bestillinger uten gyldige kunder
Duplikatdata	UNIKT	Stopper dupliserte e-postregistreringer
Ugyldige data	SJEKK	Avviser negative priser eller fremtidige fødselsdatoer
Manglende kritiske data	IKKE NULL	Forhindrer ufullstendige poster
Referanseinkonsistens	FREMMEDNØKKEL	Opprettholder dataintegritet på tvers av tabeller

Tenk på et detaljhandelsselskap som ikke har definert en unik begrensning i sin kunde-e-postkolonne. Over tid ble de samme kundene registrert flere ganger med identiske e-postadresser. Når markedsføringen sendte kampanjer, mottok noen kunder tre kopier av samme e-post, noe som økte kostnadene og skadet kundens tillit. Å legge til UNIQUE (EmailAddress) i tabelldefinisjonen ville ha forhindret at disse duplikatene noen gang ble satt inn.

Begrensninger håndhever regler på databasemotornivå, og sikrer datakvalitet uavhengig av hvordan data kommer inn i systemet. Applikasjonsvalidering kan omgås, varierer fra applikasjon til applikasjon, og er vanskeligere å opprettholde. Databasebegrensninger håndheves, er sentraliserte og gir én sannhetskilde.

Begrensninger sikrer datakvalitet og konsistens på databasenivå.

Bruk primærnøkkelbegrensninger

Primærnøkkelbegrensninger garanterer unike data og håndhever enhetens integritet. Når du spesifiserer en primærnøkkelbegrensning, oppretter Database Engine automatisk en unik indeks for primærnøkkelkolonnene. En tabell kan bare inneholde én primærnøkkelbegrensning, og alle kolonner definert innenfor en primærnøkkelbegrensning må defineres som NOT NULL.

Du kan lage en primærnøkkel ved å bruke begrensningen PRIMARY KEY . Her er et eksempel:

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

Bruk fremmednøkkelbegrensninger

Fremmednøkkelbegrensninger håndhever referanseintegritet ved å kontrollere dataene som kan lagres i fremmednøkkeltabellen. En fremmednøkkelbegrensning forhindrer endringer i data i primærnøkkeltabellen hvis disse endringene ugyldiggjør koblingen til data i fremmednøkkeltabellen.

Du kan definere kaskaderende referansehandlinger som CASCADE, SET NULL, eller SET DEFAULT spesifisere hva som skjer når en bruker prøver å slette eller oppdatere en nøkkel som eksisterende fremmednøkler peker på. Selv om det ikke er nødvendig å manuelt lage en indeks på fremmednøkkelkolonner , er det ofte nyttig fordi fremmednøkkelkolonner ofte brukes i join-kriterier.

Du kan lage en fremmednøkkel ved å bruke begrensningen FOREIGN KEY med en REFERENCES klausul. Her er et eksempel:

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

Bruk unike begrensninger

Unike begrensninger sikrer at ingen duplikatverdier legges inn i spesifikke kolonner som ikke deltar i en primærnøkkel. I motsetning til PRIMARY KEY begrensninger, UNIQUE tillater begrensninger verdien NULL. Men som med alle verdier som deltar i en UNIQUE begrensning, er det kun tillatt én nullverdi per kolonne. Databasemotoren oppretter automatisk en unik ikke-klynget indeks for å håndheve kravet om unikhet.

Du kan lage en unik begrensning ved å bruke UNIQUE nøkkelordet. Her er et eksempel:

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

Bruk sjekkbegrensninger

Sjekkbegrensninger håndhever domeneintegritet ved å begrense verdiene som aksepteres av én eller flere kolonner. Du kan lage en CHECK begrensning med hvilket som helst logisk uttrykk som returnerer TRUE eller FALSE baserer seg på logiske operatorer. Du kan bruke flere CHECK begrensninger på én kolonne eller én CHECK begrensning på flere kolonner.

Fordi nullverdier evaluerer til UNKNOWN, kan deres tilstedeværelse i uttrykk overstyre en begrensning. For eksempel tillater en begrensning MyColumn = 10 på en INT kolonne fortsatt å settes inn fordi NULL ikke evaluerer til FALSE.NULL

Du kan lage en CHECK-begrensning ved å bruke CHECK nøkkelordet med et logisk uttrykk. Her er et eksempel:

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

Bruk standardbegrensninger

Standardbegrensninger gir standardverdier når ingen verdi er spesifisert under INSERT operasjoner. Når man jobber med databaseprosjekter, anbefales det å lage begrensninger med eksplisitte navn i stedet for å tillate systemgenererte navn, som varierer mellom miljøer.

Du kan lage en STANDARD-begrensning ved å bruke DEFAULT nøkkelordet. Her er et eksempel:

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
);

Bruk sekvensobjekter

Et sekvensobjekt er et brukerdefinert skjema-bundet objekt som genererer en sekvens av numeriske verdier i henhold til spesifikasjonen som sekvensen ble laget med. I motsetning til identitetskolonner er sekvenser ikke knyttet til spesifikke tabeller. Applikasjoner refererer til et sekvensobjekt for å hente neste verdi, og forholdet mellom sekvenser og tabeller styres av applikasjonen.

Identitetskolonner fungerer godt når du trenger automatisk nummerering for én enkelt tabell. Men de er begrenset til det ene bordet. Du kan ikke dele tallene på tvers av flere tabeller, få neste verdi før du setter inn en rad, eller enkelt tilbakestille telleren. Sekvensobjekter løser disse problemene ved å generere tall uavhengig av enhver tabell.

Forstå når du skal bruke sekvenser

Bruk sekvenser i stedet for identitetskolonner i følgende scenarioer:

Delt tallserie – Applikasjonen krever at man deler en enkelt tallserie mellom flere tabeller eller flere kolonner i en tabell.
Sykkelnummerserie – Applikasjonen må starte nummerserien på nytt når et spesifisert nummer er nådd. For eksempel, etter å ha tildelt verdiene 1 til 10, begynner applikasjonen å tildele verdiene 1 til 10 igjen.
Sorterte sekvensverdier – Applikasjonen krever at sekvensverdier sorteres etter et annet felt. Funksjonen NEXT VALUE FOR kan anvende klausulen OVER , noe som garanterer at verdiene som returneres genereres i klausulens ORDER BY rekkefølge.
Reserver flere numre – En applikasjon må reservere flere sekvensielle numre samtidig. Forespørsler om identitetsverdier kan føre til hull hvis andre prosesser samtidig får utstedt numre. Anrop sp_sequence_get_range henter flere tall i sekvensen samtidig.
Endringsbar spesifikasjon – Du må endre spesifikasjonen av sekvensen, for eksempel inkrementverdien, etter opprettelse.

Sekvensobjekter kan tilby mer fleksibilitet enn identitetskolonner:

Funksjon	Sequence	Identity
Bundet til bordet	Nei	Ja
Delt på tvers av tabeller eller kolonner	Ja	Nei
Få neste verdi før innsettingsoperasjonen	Ja	Nei
Egendefinerte min/max-verdier	Ja	Begrenset
Hent flere tall samtidig	Ja	Nei
Syklus/omstart på spesifisert antall	Ja	Nei
Sorter verdier etter et annet felt	Ja	Nei
Endringsintreprise etter opprettelse	Ja	Nei

Bruk en identitetskolonne når du trenger en enkel automatisk inkrementerende primærnøkkel for en enkelt tabell og ikke trenger å dele samme tallserie på tvers av flere tabeller eller hente neste verdi før du setter inn raden.

Bruk en sekvens når applikasjonen din krever et nummer før innsettingen lages, må dele en enkelt serie mellom flere tabeller, må starte nummerserien på nytt når et spesifisert tall er nådd, eller må reservere flere sekvensielle tall samtidig.

Forstå sekvensbegrensninger

I motsetning til identitetskolonner er ikke sekvensverdier automatisk beskyttet etter innsetting i en tabell. I tillegg håndheves ikke unikhet automatisk for sekvensverdier. Hvis sekvensverdier i en tabell må være unike, lag en unik begrensning på kolonnen.

Sekvensnumre genereres utenfor omfanget av den nåværende transaksjonen. De brukes enten transaksjonen med sekvensnummeret er committert eller reversert.

Du kan lage et sekvensobjekt ved å bruke setningen CREATE SEQUENCE med valgfrie parametere for start, inkrementasjon og rekkevidde. Her er et eksempel:

-- 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;

Dette eksempelet skaper en sekvens som OrderNumber starter på 1000, øker med 1, og stopper ved 999999 uten å sykle tilbake. Funksjonen NEXT VALUE FOR henter neste tilgjengelige tall, enten inline under en INSERT setning eller tildelt en variabel før innsettingen når applikasjonen først må referere til verdien. For batchoperasjoner som krever flere sekvensielle tall samtidig, sp_sequence_get_range reserverer du en blokk på 100 tall, og returnerer de første og siste verdiene i området. Setningen ALTER SEQUENCE tilbakestiller sekvensen til 1000 når det trengs.

Begrensninger er arkitektoniske beslutninger som forhindrer problemer før de oppstår. En manglende CHECK begrensning gjør at ugyldige data kan ødelegge databasen din lydløst. Å velge identitetskolonner når du trenger tverrtabell-nummerering tvinger applikasjonsnivå-løsninger. Begrensninger definert på databasenivå beskytter datakvaliteten uansett hvilken applikasjon, verktøy eller skript som får tilgang til databasen din. Disse beslutningene former dine dataintegritetsgarantier for hele applikasjonens levetid.

Tilbakemeldinger

Var denne siden nyttig?