Nycklar och index. Databaser Exempel på motivering för tomma främmande nycklar

De används i alla aktiviteter: inom bank- och finansbranschen, turistbranschen, lager, produktion och utbildning. De är en samling tabeller, har tydliga egenskaper och är föremål för hårda krav. I relationsdatabaser kallas tabeller för relationer.

Vad är en primärnyckel i en databas

I en databas är primärnyckeln för en tabell en av dess kolumner (Primärnyckel). Låt oss titta på ett exempel på hur detta ser ut. Låt oss föreställa oss en enkel attityd hos universitetsstudenter (låt oss kalla det "Studenter").

Vi måste identifiera en elev unikt med en kolumn. För att göra detta måste informationen i denna kolumn vara unik för varje post. Men tillgängliga data i detta avseende tillåter oss inte att entydigt identifiera posten, eftersom namne, namne och studenter med samma efternamn och förnamn kan studera i samma kurs och fakultet. Den primära nyckeln i databasen används för att exakt identifiera den önskade raden i en relation. Oftast används ett numeriskt fält i denna egenskap, vilket automatiskt ökar när en post skrivs in (automatisk inkrementerande identifieringskolumn).

Enkel och sammansatt primärnyckel

Primär nyckel kan vara enkel eller sammansatt. Om det unika hos en post bestäms av värdet i endast ett fält, som beskrivits ovan, har vi att göra med en enkel nyckel. En sammansatt nyckel är en primär databasnyckel som består av två eller flera fält. Tänk på följande attityd hos bankkunder.

Fullständigt namn	Födelsedatum	Pass-serien	Passnummer
Ivanov P.A.	12.05.1996	75	0553009
Sergeev V.T.	14.07.1958	71	4100654
Krasnov L.V.	22.01.2001	73	1265165

Människors pass kan innehålla samma serie eller nummer, men det finns inga pass med samma serie och nummerkombination. Således kommer fälten "Passerie" och "Passnummer" att bli en sammansatt nyckel för den angivna relationen, som unikt identifierar personen.

Samband mellan relationer

Så, en primärnyckel i en databas är en eller flera kolumner i en tabell som låter en unikt identifiera en rad i den relationen. Vad är det till för?

Låt oss återgå till det första exemplet med relationen "Studenter". Utöver detta förhållande lagrar databasen även annan information, till exempel varje elevs prestation. För att inte upprepa all information som redan finns i databasen använder de en nyckel som refererar till den önskade posten. Det ser ut så här.

I de två exempelrelationerna ser vi ett ID-fält. Dessa är de primära nycklarna i databasen för dessa tabeller. Som du kan se innehåller den akademiska posten endast länkar till dessa fält från andra tabeller utan att all information från dem behöver anges.

Naturlig nyckel och surrogatnyckel

Hur bestäms primärnyckeln för en databastabell? De två exemplen vi tittade på - "Studenter" och "Bankklienter" - illustrerar begreppen naturliga nycklar och surrogatnycklar. I tabellen över bankkunder definierade vi en nyckel bestående av fälten "Nummer" och "Passerie", med hjälp av redan befintliga kolumner. Denna nyckel kallas naturlig vi gjorde inga ändringar eller tillägg för att bestämma den. När det gäller relationen "Studenter" gav inget enskilt fält eller kombination av fält oss unika. Detta tvingade oss att ange ytterligare ett studentkodfält. Denna nyckel kallas en surrogatnyckel, för vilken vi har lagt till ytterligare en tjänstkolumn i tabellen. Denna kolumn innehåller inga användbar information och tjänar endast till att identifiera poster.

Främmande nyckel och dataintegritet i databasen

Allt ovanstående leder oss till den främmande nyckeln och databasens integritet. Främmande nyckel är ett fält som refererar till den primära nyckeln för en främmande relation. I framstegstabellen är dessa kolumnerna "Student" och "Disciplin". Deras uppgifter hänvisar oss till externa tabeller. Det vill säga, "Student"-fältet i "Prestanda"-relationen är en främmande nyckel, och i "Student"-relationen är det primärnyckeln i databasen.

En viktig princip för att bygga databaser är deras integritet. Och en av dess regler är referensintegritet. Detta innebär att en främmande nyckel i en tabell inte kan referera till en icke-existerande primärnyckel i en annan relation. Du kan inte radera en post med kod 1000 - Ivan Ivanov från Studentrelationen om den refereras av en post från den akademiska prestationstabellen. I en korrekt konstruerad databas, när du försöker ta bort den, kommer du att få ett felmeddelande om att detta fält används.

Det finns andra grupper av integritetsregler, såväl som andra databasrestriktioner, som också förtjänar uppmärksamhet och bör beaktas av utvecklare.

Tidigare i den här boken påpekade vi vissa samband som finns mellan vissa fält av typiska tabeller. Snum-fältet i tabellen Kunder motsvarar till exempel snum-fältet i tabellen Säljare och Order-tabellen. Cnum-fältet i tabellen Kunder motsvarar också cnum-fältet i tabellen Order. Vi kallade den här typen av relationsreferensintegritet; och under diskussionen såg du hur det kan användas.

I det här kapitlet kommer du att utforska referensintegriteten mer i detalj och lära dig allt om de begränsningar du kan använda för att underhålla den. Du kommer också att se hur denna begränsning gäller när du använder DML-modifieringskommandon. Eftersom referensintegritet involverar förhållandet mellan fält eller grupper av fält, ofta i olika bord, kan den här åtgärden vara något mer komplex än andra begränsningar. Av denna anledning är det bra att vara helt bekant med det, även om du inte planerar att skapa tabeller. Dina modifieringskommandon kan göras mer effektiva genom att använda en referensintegritetsbegränsning (som med andra begränsningar, men en referensintegritetsbegränsning kan påverka andra tabeller än de som den är definierad på), och vissa frågefunktioner, såsom joins, är iterativt strukturerade i termer avser integritetsförhållanden (som betonats i kapitel 8).

UTLÄNDSK NYCKEL OCH FÖRÄLDERNYCKEL

När alla värden i ett tabellfält är representerade i ett fält i en annan tabell, säger vi att det första fältet refererar till det andra. Detta indikerar ett direkt samband mellan värdena i de två fälten. Till exempel har var och en av kunderna i tabellen Kunder ett snum-fält som pekar på säljaren som är tilldelad i tabellen Säljare. För varje beställning i ordertabellen finns det en och endast denna säljare och en och endast denna kund. Detta visas med snum- och cnum-fälten i ordertabellen.

När ett fält i en tabell refererar till ett annat kallas det en främmande nyckel; och fältet som det refererar till kallas föräldernyckeln. Så snum-fältet i tabellen Kunder är en främmande nyckel, och snum-fältet det refererar till i tabellen Leverantörer är den överordnade nyckeln.

Likaså är cnum- och snum-fälten i ordertabellen främmande nycklar som hänvisar till deras överordnade nycklar som är namngivna i tabellen Kunder och leverantörer. Namnen på den främmande nyckeln och föräldranyckeln behöver inte vara samma, det är bara en konvention som vi följer för att göra joinningen tydligare.

UTLANDSNYCKLAR MED FLERA KOLUMN

I verkligheten består en främmande nyckel inte nödvändigtvis av endast ett kön. Liksom en primärnyckel kan en främmande nyckel ha valfritt antal fält, som alla behandlas som en enda enhet. Den främmande nyckeln och föräldranyckeln den refererar till måste naturligtvis ha samma nummer och könstyp, och vara i samma ordning. Främmande nycklar som består av ett kön - de som vi enbart använde i våra standardtabeller är de vanligaste. För att hålla vår diskussion enkel kommer vi ofta att referera till en främmande nyckel som en enda kolumn. Detta är ingen slump. Om detta inte noteras kommer vem som helst att säga om ett fält som är en främmande nyckel att det också tillhör en grupp av fält som är en främmande nyckel.

Betydelsen av främmande nycklar och föräldranyckel

När ett fält är en främmande nyckel är det på något sätt relaterat till tabellen det refererar till. Vad du i huvudsak säger är "varje värde i detta fält (den främmande nyckeln) är direkt kopplat till ett värde i ett annat fält (föräldernyckeln)." Varje värde (varje rad) på en främmande nyckel måste otvetydigt referera till en och endast det värdet (raden) på den överordnade nyckeln. Om så är fallet, kommer faktiskt ditt system, som de säger, att vara i ett tillstånd av referensintegritet.

Du kan se detta med ett exempel. Den främmande nyckeln snum i tabellen Kunder har värdet 1001 för raderna Hoffman och Clemens. Låt oss anta att vi hade två rader i tabellen Leverantörer med fältvärdet snum = 1001. Hur vet vi vilken av de två leverantörerna som kunderna Hoffman och Clemens tilldelades? På samma sätt, om det inte finns några sådana rader i tabellen Leverantörer, kommer vi att sluta med att Hoffman och Clemens tilldelas en leverantör som inte finns!

Det är tydligt att varje värde i en främmande nyckel måste representeras en gång, och endast en gång, i den överordnade nyckeln. Faktum är att ett givet främmande nyckelvärde bara kan referera till ett överordnat nyckelvärde utan att det omvända är möjligt: d.v.s. vilket nummer som helst kan hänvisa till ett enskilt värde för den överordnade nyckeln. Du kan se detta i de typiska tabellerna i våra exempel. Både Hoffman och Clemens är tilldelade Peel, så båda deras värden för främmande nyckel är samma som samma överordnade nyckel, vilket är bra. Ett värde för en främmande nyckel måste endast referera till ett värde för en överordnad nyckel, men ett värde på en överordnad nyckel kan refereras av valfritt antal värden för främmande nyckel. Som en illustration visas främmande nyckelvärden från tabellen Kunder som matchar deras överordnade nyckel i tabellen Säljare i figur 19.1. För enkelhetens skull har vi inte tagit hänsyn till kön som inte är relevant för detta exempel.

BEGRÄNSNING AV UTLÄNDSK NYCKEL

SQL upprätthåller referensintegritet med begränsningen FOREIGN KEY. Även om FOREIGN KEY-begränsningen är en ny funktion i SQL, gör den den ännu inte universell. Dessutom är vissa av dess implementeringar mer komplexa än andra. Denna funktion bör begränsa de värden som du kan ange i din databas för att tvinga den främmande nyckeln och överordnade nyckeln att överensstämma med referensintegritet. En av åtgärderna för en främmande nyckel-begränsning är att kassera värden för fält som är begränsade som en främmande nyckel som inte redan är representerade i den överordnade nyckeln. Denna begränsning påverkar också din förmåga att ändra eller ta bort överordnade nyckelvärden (vi kommer att diskutera detta senare i det här kapitlet).

HUR KAN FÄLT REPRENTERAS SOM UTLÄNDSKA NYCKLAR

Du använder en FOREIGN KEY-begränsning i ett CREATE TABLE (eller ALTER TABLE)-kommando som innehåller fältet som du vill deklarera som en främmande nyckel. Du ger dem en överordnad nyckel som du kommer att referera till inom begränsningen FOREIGN KEY. Att placera denna begränsning i kommandot är densamma som för de andra begränsningarna som diskuterades i föregående kapitel. Figur 19.1: Utländsk nyckel för kundtabellen med överordnad nyckel

Liksom de flesta begränsningar kan det vara en tabell- eller kolumnbegränsning, i tabellform som tillåter flera fält att användas som en enda främmande nyckel.

UTLÄNDSK NYCKEL SOM BORDSBEGRÄNSNING

FOREIGN KEY-tabellens restriktionssyntax: FOREIGN KEY REFERENSER [ ] Den första kolumnlistan är en kommaseparerad lista med en eller flera tabellkolumner som kommer att skapas eller ändras av detta kommando. Pktable är tabellen som innehåller föräldranyckeln. Det kan vara en tabell som skapas eller modifieras av det aktuella kommandot. Den andra kolumnlistan är listan över kolumner som kommer att utgöra den överordnade nyckeln. De två kolumnlistorna måste vara kompatibla, dvs.

* De måste ha samma antal kolumner.

* I den här sekvensen måste den första, andra, tredje osv. kolumnen i kolumnlistan med främmande nyckel ha samma datatyper och storlekar som den första, andra, tredje, etc. kolumnen i kolumnlistan för överordnade nyckel .

Kolumnerna i listorna i båda kolumnerna ska inte ha samma namn, även om vi använde den här metoden i våra exempel för att göra sambandet tydligare.

Låt oss skapa en kundtabell med snum-fältet definierat som en främmande nyckel som refererar till tabellen Sellers: CREATE TABLE Kunder (cnum heltal INTE NULL PRIMÄRNYCKEL cname char(10), stad char(10), snum heltal, FOREIGN KEY (snum) REFERENSER Säljare (snum ); Tänk på att när du använder ALTER TABLE istället för CREATE TABLE, för att tillämpa FOREIGN KEY-begränsningen, måste värdena du anger i den främmande nyckeln och överordnade nyckeln vara i referensintegritetstillstånd, annars måste ALTER TABLE-kommandot kommer att avvisas - för bekvämlighets skull måste du först formulera strukturella principer, såsom referensintegritet, i ditt system, när det är möjligt, varje gång.

UTLÄNDSK NYCKEL SOM KOLUMNBEGRÄNSNING

Alternativet att begränsa en kolumn med en FOREIGN KEY-begränsning kallas också en REFERENCES-begränsning, eftersom den faktiskt inte innehåller orden FOREIGN KEY, utan helt enkelt använder ordet REFERENCES, följt av den överordnade nyckeln, så här: CREATE TABLE Customers ( cnum heltal INTE NULL PRIMÄRNYCKEL, cname char(10), city char(10), snum heltal REFERENSER Säljare (snum)); Ovanstående definierar Customers.snum som en främmande nyckel vars överordnade nyckel är Salespeople.snum. Detta motsvarar en tabellbegränsning så här: UTLÄNDLIG KEY (snum) REGERENSER Säljare (snum)

Genom att använda en FOREIGN KEY-begränsning i en tabell eller kolumn kan du utelämna den överordnade nyckelns kolumnlista om den överordnade nyckeln har en PRIMARY KEY-begränsning. Naturligtvis, i fallet med nycklar med många fält, måste ordningen på kolumnerna i främmande och primärnycklar matcha, och i alla fall gäller principen om kompatibilitet mellan de två nycklarna fortfarande. Om vi till exempel placerade en PRIMARY KEY-begränsning i snum-fältet i tabellen Sales, skulle vi kunna använda den som en främmande nyckel i tabellen Customers (liknande föregående exempel) med detta kommando: CREATE TABLE Customers (cnum integer NOT NULL) PRIMARY KEY, cname char(10) , city char(10), snum heltal REFERENSER Säljare); Den här funktionen byggdes in i språket för att uppmuntra dig att använda primärnycklar som överordnade nycklar.

HUR REFERENSINTEGRITET BEGRÄNSAR VÄRDEN PÅ EN FÖRÄLDERNYCKEL

Att upprätthålla referensintegritet kräver vissa begränsningar för de värden som kan representeras i fält som deklareras som en främmande nyckel och en överordnad nyckel. Den överordnade nyckeln måste vara strukturerad för att säkerställa att varje främmande nyckelvärde motsvarar en angiven rad. Det betyder att den (nyckeln) måste vara unik och inte innehålla några tomma värden (NULL). Detta är inte tillräckligt för föräldranyckeln om samma krav är uppfyllt som vid deklaration av en främmande nyckel. SQL måste vara säker på att dubbla värden eller tomma värden(NULL) angavs inte i den överordnade nyckeln. Därför måste du se till att alla fält som används som överordnade nycklar har antingen en PRIMARY KEY-restriktion eller en UNIQUE constraint, som NOT NULL-begränsningen.

PRIMÄRNYCKEL SOM EN UNIK UTLÄNDSK NYCKEL

Att länka dina främmande nycklar till enbart primärnycklar, som vi gjorde i standardtabeller, är en bra strategi. När du använder främmande nycklar associerar du dem inte bara med de överordnade nycklar de refererar till; du associerar dem med en specifik tabellrad där den överordnade nyckeln kommer att hittas. Själva överordnade nyckeln ger ingen information som inte redan finns i den främmande nyckeln. Innebörden av till exempel sex snum som en främmande nyckel i tabellen Kunder är relationen den tillhandahåller, inte till värdet av sex snum som den refererar till, utan till annan information i försäljningstabellen, såsom namnen på säljare, deras platser och så vidare. En främmande nyckel är inte bara ett förhållande mellan två identiska värden; detta är en relation, med dessa två värden, mellan två rader i tabellen som anges i frågan. Detta snum-fält kan användas för att associera all information i en rad från tabellen Kunder med en referensrad från tabellen Säljare - till exempel för att ta reda på om de bor i samma stad, vem som har ett längre namn, om säljaren har alla andra kunder förutom denna kunds kunder, och så vidare. Eftersom syftet med en primärnyckel är att identifiera unikheten hos en rad, är det ett mer logiskt och mindre tvetydigt val för en främmande nyckel. För alla främmande nyckel som använder en unik nyckel som sin överordnade nyckel, måste du skapa en främmande nyckel som använder samma tabells primärnyckel för samma effekt. En främmande nyckel som inte har något annat syfte än att länka rader liknar en primärnyckel som endast används för att identifiera rader, och är

bra botemedel

håll din databasstruktur tydlig och enkel, och därför mindre komplex.

UTLÄNDSKA NYCKELBEGRÄNSNINGAR

Låt oss ange att alla främmande nycklar som skapats i våra exempeltabeller deklareras och upprätthålls med begränsningar för främmande nyckel, enligt följande: CREATE TABLE Säljare (snum heltal INTE NULL PRIMÄRNYCKEL, sname char(10) NOT NULL, city char(10) , comm decimal );

CREATE TABLE-kunder (cnum heltal INTE NULL PRIMÄRNYCKEL, cname char(10) NOT NULL, city char(10), rating heltal, snum heltal, FOREIGN KEY (snum) REFERENSER Säljare, UNIKA (cnum, snum) ; SKAPA TABELL Orders ( cnum heltal INTE NULL PRIMÄRNYCKEL, amt decimal, odate datum INTE NULL, cnum heltal NOT NULL snum heltal INTE NULL UTLÄNDSK NYCKEL (cnum, snum) REFERENSER KUNDER (cnum, snum);

INKLUSIVE TABELLBESKRIVNINGAR

Ur synvinkel att upprätthålla databasintegritet är interna avbrott (eller undantag) naturligtvis oönskade. Om du tillåter dem och samtidigt vill bibehålla integriteten för din databas, kan du deklarera snum- och cnum-fälten i tabellen Order som oberoende främmande nycklar för dessa fält i tabellen Leverantörer respektive Kunder. Det är faktiskt inte nödvändigt att använda sex snum i Order-tabellen som vi gjorde, även om det är användbart att göra det för variation. Cnum-fältet som länkar varje kundorder i Kundtabellen, Ordertabellen och Kundtabellen måste alltid delas för att hitta rätt snum-fält för den ordern (utan att tillåta några undantag). Detta innebär att vi registrerar en bit information - vilken kund som är tilldelad vilken leverantör - två gånger, och ytterligare arbete kommer att behöva göras för att säkerställa att båda versionerna är konsekventa. Om vi inte har en begränsning för främmande nyckel som nämnts ovan kommer denna situation att vara särskilt problematisk eftersom varje beställning måste kontrolleras manuellt (tillsammans med frågan) för att säkerställa att motsvarande säljare krediterade varje motsvarande försäljning. Att ha denna typ av informationsredundans i din databas kallas denormalisering, vilket är oönskat i en idealisk relationsdatabas, även om det i praktiken kan lösas. Demoralisering kan göra att vissa frågor körs snabbare, eftersom en fråga på en tabell alltid är mycket snabbare än en fråga på en koppling.

EFFEKTEN AV BEGRÄNSNINGAR

Hur påverkar sådana begränsningar din förmåga och oförmåga att använda DML-modifieringskommandon? För fält som definieras som främmande nycklar är svaret ganska enkelt: alla värden du lägger i dessa fält med ett INSERT- eller UPDATE-kommando måste redan finnas i deras överordnade nycklar. Du kan dock ange NULL-värden i dessa fält NULL-värdenär inte tillåtna i överordnade nycklar om de har en NOT NULL-begränsning. Du kan RADERA alla rader med främmande nycklar utan att använda överordnade nycklar alls.

Eftersom frågan om att ändra överordnade nyckelvärden tas upp, är svaret, enligt definitionen av ANSI, ännu enklare, men kanske något mer begränsat: något värde för överordnad nyckel som refereras av ett främmande nyckelvärde kan inte tas bort eller ändras. Det betyder till exempel att du inte kan ta bort en kund från tabellen Kunder medan den fortfarande har order i tabellen Order. Beroende på hur du använder dessa tabeller kan detta vara antingen önskvärt eller krångligt. Detta är dock säkerligen bättre än att ha ett system som låter dig ta bort en kund med aktuella beställningar och lämna ordertabellen med hänvisning till icke-existerande kunder. Poängen med detta begränsningssystem är att skaparen av beställningstabellen, med hjälp av Kundtabellen och Säljartabellen som överordnade nycklar, kan införa betydande begränsningar för åtgärder i dessa tabeller. Av denna anledning kommer du inte att kunna använda en tabell som du inte kontrollerar (det vill säga du har inte skapat den och du är inte dess ägare) förrän ägaren (skaparen) av den tabellen specifikt ger dig rätten att göra så (som förklaras i kapitel 22). Det finns några andra möjliga åtgärder

ändringar av föräldranyckeln som inte är en del av ANSI men som finns i vissa kommersiella program. Om du vill ändra eller ta bort det aktuella referensvärdet för en överordnad nyckel, finns det i huvudsak tre möjligheter:

Du kan begränsa, eller förbjuda, ändringar (på ANSI-sätt) genom att ange att ändringar av den överordnade nyckeln är begränsade.

Du kan göra en ändring i föräldranyckeln och därigenom göra ändringar i den främmande nyckeln automatiskt, vilket kallas en kaskadändring.

Du kan göra en ändring av den överordnade nyckeln och ställa in den främmande nyckeln till NULL, automatiskt (förutsatt att NULLS är tillåtet i den främmande nyckeln), vilket kallas en ändring av en främmande nyckel med null. Även inom dessa tre kategorier kanske du inte vill hantera alla modifieringskommandon på detta sätt. INSERT är naturligtvis irrelevant. Det lägger de nya överordnade nyckelvärdena i tabellen så att inget av dessa värden kan anropas till. Men du kanske vill tillåta att ändringar kaskadkopplas utan raderingar och vice versa. En bättre situation kan vara en som låter dig definiera någon av de tre kategorierna, oberoende av kommandona UPDATE och DELETE. Vi kommer därför att hänvisa till uppdateringseffekterna och borttagningseffekterna, som avgör vad som händer om du utfärdar ett UPDATE- eller DELETE-kommando på en överordnad nyckel. Dessa effekter vi pratade om kallas: BEGRÄNSADE ändringar, CASCADES-förändringar och NULL-förändringar.

För experimentets fullständighets skull, låt oss anta att du har en anledning att ändra snum-fältet i tabellen Leverantörer i det fall då vår Leverantörstabell ändrar partitioner. (Att byta primärnycklar är vanligtvis inget vi rekommenderar att man gör i praktiken. Det är bara ytterligare en anledning till att befintliga primärnycklar inte vet hur man gör något annat än att fungera som primärnycklar: de bör inte ändras.) När du byter handlare nummer vill du att alla dess kunder ska sparas. Men om den här säljaren lämnar sitt företag eller företag kanske du inte vill ta bort hans kunder samtidigt som du tar bort honom från databasen. Istället vill du se till att kunderna tilldelas någon annan. För att göra detta måste du ange UPDATE med en kaskadeffekt och DELETE med en begränsad effekt.

SKAPA TABELL Kunder (cnum heltal INTE NULL PRIMÄRNYCKEL, cname char(10) NOT NULL, city char(10), rating heltal, snum heltal REFERENSER Säljare, UPPDATERING AV SÄLJARE CASCADES, DELETE OF SÄLARE BEGRÄNSAT); Om du nu försöker ta bort Peel från leverantörstabellen, kommer kommandot inte att vara giltigt förrän du ändrar värdet på sex snum för Hoffman och Clemens kunder för en annan tilldelad leverantör. Å andra sidan kan du ändra sex snum-värdet för Peel till 1009, och Hoffman och Clemens kommer också att ändras automatiskt.

Den tredje effekten är Empty (NULL) ändringar. Det händer att när säljare lämnar ett företag så överförs inte deras nuvarande beställningar till en annan säljare. Å andra sidan vill du avbryta alla beställningar automatiskt för kunder vars konton du raderar. Genom att ändra säljarens eller kundens nummer kan du enkelt överföra dem till honom. Exemplet nedan visar hur du kan skapa en beställningstabell med dessa effekter.

Som nämnts tidigare kan en FOREIGN KEY-begränsning representera denna privata tabell som en överordnad nyckeltabell. Långt ifrån enkel, den här funktionen kan komma väl till pass. Låt oss anta att vi har en tabell med anställda med ett chefsfält. Detta fält innehåller numren på varje anställd, av vilka några också är administratörer. Men eftersom varje administratör samtidigt förblir anställd kommer han naturligtvis också att finnas representerad i denna tabell. Låt oss skapa en tabell där anställds nummer (en kolumn som heter empno) deklareras som primärnyckel, och administratören, som en främmande nyckel, kommer att referera till den: CREATE TABLE Anställda (empno heltal INTE NULL PRIMARY KEY, namn char(10) NOT NULL UNIOUE , chef heltal REFERENSER Anställda); (Eftersom den främmande nyckeln är den refererade primärnyckeln i tabellen, kan kolumnlistan exkluderas.) Det finns innehållet i denna tabell: EMPNO NAME MANAGER _____ ________ _______ 1003 Terrence 2007 2007 Atali NULL 1688 McKenna 1003 2002 Collier 2002 Collier kan se, var och en av dessa (men inte Atali), hänvisar till en annan anställd i tabellen som sin administratör. Atali, som har det högsta numret i tabellen, måste ha sitt värde satt till NULL. Detta ger en annan princip om referensintegritet. En främmande nyckel som refererar tillbaka till en privat tabell måste tillåta värden = NULL. Om så inte är fallet, hur skulle du infoga den första raden? Även om denna första rad hänvisar till sig själv, måste värdet på den överordnade nyckeln redan vara inställt när det främmande nyckelvärdet skrivs in. Denna princip kommer att gälla även om den främmande nyckeln refererar tillbaka till den privata tabellen inte direkt utan genom en referens till en annan tabell, som sedan hänvisar tillbaka till den främmande nyckeltabellen. Anta till exempel att vår försäljningstabell har ett extra fält som refererar till tabellen Kunder, så att varje tabell refererar till den andra, som visas i följande CREATE TABLE-sats: CREATE TABLE Säljare (snum heltal INTE NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10), comm declmal, cnum heltal REFERENSER Kunder); I syfte att korsreferens tillåter SQL faktiskt detta, men ingendera tabellen kommer att vara användbar medan båda håller på att skapas. Å andra sidan, om de två tabellerna skapas av olika användare, blir problemet ännu svårare. Korsreferenser kan vara ett användbart verktyg, men det är inte utan tvetydighet och faror. Det tidigare exemplet är till exempel inte helt användbart eftersom det begränsar säljaren till en enskild kund, och det är inte nödvändigt att använda en korsreferens för att uppnå detta. Vi rekommenderar att du är försiktig i användningen och analyserar hur dina program hanterar effekterna av ändring och radering, såväl som processerna för privilegier och interaktiv frågebehandling, innan du skapar ett korsreferensintegritetssystem. (Privilegier och interaktiv förfrågningsbehandling kommer att diskuteras i kapitel 22 respektive 1.)

SKAPA TABELL Beställningar (onum heltal INTE NULL PRIMÄRNYCKEL, amt decimal, odate date NOT NULL cnum heltal NOT NULL REFERENSER Kunder snum heltal REFERENSER Säljare, UPPDATERING AV Kunder CASCADES, DELETE OF Customers CASCADES, speeopLEES CASCADES, speeopLEES OF Sales, DleeopLEES; Naturligtvis, i ett DELETE-kommando med effekten av en Empty-ändring på leverantörstabellen, måste NOT NULL-begränsningen tas bort från snum-fältet.

Du har nu ganska bra kontroll över referensintegriteten. Grundidén är att alla främmande nyckelvärden refererar till den angivna överordnade nyckelraden. Detta innebär att varje främmande nyckelvärde måste representeras en gång, och endast en gång, i den överordnade nyckeln. Närhelst ett värde placeras i en främmande nyckel, kontrolleras den överordnade nyckeln för att säkerställa att dess värde representeras; annars kommer kommandot att avvisas. Den överordnade nyckeln måste ha en PRIMARY KEY eller UNIQUE begränsning för att säkerställa att värdet inte representeras mer än en gång. Ett försök att ändra ett värde på en överordnad nyckel som för närvarande representeras i en främmande nyckel kommer att avvisas helt och hållet. Ditt system kan dock ge dig valet att få värdet på den främmande nyckeln satt till NULL eller att få det nya värdet på föräldranyckeln, och ange vilket som kan erhållas oberoende för kommandona UPDATE och DELETE. Detta avslutar vår diskussion om kommandot CREATE TABLE. Därefter kommer vi att presentera en annan typ av kommando - CREATE. I kapitel 20 kommer du att lära dig hur du representerar dataobjekt som ser ut och fungerar som en tabell men som faktiskt är resultatet av frågor. Vissa begränsningsfunktioner kan också utföras av vyer, så att du bättre kommer att kunna bedöma ditt behov av begränsningar efter att du har läst de kommande tre kapitlen.

ARBETA MED SQL

1. Skapa en tabell med namnet Cityorders. Den bör innehålla samma onum-, amt- och snum-fält som ordertabellen, och samma cnum- och stad-fält som Customers-tabellen, så att varje kunds beställning läggs in i tabellen tillsammans med dess stad.

Onum-fältet kommer att vara den primära nyckeln för Cityorders. Alla våningar i Cityorders måste ha restriktioner jämfört med tabellerna Kunder och Order. Det är möjligt att föräldernycklarna i dessa tabeller redan har lämpliga begränsningar.

2. Låt oss komplicera problemet. Omdefiniera ordertabellen enligt följande: lägg till en ny kolumn som heter prev som kommer att identifieras för varje order, onum-fältet för den tidigare ordern för den aktuella kunden.

I det här ämnet, med två tabeller som exempel, definieras de grundläggande begreppen för relationsdatabaser, nämligen:

primärnyckel;
främmande nyckel;
enkel och sammansatt nyckel;
attityd, typer av relationer;
konstgjorda och naturliga nycklar;
huvud (master) och underordnade (detalj) tabeller.

Indata

Låt en databas över företagsanställda ges, som består av två tabeller. Den första tabellen innehåller data om medarbetaren. Den andra tabellen innehåller information om den anställdes lön.

Tabellerna har följande struktur.

"Arbetstagare". Innehåller personaldata "Lön". Innehåller information om anställdas löner.

Fråga/svar

1. Vad är en primärnyckel i en databastabell? Vad används primärnycklar till?

Vid arbete med tabeller i relationsdatabaser är det önskvärt (nödvändigt) att varje tabell har en s.k. primärnyckel.

Primär nyckelär ett fält som används för att säkerställa unika data i tabellen. Detta innebär att värdet (informationen) i primärnyckelfältet i varje rad (post) i tabellen kan vara unikt.

Unikitet är nödvändigt för att undvika oklarheter när det inte är känt vilken tabellpost som kan nås om det finns dubbla poster i tabellen (två poster har samma värden i alla fält i tabellen).

Exempel. För tabellen "Anställd" kan du ange ytterligare ett fält, som kommer att vara den primära nyckeln. Men fältet "Personalnummer" (attribut) säkerställer också unikhet. Eftersom det teoretiskt sett inte kan finnas två identiska personalnummer. I praktiken kan det finnas fall där samma personalnummer anges av misstag och värdena för alla tabellfält är desamma. Som ett resultat kommer två identiska poster att visas i tabellen. För att undvika ett sådant fel är det bättre att skapa ett extra räknarfält i tabellen, vilket säkerställer unikhet.

Du kan också ange ett extra fält för tabellen "Lön", som kommer att vara den primära nyckeln.

2. Vad är ett samband mellan tabeller? Exempel

Tabeller i relationsdatamodellen kan ha relationer med varandra. Sådana kopplingar kallas relationer. För tabellerna "Anställd" och "Lön" kan du upprätta en anslutning genom att använda fältet "Personalnummer".

Exempel. Låt oss analysera tabellerna "Anställd" och "Lön". I dessa tabeller kan du upprätta en relation mellan tabeller baserat på fältet Personalnummer. Det vill säga att kopplingen mellan tabeller sker på basis av fältet (attributet) "Personalnummer".

Det betyder följande. Om du behöver hitta upplupna löner i tabellen "Lön" för den anställde Ivanov I.I., måste du utföra följande steg:

hitta personalnumret för den anställde Ivanov I.I. i tabellen "Anställd". Värdet på personalnumret är 7585;
i tabellen "Lön", hitta alla värden som är lika med 7585 (personalnummer);
välj i tabellen "Lön" alla värden i fältet "Upplupna" som motsvarar personalnummer 7585.

Ris. 1. Illustration av förhållandet mellan tabeller. Personalnummer 2145 i tabellen "Anställd" visas i tabellen "Lön"

Ris. 2. Koppling (relation) mellan tabellfält

3. Vad är en främmande nyckel? Exempel

Begreppet "främmande nyckel" är viktigt när man överväger relaterade tabeller.

Främmande nyckel– är ett eller flera fält (attribut) som är primära i en annan tabell och vars värde ersätts av värdena för primärnyckeln i en annan tabell.

Exempel. Låt det finnas ett samband mellan tabellerna "Anställd" och "Lön" i fältet "Personalnummer". I det här fallet kan fältet "Personalnummer" i tabellen "Anställd" vara primärnyckeln och fältet "Personalnummer" i tabellen "Lön" kan vara en främmande nyckel. Detta innebär att värdena i fältet "Personalnummer" i tabellen "Lön" ersätts med värdena i fältet "Personalnummer" i tabellen "Anställd".

4. Vad är en rekursiv främmande nyckel?

Rekursiv främmande nyckelär en främmande nyckel som refererar till samma tabell som den tillhör. I detta fall är fältet (attributet) som motsvarar den främmande nyckeln nyckeln för samma relation (länk).

5. Kan primära och främmande nycklar vara enkla eller sammansatta (komplexa)?

Primära, sekundära och främmande nycklar kan vara antingen enkla eller sammansatta (komplexa). Enkla nycklar– det här är nycklar som bara innehåller ett fält (ett attribut). Sammansatt(komplexa) nycklar är nycklar som innehåller flera fält (attribut).

6. Vad är skillnaden mellan en konstgjord och en naturlig nyckel? Exempel

Naturlig nyckel ger unikhet från själva essensen av ämnesområdet. Det finns fall då värdena för poster i vissa fält (fält) i tabellen är unika. Detta fält kan vara en naturlig nyckel.

Konstgjord nyckel införs ytterligare för att säkerställa unika värden. Oftast är den konstgjorda nyckeln ett fält av typen räknare. I ett sådant fält, när man lägger till ny post(rader) i tabellen ökas räknarvärdet med 1 (eller annat värde). Om en post raderas från tabellen reduceras maxvärdet för radräknaren inte längre utan förblir som den är. Vanligtvis övervakas allt detta av ett databashanteringssystem.

Exempel. I tabellen "Anställd" är den naturliga nyckeln fältet (attributet) "Personalnummer". Fältet ”Personalnummer” är unikt i sig, eftersom det inte kan finnas två anställda med samma personalnummer.

I tabellen "Lön" kan värdet i alla fyra fälten upprepas av misstag. Därför är det lämpligt att lägga till ett extra räknarfält här, vilket kommer att vara en konstgjord nyckel. I det här fallet kan tabellen "Lön" med ett extra fält se ut ungefär så här:

där "Number"-fältet är en konstgjord nyckel som säkerställer unikhet.

7. Vilka är de olika sätten att välja en primärnyckel?

Det finns tre sätt att välja en primärnyckel:

använd inkrementfältet (räknarfältet) som en konstgjord nyckel;
välj ett fält från data som kan ge unikhet;
välj flera fält från data som kan ge unikhet. I det här fallet kommer nyckeln också att kallas komplex (komposit).

8. Vad betyder termerna "huvudtabell" (master) och "underordnad tabell" (detalj)?

Om det finns en relation mellan tabeller, kan en av dem vara den huvudsakliga (master) och den andra underordnade (detalj). Huvudtabellen visar alla poster som passar in i den. Slavtabellen visar endast de poster som matchar värdet på huvudtabellnyckeln, som för närvarande är aktiv (aktuell). Om den aktuella posten för huvudtabellen ändras, ändras uppsättningen av tillgängliga poster för slavtabellen.

Exempel. Om vi betraktar tabellerna "Anställd" och "Lön", så är tabellen "Anställd" den huvudsakliga, och tabellen "Lön" är en underordnad.

9. Vilka typer av relationer (länkar) finns mellan tabeller?

Det finns fyra huvudtyper av relationer mellan tabeller:

"en till en". I detta fall motsvarar varje post i en tabell endast en post i en annan tabell;
"en till många". Detta är när en post i huvudtabellen (master) motsvarar flera poster i den underordnade tabellen (detalj). Det vill säga att varje post som är den primära nyckeln för en tabell motsvarar flera poster i den relaterade tabellen;
"många till en". Detta är när flera poster i huvudtabellen motsvarar en post i den underordnade tabellen;
"många till många". Detta är när det finns flera relaterade poster i båda tabellerna.

Exempel. Om vi tar hänsyn till förhållandet mellan "Anställd" och "Lön"-tabellerna, så är detta förhållande av typen "en-till-många". Tabellen "Anställd" är den viktigaste. Tabellen "Lön" är en underordnad tabell.

GÄLLER FÖR: SQL Server(sedan 2016)Azure SQL DatabaseAzure SQL Data WarehouseParallel Data Warehouse

Primära och främmande nycklar är två typer av begränsningar som kan användas för att säkerställa dataintegritet i SQL-tabeller Server. Dessa är viktiga databasobjekt.

Det här ämnet behandlas i följande avsnitt.

Primära nyckelbegränsningar

Utländska nyckelbegränsningar

Relaterade uppgifter

Vanligtvis har en tabell en kolumn eller en kombination av kolumner som innehåller värden som unikt identifierar varje rad i tabellen. Denna kolumn eller kolumner kallas den primära nyckeln (PK) i tabellen och ger integritet till tabellens entitet. Primära nyckelbegränsningar definieras ofta på en identitetskolumn eftersom de säkerställer att data är unik.

När du ställer in en primärnyckelrestriktion på en tabell i databasmotorn säkerställer databasmotorn att data är unik av automatiskt skapande ett unikt index på primärnyckelkolumnerna. Detta index ger också snabb åtkomst till data när du använder en primärnyckel i frågor. Om en primärnyckelbegränsning är definierad på mer än en kolumn kan värden dupliceras inom en enda kolumn, men varje kombination av värden från alla kolumner i definitionen av primärnyckelbegränsningen måste vara unik.

Som visas i följande figur, kolumnerna Produkt-ID Och Leverantörs-ID i tabellen Inköp.Produktleverantör bildar en sammansatt primärnyckelrestriktion för en given tabell. Detta säkerställer att varje rad i tabellen Produktleverantör har en unik kombination av betydelser Produkt-ID Och Leverantörs-ID. Detta förhindrar att dubbletter av rader infogas.

En tabell kan bara ha en primärnyckelrestriktion.

Primärnyckeln får inte ha fler än 16 kolumner och den totala nyckellängden får inte överstiga 900 byte.

Ett index som bildas av en primärnyckelbegränsning kan inte orsaka att antalet index i tabellen överskrider gränsen på 999 icke-klustrade index och 1 klustrade index.

Om en primärnyckelrestriktion inte anger om indexet är klustrat eller icke-klustrat, skapas ett klustrat index om ett sådant inte finns i tabellen.

Alla kolumner med en primärnyckelbegränsning måste definieras som icke-nullbara. Om nullbarhet inte anges, är alla kolumner med en primärnyckelbegränsning inställda på icke-nullbara.

Om primärnyckeln är definierad i en kolumn av en CLR användardefinierad datatyp, måste implementeringen av den typen stödja binär sortering.

En främmande nyckel (FK) är en kolumn eller kombination av kolumner som används för att tvinga fram en relation mellan data i två tabeller för att styra data som kan lagras i tabellen med främmande nyckel. Om en eller flera kolumner som innehåller primärnyckeln för en tabell refereras i en eller flera kolumner i en annan tabell, skapar en främmande nyckellänk en relation mellan de två tabellerna. Denna kolumn blir en främmande nyckel i den andra tabellen.

Till exempel bord Sales.SalesOrderHeader kopplad till tabell Säljare. Säljare använder en främmande nyckel eftersom det finns ett logiskt samband mellan försäljningsorder och försäljningschefer. Kolumn Säljare-ID i tabellen Sales.SalesOrderHeader matchar primärnyckelkolumnen i tabellen Försäljare. Kolumn Säljare-ID i tabellen Sales.SalesOrderHeaderär en främmande nyckel till bordet Försäljare. Genom att upprätta denna relation med hjälp av en främmande nyckel kan värdet för Säljare-ID kan inte infogas i tabellen SalesOrderHeader, om det för närvarande inte finns i tabellen Försäljare.

Det maximala antalet tabeller och kolumner som en tabell kan referera till som främmande nycklar (utgående referenser) är 253. SQL Server 2016 ökar gränsen för antalet andra tabeller och kolumner som kan referera till kolumner i samma tabell (inkommande referenser), fr.o.m. 253 upp till 10 000 (kräver en kompatibilitetsnivå på minst 130.) Förstoring är föremål för följande begränsningar:

Överskridande av 253 främmande nyckelreferenser stöds endast för DML DELETE-operationer. UPDATE- och MERGE-operationer stöds inte.

Att överskrida 253 främmande nyckelreferenser är för närvarande inte tillgängligt för kolumnlagerindex, minnesoptimerade tabeller, Stretch-databas eller partitionerade tabeller med främmande nyckel.

Index i främmande nyckelbegränsningar

Till skillnad från primärnyckelbegränsningar skapas inte ett motsvarande index automatiskt när du skapar en främmande nyckelbegränsning. Det är dock ofta nödvändigt att skapa ett index på en främmande nyckel manuellt av följande skäl:

Kolumner för främmande nyckel används ofta i kopplingskriterier när de används tillsammans för att söka efter data från relaterade tabeller. Detta implementeras genom att mappa en kolumn eller kolumner i en främmande nyckelrestriktion i en tabell till en eller flera primära eller unika nyckelkolumner i en annan tabell. Ett index gör att databasmotorn snabbt kan hitta relaterade data i en främmande nyckeltabell. Det är dock inte obligatoriskt att skapa ett index. Data från två relaterade tabeller kan kombineras även om det inte finns några begränsningar för primärnyckel eller främmande nyckel definierade mellan tabellerna, men en främmande nyckelrelation mellan två tabeller indikerar att de två tabellerna är optimerade för att användas tillsammans i en fråga som använder nycklarna som kriterier.

Begränsningar för främmande nyckel letar efter ändringar av begränsningar för primärnyckel i relaterade tabeller.

Referensintegritet

Huvudsyftet med en främmande nyckel begränsning är att kontrollera data som kan lagras i tabellen med främmande nyckel, men begränsningen styr också ändringar av data i primärnyckeltabellen. Till exempel om du tar bort raden för försäljningschef från tabellen Säljare. Säljare, vars ID används i försäljningsorder i tabellen Sales.SalesOrderHeader, kommer de två tabellernas referensintegritet att kränkas. Fjärrchefsförsäljningsordrar i tabell SalesOrderHeader blir ogiltigt utan koppling till data i tabellen Försäljare.

En främmande nyckel-begränsning förhindrar att denna situation uppstår. En begränsning upprätthåller referensintegritet på följande sätt: den förhindrar ändringar av data i primärnyckeltabellen om sådana ändringar skulle ogiltigförklara en länk i främmande nyckeltabellen. Om du försöker ta bort en rad i en primärnyckeltabell eller ändra värdet på den nyckeln, om du upptäcker att det borttagna eller ändrade primärnyckelvärdet har ett motsvarande värde i en främmande nyckelrestriktion i en annan tabell, kommer åtgärden att misslyckas. För att framgångsrikt kunna modifiera eller ta bort en rad med en främmande nyckel-begränsning måste du först ta bort främmande nyckeldata i främmande nyckeltabellen eller ändra data i främmande nyckeltabellen som relaterar den främmande nyckeln till data i en annan primärnyckel.

Kaskadande referensintegritet

Genom att använda överlappande referensintegritetsbegränsningar kan du definiera de åtgärder som databasmotorn ska vidta när en användare försöker ta bort eller uppdatera en nyckel som pekas på av främmande nycklar som fortfarande finns. Följande kaskadåtgärder kan definieras.

INGEN ÅTGÄRD
Databasmotorn genererar ett fel och återställer sedan borttagnings- eller uppdateringsåtgärden på raden i den överordnade tabellen.

KASKAD
Motsvarande rader uppdateras eller tas bort från referenstabellen if given rad uppdateras eller tas bort från den överordnade tabellen. CASCADE-värdet kan inte anges om kolumnen är av typen tidsstämpelär en del av en främmande nyckel eller referensnyckel. Åtgärden ON DELETE CASCADE kan inte specificeras i en tabell som har en INSTEAD OF DELETE-utlösare definierad. ON UPDATE CASCADE-satsen kan inte specificeras i tabeller som har INSTEAD OF UPDATE-utlösare definierade.

SÄTT NULL
Alla värden som utgör en främmande nyckel sätts till NULL när motsvarande rad i den överordnade tabellen uppdateras eller tas bort. För att uppfylla denna begränsning måste kolumner för främmande nyckel vara nullbara. Kan inte ställas in på tabeller som har INSTEAD OF UPDATE-utlösare definierade.

STÄLL IN STANDARD
Alla värden som utgör en främmande nyckel är inställda på sina standardvärden när motsvarande rad i den överordnade tabellen raderas eller uppdateras. För att uppfylla denna begränsning måste alla kolumner för främmande nyckel ha standarddefinitioner. Om en kolumn är nullbar och standardvärdet inte är explicit definierat, blir standardvärdet för kolumnen NULL. Kan inte ställas in på tabeller som har INSTEAD OF UPDATE-utlösare definierade.

Nyckelorden CASCADE, SET NULL, SET DEFAULT och NO ACTION kan kombineras i tabeller som har ömsesidiga referensrelationer. Om databasmotorn stöter på nyckelordet NO ACTION kommer den att stoppa och återställa de associerade CASCADE-, SET NULL- och SET DEFAULT-operationerna. Om en DELETE-sats innehåller en kombination av nyckelorden CASCADE, SET NULL, SET DEFAULT och NO ACTION, utförs alla CASCADE-, SET NULL- och SET DEFAULT-operationer innan databasmotorn söker efter NO ACTION-operationen.

Utlösare och överlappande referensåtgärder

Kaskadreferensåtgärder aktiveras EFTER UPPDATERING eller EFTER DELETE-utlösare enligt följande:

Alla överlappande referensåtgärder som direkt orsakas av de ursprungliga DELETE- eller UPDATE-satserna exekveras först.

Om det finns några AFTER-utlösare definierade i tabeller som har ändrats, aktiveras dessa utlösare efter att alla överlappande åtgärder har slutförts. Dessa utlöser eld i omvänd ordning av kaskadåtgärder. Om flera utlösare är definierade för en enskild tabell, aktiveras de i slumpmässig ordning om inte tabellens första och sista utlösare är valda. Denna ordning bestäms av proceduren.

Om överlappande åtgärdssekvenser kommer från en tabell som var det omedelbara målet för en DELETE- eller UPDATE-åtgärd, bestäms inte ordningen i vilken åtgärdssekvenserna utlöser. En handlingssekvens avfyrar dock alltid alla sina triggers innan nästa gör det.

En EFTER-utlösare på en tabell som var det omedelbara målet för en DELETE- eller UPDATE-åtgärd aktiveras oavsett om några rader har ändrats. I det här fallet påverkas inga andra tabeller av överlappande.

Om en av de tidigare triggarna utför DELETE- eller UPDATE-operationer på andra tabeller, kan dessa operationer utlösa sin egen sekvens av kaskadåtgärder. Dessa sekundära åtgärdssekvenser bearbetas för varje DELETE- eller UPDATE-operation efter att alla primära åtgärdssekvensutlösare har slutförts. Denna process kan upprepas rekursivt för efterföljande DELETE- eller UPDATE-operationer.

Om du utför CREATE, ALTER, DELETE eller andra DDL-operationer inuti triggers kan det leda till att DDL-triggers utlöses. Detta kan leda till ytterligare DELETE- eller UPDATE-operationer, som startar ytterligare sekvenser av kaskadåtgärder och aktiverar deras utlösare.

Om ett fel inträffar i någon speciell sekvens av kaskadreferensåtgärder kommer inga AFTER-utlösare att aktiveras på den sekvensen, och DELETE- eller UPDATE-operationer som genereras av den sekvensen kommer att återställas.

En tabell som har en INSTEAD OF-utlösare definierad kan också ha en REFERENCES-sats som anger en specifik kaskadåtgärd. En AFTER-utlösare på en överlappande åtgärds måltabell kan emellertid utfärda en INSERT-, UPDATE- eller DELETE-sats på en annan tabell eller vy, vilket avfyrar en INSTEAD OF-utlösare på det objektet.

Följande tabell listar vanliga uppgifter associerade med primärnyckel och främmande nyckel.

Och så, tyst, närmade vi oss ett mycket viktigt ämne - primära och främmande nycklar. Om de förra används av nästan alla, ignoreras de senare på något sätt. Men förgäves. Främmande nycklar är inte ett problem, det är de verklig hjälp i dataintegritet.

1.2.5. Primär nyckel

Vi har redan pratat mycket om nyckelfält, men vi har aldrig använt dem. Det mest intressanta är att allt fungerade. Detta är en fördel, eller kanske en nackdel, med databasen. Microsoft SQL Server och MS Access. Detta trick kommer inte att fungera i Paradox-tabeller och utan ett nyckelfält kommer tabellen att vara skrivskyddad.

Till viss del är nycklar restriktioner och de kan betraktas i samband med CHECK-satsen eftersom deklarationen sker på ett liknande sätt och till och med använder CONSTRAINT-satsen. Låt oss titta på denna process med ett exempel. För att göra detta kommer vi att skapa en tabell med två fält "guid" och "vcName". Detta ställer in "guide"-fältet som primärnyckel:

CREATE TABLE Globally_Unique_Data (guide unikidentifierare DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMÄRNYCKEL (Guid))

Det bästa här är CONSTRAINT-linjen. Som vi vet, efter detta nyckelord kommer namnet på begränsningen, och nyckeldeklarationen är inget undantag. För att namnge en primärnyckel rekommenderar jag att du använder ett namn som PK_name, där namn är namnet på fältet som ska bli primärnyckeln. Förkortningen PK kommer från Primary Key.

Efter detta, istället för nyckelordet CHECK, som vi använde i begränsningarna, finns det en PRIMARY KEY-operator. Detta är vad som indikerar att vi inte behöver en check, utan en primärnyckel. Ett eller flera fält som kommer att utgöra nyckeln anges inom parentes.

Kom ihåg att inga två rader kan ha samma värde i ett nyckelfält, där en primärnyckelbegränsning är identisk med en unik begränsning. Det betyder att om du gör fältet för att lagra efternamnet till primärnyckeln, så kommer det inte att vara möjligt att skriva två Ivanovs med olika namn i en sådan tabell. Detta bryter mot den primära nyckelbegränsningen. Det är därför nycklar är restriktioner och deklareras på samma sätt som en CHECK-begränsning. Men detta gäller inte bara för primärnycklar och sekundära nycklar med unika.

I i detta exempel, är den primära nyckeln ett fält av typen uniqueidentifier (GUID). Standardvärdet för detta fält är resultatet av NEWID-serverproceduren.

Uppmärksamhet

Endast en primärnyckel kan skapas för en tabell

För att förenkla exemplen är det lämpligt att använda en numerisk typ som nyckel, och om databasen tillåter blir det bättre om den är av typen "autoincrement" (automatiskt ökande/minskande tal). I MS SQL Server är detta fält IDENTITY, och i MS Access är det ett fält av "räknartyp".

Följande exempel visar hur man skapar en produkttabell med ett automatiskt ökande heltalsfält som primärnyckel:

SKAPA TABELL Produkter (id int IDENTITY(1, 1), produkt varchar(50), prispengar, kvantitet numerisk(10, 2), CONSTRAINT PK_id PRIMÄRNYCKEL (id))

Det är den här typen av nyckel som vi kommer att använda oftast, eftersom nyckelfältet kommer att lagra siffror som är lätta att förstå och som blir enklare och mer visuella att arbeta med.

En primärnyckel kan bestå av mer än en kolumn. Följande exempel skapar en tabell där fälten "id" och "Produkt" utgör primärnyckeln, vilket innebär att ett unikt index kommer att skapas på båda fälten:

CREATE TABLE Products1 (id int IDENTITY(1, 1), Product varchar(50), Price money, Quantity numeric(10, 2), CONSTRAINT PK_id PRIMARY KEY (id, [Produktnamn]))

Mycket ofta skapar programmerare en databas med ett nyckelfält i form av ett heltal, men samtidigt säger uppgiften tydligt att vissa fält måste vara unika. Varför inte omedelbart skapa en primärnyckel från de fält som måste vara unika och det kommer inte att finnas något behov av att skapa separata lösningar för detta problem.

Den enda nackdelen med en primärnyckel med flera kolumner är problemet med att skapa relationer. Här måste man ta sig ur det med olika metoder, men problemet kan ändå lösas. Du behöver bara ange ett fält av typen unikidentifierare och göra en anslutning med den. Ja, i det här fallet får vi en unik primärnyckel och ett fält av typen unikidentifierare, men denna redundans kommer som ett resultat inte att vara större än samma tabell där primärnyckeln är unikidentifierare, och en unikhetsbegränsning sätts på de fält som måste vara unik. Vad ska man välja? Beror på specifik uppgift och vad är bekvämare för dig att arbeta med.

1.2.6. Främmande nyckel

En främmande nyckel är också en CONSTRAINT-begränsning och representerar förhållandet mellan två tabeller. Låt oss säga att du har två tabeller:

Namn – innehåller namn på personer och består av identifieringsfält (nyckelfält), namn.
Telefoner är en telefontabell som består av en identifierare (nyckelfält), en främmande nyckel för att ansluta till namntabellen och ett strängfält för att lagra telefonnumret.

En person kan ha flera telefoner, så vi delade upp datalagringen i olika tabeller. Figur 1.4 visar visuellt förhållandet mellan två tabeller. Om du redan har arbetat med länkade tabeller kommer detta att räcka för dig. Om du hör om anslutningar för första gången, låt oss försöka ta en närmare titt på problemet.

Låt oss till exempel ta ett bord med tre personer. Tabell 1.3 visar innehållet i tabellen "Namn". Det finns bara tre rader och var och en har sin egen unika huvudnyckel. För att vara unik, när vi skapar en tabell, kommer vi att göra nyckeln till ett automatiskt ökande fält.

Tabell 1.3 Innehåll i tabellen Namn

Tabell 1.4. Innehåll i tabellen Telefoner

Tabell 1.4 innehåller fem telefonnummer. Huvudnyckelfältet innehåller också en unik huvudnyckel, som också kan göras automatiskt inkrementerad. En sekundär nyckel är en relation till den primära nyckeln i tabellen Namn. Hur fungerar denna koppling? Petrov har siffran 1 som primärnyckel i tabellen Namn I tabellen Telefoner, i den sekundära nyckeln, letar vi efter siffran 1 och får Petrovs telefonnummer. Detsamma gäller resten av inläggen. Visuellt kan kopplingen ses i figur 1.5.

Denna typ av datalagring är mycket bekväm. Om det inte var möjligt att skapa relaterade tabeller skulle vi i Namntabellen behöva ange alla telefonnummer i ett fält. Detta är obekvämt med tanke på användning, underhåll och datahämtning.

Du kan skapa flera namnfält i en tabell, men frågan uppstår - hur många. En person kan bara ha 1 telefon, men jag har t.ex. 3, jobbiga inte med. Stor mängd fält leder till dataredundans.

Du kan skapa en separat rad med efternamnet för varje telefon i namntabellen, men detta är bara enkelt för detta enkelt exempel, när du bara behöver ange ett efternamn och du enkelt kan göra flera poster för Petrov med flera telefonnummer. Vad händer om det finns 10 eller 20 fält? Så skapandet av två tabeller länkade med en främmande nyckel kan ses i Listing 1.6.

Lista 1.6. Skapa tabeller länkade med en främmande nyckel

CREATE TABLE Names (idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Phones (idPhone int IDENTITY(1,1), idName int, vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMÄRNYCKEL (idPhone), CONSTRAINT FK_idName UTLÄNDLIG KEY (idName) REFERENSER Namn (idName))

Vänligen granska listningsinnehållet noggrant. Det är ganska intressant eftersom det använder några av de operatörer som vi redan har täckt och ytterligare exempel kommer inte att skada. För båda tabellerna skapas ett nyckelfält som kommer först, är av typen int och inkrementeras automatiskt, med start från 1 i steg om ett. Nyckelfältet görs till huvudnyckeln med hjälp av en CONSTRAINT-begränsning.

I beskrivningen av tabellen Telefoner innehåller den sista raden en ny deklaration för oss, nämligen deklarationen av en främmande nyckel med hjälp av FOREIGN KEY-operatören. Som du kan se är detta också en begränsning och lite senare kommer du att se varför. Tabellfältet som ska länkas till en annan tabell anges inom parentes. Efter detta kommer nyckelordet REFERENCES (länk), namnet på tabellen som kopplingen ska vara med (Names) och inom parentes namnet på fältet ("idName"). Vi har alltså gjort en koppling, som visas i figur 1.4.

Uppmärksamhet!

En främmande nyckel kan bara referera till en annan tabells primärnyckel eller en unik begränsning. Det betyder att efter nyckelordet REFERENCES måste det finnas ett tabellnamn och endast en primärnyckel eller ett fält med en UNIQUE begränsning kan anges inom parentes. Andra fält kan inte anges.

Nu, om du kan fylla tabellerna med data. De nästa tre lagen lägger till de tre namnen vi såg i Tabell 1.3:

INSERT INTO Names(vcName) VALUES("Petrov") INSERT INTO Names(vcName) VALUES("Ivanov") INSERT INTO Names(vcName) VALUES("Sidorov")

Om du redan har arbetat med SQL kan du lägga till poster för telefontabellen. Jag kommer att utelämna dessa kommandon, men du kan se dem i filen foreign_keys.sql i katalogen Chapter1 på CD:n.

Vår uppgift nu är att se vad de restriktiva åtgärderna för en främmande nyckel är, låt oss ta reda på det. Vi har specificerat ett explicit förhållande mellan två fält i olika tabeller. Om du försöker lägga till en post i telefontabellen med en identifierare i fältet "idName" som inte finns i fältet med samma namn (namnet kan ändras till ett annat) i tabellen med efternamn, uppstår ett fel . Detta kommer att bryta relationen mellan de två tabellerna, och den främmande nyckel-begränsningen tillåter inte poster att existera utan relationen.

Begränsningen gäller även vid ändring eller radering av poster. Om du till exempel försöker ta bort en rad med efternamnet Petrov, uppstår ett fel med en främmande nyckel. Du kan inte ta bort poster som har externt relaterade rader. Först måste du radera alla telefonnummer för denna post, och först efter det kommer det att vara möjligt att ta bort raden med efternamnet Petrov.

När du skapar en främmande nyckel kan du ange ON DELETE CASCADE eller ON UPDATE CASCADE. I det här fallet, om du tar bort Petrovs post från namntabellen eller ändrar identifieraren, kommer alla poster i telefontabellen som är associerade med Petrovs rad att uppdateras automatiskt. Aldrig. Nej, det måste skrivas med stora bokstäver: Gör ALDRIG så här. Allt måste raderas eller ändras manuellt. Om en användare av misstag raderar en post från namntabellen, raderas även motsvarande telefoner. Det är ingen idé att skapa en främmande nyckel om hälften av dess restriktioner försvinner! Allt måste göras manuellt, och det rekommenderas aldrig att ändra identifierare.

Att ta bort själva tabellerna bör också börja med den underordnade tabellen, det vill säga med telefoner, och först då kan du ta bort huvudtabellen Namn.

Slutligen ska jag visa dig hur du vackert får en matchning mellan namn och telefonnummer från två bord:

VÄLJ vcName, vcPhone FROM Names, Phones WHERE Names.idName=Phones.idName

Vi kommer att prata om sådana frågor mer i detalj i kapitel 2. För nu gav jag ett exempel bara så att du kan se kraften i relaterade tabeller.

En tabell kan innehålla upp till 253 främmande nycklar, vilket är tillräckligt för även de mest komplexa databaserna. Själv var jag tvungen att arbeta med databaser där antalet främmande nycklar inte översteg 7 per tabell. Om det är mer, är databasen troligen felaktigt utformad, även om det finns undantag.

Själva tabellen kan också ha maximalt 253 främmande nycklar. Främmande nycklar i en tabell är mindre vanliga, vanligtvis inte fler än 3. Oftast kan en tabell ha många länkar till andra tabeller.

En främmande nyckel kan referera till samma tabell som den skapas i. Du har till exempel en tabell över jobbtitlar i en organisation, som visas i Tabell 1.5. Tabellen består av tre fält: primärnyckel, främmande nyckel och befattning. Vilken organisation som helst kan ha många positioner, men det skulle vara ganska logiskt att visa deras namn och underordningsstruktur i en tabell. För att göra detta måste den främmande nyckeln vara associerad med den primära nyckeln i positionstabellen.

Tabell 1.5. Bord med intern länk

Som ett resultat får vi att generaldirektören har en noll främmande nyckel, d.v.s. denna position står i spetsen för alla andra. På kommersiell direktör och direktör för allmänna frågor Utlandsnyckeln pekar mot vd-raden. Det innebär att dessa två befattningar rapporterar direkt till generaldirektör. Och så vidare.

Låt oss se hur vi kan skapa allt detta som en SQL-fråga:

SKAPA TABELL Positioner (idPosition int IDENTITY(1,1), idParentPosition int, vcName varchar(30), CONSTRAINT PK_idPosition PRIMARY KEY (idPosition), CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) REFERENCER Position)s (idPosition)

Som du kan se refererar den främmande nyckeln helt enkelt till samma tabell som vi skapar. På CD:n, i katalogen Chapter1, kan du se i filen foreign_keys_to_self.sql ett exempel på att skapa denna tabell, fylla den med data och visa positioner med hänsyn till deras underordning. I nästa kapitel kommer vi att titta närmare på möjligheten att arbeta med sådana tabeller.

En till en relation

Hittills har vi tittat på det klassiska förhållandet, när en rad i huvuddatatabellen motsvarar en rad från den relaterade tabellen. Detta förhållande kallas en-till-många. Men det finns andra kopplingar, och nu ska vi titta på en annan - en till en, när en post i huvudtabellen är kopplad till en post i en annan. För att implementera detta räcker det att länka primärnycklarna till båda tabellerna. Eftersom primärnycklar inte kan upprepas kan endast en rad relateras i båda tabellerna.

Följande exempel skapar två tabeller som har en primär nyckelrelation:

Endast ett av borden behöver en främmande nyckel. Eftersom relationen är en till en spelar det ingen roll i vilken tabell den ska skapas.

många till många

Det mest komplexa förhållandet är många-till-många, där många poster från en tabell matchar många poster från en annan tabell. För att implementera detta räcker det inte med tre tabeller.

Först måste vi förstå när en många-till-många-relation kan användas? Låt oss säga att du har två tabeller: en lista över husboende och en lista med telefonnummer. En lägenhet kan ha mer än ett nummer, vilket innebär att ett efternamn kan ha två telefonnummer. Det visar sig att det finns en en-till-många-relation. Å andra sidan kan det finnas två familjer i en lägenhet (en gemensam lägenhet eller bara en hyresgäst som använder ägarens telefon), vilket gör att kopplingen mellan telefonen och den boende också är en till många. Och det svåraste alternativet är att ha två telefoner i en gemensam lägenhet. I det här fallet används båda numren av flera boende i lägenheten. Så det visar sig att "många" familjer kan använda "många" telefoner (många-till-många-kommunikation).

Hur implementerar man en många-till-många-relation? Vid första anblicken är detta omöjligt i relationsmodellen. För ungefär 10 år sedan letade jag länge olika alternativ och slutade med att helt enkelt skapa en tabell som var fylld med redundant data. Men en dag fick jag en uppgift, tack vare vilken en utmärkt lösning uppstod från villkoren - jag behövde skapa två tabeller med lägenhetsboende och telefonnummer och implementera bara en primärnyckel i dem. Främmande nycklar behövs inte i denna tabell. Men kopplingen mellan borden bör vara genom ett tredje, anslutande bord. Vid första anblicken är detta svårt och oklart, men när du väl förstår denna metod kommer du att se den fulla kraften i denna lösning.

Tabell 1.6 och 1.7 visar exempel på efternamns- respektive telefontabeller. Och Tabell 1.8 visar länkningstabellen.

Tabell 1.6. Efternamnstabell

Tabell 1.7. Telefonbord

Tabell 1.8. Telefonbord

Låt oss nu se hur datasökningslogiken kommer att se ut i ett många-till-många-förhållande. Låt oss säga att vi måste hitta alla telefoner som tillhör Ivanov. Ivanovs primärnyckel är lika med 1. Vi hittar i länkningstabellen alla poster för vilka fältet "Relation med namn" är lika med 1. Dessa kommer att vara poster 1 och 2. I dessa poster i fältet "Relation med telefon" finns är identifierare 1 respektive 2, och Det betyder att Ivanov äger numren från telefonbordet, som finns på rad 1 och 2.

Låt oss nu lösa det omvända problemet - bestäm vem som har tillgång till telefonnumret 567575677. Detta nummer i telefontabellen har knapp 3. Vi letar efter alla poster i länkningstabellen, där det i fältet "Telefonanslutning" är lika med 3. Det här är poster med nummer 4 och 5, som i fältet "Namnlänk" innehåller värden 2 respektive 3. Om du nu tittar på tabellen över efternamn, kommer du att se Petrov och Sidorov på nummer 2 och 3. Det innebär att dessa två boende använder telefonnumret 567575677.

Gå igenom alla tre tabellerna och se till att du förstår vilka telefonnummer som tillhör vilka boende och vice versa. Om du ser detta samband kommer du att förstå att det är så enkelt som tre ören och du kan snabbt implementera det i dina projekt.

CREATE TABLE Names (idName uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Phones (idPhone uniqueidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT PK_idPhoneidPhone PRIMARY) KEY CREATE TABLE LinkTable (idLinkTable uniqueidentifier DEFAULT NEWID(), idName uniqueidentifier, idPhone uniqueidentifier, CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable), CONSTRAINT FK_idPhone FOREIGN KEY (idPhone) REFERENCES Phones (idPhone), CONSTRAINT Name REFERENFKEYs ))

Länkningstabellen har två främmande nycklar som länkar till namnen och telefontabellerna och en primärnyckel som säkerställer att poster är unika.

Jag valde GUID-fältet som primärnyckel eftersom det är bekvämare för att lösa just detta problem. Faktum är att vi måste infoga poster i två tabeller och i båda fallen måste vi ange samma nyckel. GUID-värdet kan genereras och sedan användas när data infogas i båda tabellerna.

Du kan också använda ett automatiskt ökande fält som nyckel, men i det här fallet är problemet lite svårare att lösa, eller snarare, det är obekvämt att lösa problemet. Till exempel, när du lägger till ett telefonnummer måste du först infoga motsvarande rad i tabellen, sedan hitta den, bestämma nyckeln som tilldelades raden och sedan göra anslutningen.

I det här skedet är vi begränsade till att bara skapa tabeller, men i avsnitt 2.8 kommer vi att återkomma till detta ämne och lära oss och lära oss hur man arbetar med relaterade tabeller. Att arbeta med en en-till-en- och en-till-många-relation är inte mycket annorlunda, eftersom endast två tabeller är inblandade i detta schema. Många-till-många-relationer är lite mer komplicerade på grund av länkningstabellen, så vi tar upp det separat i avsnitt 2.27.

Tematiskt material: