Håndhæv dataintegritet med begrænsninger

Begrænsninger og sekvensobjekter er designvalg, der forhindrer dataproblemer, før de opstår. En manglende fremmednøglebegrænsning betyder, at forældreløse poster måske allerede findes i din database. Tilføjelse af sekvensobjekter senere for at erstatte identitetskolonner kræver ændringer på tværs af alle applikationer. Applikationskode kan validere data, men brugere kan omgå det gennem masseimporter, direkte forespørgsler eller nye applikationer, der springer valideringen over.

Databasebegrænsninger håndhæver regler på motorniveau, så de gælder altid. De beslutninger, du træffer under designet, såsom hvilke regler der skal håndhæves i databasen, og om du skal bruge identitetskolonner eller sekvenser, påvirker din datakvalitet gennem hele applikationens levetid.

Forstå hvornår du skal bruge begrænsninger

Datakvalitetsproblemer er dyre. Dårlig datakvalitet fører til forkerte forretningsbeslutninger, fejlslagne integrationer og overtrædelser af overholdelse. I modsætning til validering på applikationsniveau, som kan være inkonsistent på tværs af forskellige applikationer, der tilgår den samme database, håndhæver begrænsninger regler på databasemotorniveau, hvor de ikke kan omgås af applikationskode, ad hoc-forespørgsler, direkte SQL-scripts eller masseimporter. Hver INSERT, UPDATE, og DELETE operation skal opfylde alle definerede begrænsninger, før databasemotoren foretager ændringen.

Anvend databasebegrænsninger

Begrænsninger forhindrer datakvalitetsproblemer, før de ødelægger din database. Følgende tabel viser, hvordan hver begrænsningstype adresserer specifikke problemer med dataintegritet:

Forestil dig en detailvirksomhed, der ikke har defineret en unik begrænsning i deres kundemail-kolonne. Over tid blev de samme kunder registreret flere gange med identiske e-mailadresser. Når marketing sendte kampagner, modtog nogle kunder tre kopier af den samme e-mail, hvilket øgede omkostningerne og skadede kundernes tillid. At tilføje UNIQUE (EmailAddress) til tabeldefinitionen ville have forhindret, at disse dubletter nogensinde blev indsat.

Begrænsninger håndhæver regler på databasemotor-niveau og sikrer datakvalitet uanset hvordan data kommer ind i systemet. Applikationsvalidering kan omgås, varierer fra applikation til applikation og er sværere at vedligeholde. Databasebegrænsninger håndhæves, centraliseres altid og udgør én sandhedskilde.

Begrænsninger sikrer datakvalitet og konsistens på databaseniveau.

Brug primærnøglebegrænsninger

Primærnøglebegrænsninger garanterer unikke data og håndhæver enhedens integritet. Når du angiver en primærnøglebegrænsning, opretter Database Engine automatisk et unikt indeks for primærnøglekolonnerne. En tabel kan kun indeholde én primærnøglebegrænsning, og alle kolonner defineret inden for en primær nøglebegrænsning skal defineres som NOT NULL.

Du kan oprette en primærnøgle ved at bruge begrænsningen PRIMARY KEY . Her er et eksempel:

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

Brug fremmednøglebegrænsninger

Fremmednøglebegrænsninger håndhæver referenceintegritet ved at kontrollere de data, der kan gemmes i fremmednøgletabellen. En fremmednøglebegrænsning forhindrer ændringer i data i primærnøgletabellen, hvis disse ændringer ugyldiggør linket til data i fremmednøgletabellen.

Du kan definere kaskaderende referencehandlinger som CASCADE, SET NULL, eller SET DEFAULT angive, hvad der sker, når en bruger forsøger at slette eller opdatere en nøgle, som eksisterende fremmednøgler peger på. Selvom det ikke er nødvendigt manuelt at oprette et indeks på fremmednøglekolonner , er det ofte nyttigt, fordi fremmednøglekolonner ofte bruges i join-kriterier.

Du kan oprette en fremmednøgle ved at bruge begrænsningen 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)
);

Brug unikke begrænsninger

Unique-begrænsninger sikrer, at der ikke indtastes dubletter i specifikke kolonner, der ikke deltager i en primærnøgle. I modsætning til PRIMARY KEY begrænsninger UNIQUE tillader begrænsninger værdien NULL. Men som med enhver værdi, der deltager i en UNIQUE begrænsning, er kun én nulværdi tilladt pr. kolonne. Database Engine opretter automatisk et unikt ikke-klyngede indeks for at håndhæve kravet om enkhed.

Du kan oprette en unik begrænsning ved at bruge UNIQUE nøgleordet. Her er et eksempel:

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

Brug kontrolbegrænsninger

Kontrolbegrænsninger håndhæver domæneintegritet ved at begrænse de værdier, der accepteres af en eller flere kolonner. Du kan oprette en CHECK begrænsning med ethvert logisk udtryk, der returnerer TRUE eller FALSE er baseret på logiske operatorer. Du kan anvende flere CHECK begrænsninger på en enkelt kolonne eller anvende en enkelt CHECK begrænsning på flere kolonner.

Fordi nulværdier evaluerer til UNKNOWN, kan deres tilstedeværelse i udtryk tilsidesætte en begrænsning. For eksempel tillader en begrænsning MyColumn = 10 på en INT kolonne stadig, at indsættes, fordi NULL ikke evaluerer til FALSE.NULL

Du kan oprette en CHECK-begrænsning ved at bruge CHECK nøgleordet med et logisk udtryk. Her er et eksempel:

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

Brug standardbegrænsninger

Standardbegrænsninger giver standardværdier, når der ikke er angivet nogen værdi under INSERT operationerne. Når man arbejder med databaseprojekter, anbefales det at oprette begrænsninger med eksplicitte navne frem for at tillade systemgenererede navne, som varierer mellem miljøer.

Du kan oprette en STANDARD-begrænsning ved at bruge DEFAULT nøgleordet. 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
);

Brug sekvensobjekter

Et sekvensobjekt er et brugerdefineret skema-bundet objekt, der genererer en sekvens af numeriske værdier i henhold til den specifikation, som sekvensen blev oprettet med. I modsætning til identitetskolonner er sekvenser ikke knyttet til specifikke tabeller. Applikationer henviser til et sekvensobjekt for at hente dets næste værdi, og forholdet mellem sekvenser og tabeller styres af applikationen.

Identitetskolonner fungerer godt, når du har brug for automatisk nummerering til en enkelt tabel. Men de er begrænset til det ene bord. Du kan ikke dele tallene på tværs af flere tabeller, få den næste værdi før du indsætter en række, eller nemt nulstille tælleren. Sekvensobjekter løser disse problemer ved at generere tal uafhængigt af enhver tabel.

Forstå hvornår du skal bruge sekvenser

Brug sekvenser i stedet for identitetskolonner i følgende scenarier:

• Delt talserie - Applikationen kræver, at en enkelt talserie deles mellem flere tabeller eller flere kolonner i en tabel.
• Cykelnummerserie - Applikationen skal genstarte nummerserien, når et angivet tal er nået. For eksempel, efter at have tildelt værdierne 1 til 10, begynder applikationen igen at tildele værdierne 1 til 10.
• Sorterede sekvensværdier - Applikationen kræver, at sekvensværdier sorteres efter et andet felt. Funktionen NEXT VALUE FOR kan anvende klausulen OVER , hvilket garanterer, at de værdier, der returneres, genereres i klausulens ORDER BY rækkefølge.
• Reserver flere numre – En applikation skal reservere flere sekventielle numre på én gang. Anmodning om identitetsværdier kunne resultere i huller, hvis andre processer samtidig fik udstedt numre. Kald sp_sequence_get_range henter flere tal i sekvensen på én gang.
• Ændringsbar specifikation - Du skal ændre specifikationen af sekvensen, såsom inkrementværdien, efter oprettelsen.

Sekvensobjekter kan tilbyde mere fleksibilitet end identitetskolonner:

Brug en identitetskolonne, når du har brug for en simpel automatisk inkrementerende primærnøgle til en enkelt tabel og ikke behøver at dele den samme talserie på tværs af flere tabeller eller hente den næste værdi, før du indsætter rækken.

Brug en sekvens, når din applikation kræver et nummer, før indsættelsen laves, skal dele en enkelt række mellem flere tabeller, skal genstarte talrækken, når et angivet tal er nået, eller skal reservere flere sekventielle numre på én gang.

Forstå sekvensbegrænsninger

I modsætning til identitetskolonner er sekvensværdier ikke automatisk beskyttet efter indsættelse i en tabel. Desuden håndhæves unikhed ikke automatisk for sekvensværdier. Hvis sekvensværdier i en tabel skal være unikke, opret en entydig begrænsning på kolonnen.

Sekvensnumre genereres uden for den aktuelle transaktions omfang. De forbruges, uanset om transaktionen med sekvensnummeret er committet eller rulles tilbage.

Du kan oprette et sekvensobjekt ved at bruge sætningen CREATE SEQUENCE med valgfrie parametre for start, inkrementering og interval. 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 eksempel skaber en sekvens ved navn, OrderNumber der starter ved 1000, stiger med 1 og stopper ved 999999 uden at cykle tilbage. Funktionen NEXT VALUE FOR henter det næste tilgængelige tal, enten inline under en INSERT sætning eller tildelt en variabel før indsættelsen, når applikationen først skal referere til værdien. For batchoperationer, der kræver flere sekventielle numre på én gang, sp_sequence_get_range reserveres en blok med 100 tal, som returnerer de første og sidste værdier i intervallet. Sætningen nulstiller sekvensen tilbage til 1000, når det ALTER SEQUENCE er nødvendigt.

Begrænsninger er arkitektoniske beslutninger, der forhindrer problemer, før de opstår. En manglende CHECK begrænsning tillader ugyldige data at ødelægge din database lydløst. Valg af identitetskolonner, når du har brug for tværtabelnummerering, tvinger applikationsniveau-løsninger til løsninger. Begrænsninger defineret på databaseniveau beskytter datakvaliteten uanset, hvilket program, værktøj eller script der tilgår din database. Disse beslutninger former dine garantier for dataintegritet gennem hele applikationens levetid.