Cheie secundară a bazei de date. Conceptul de cheie într-o bază de date, chei primare și străine. Integritate referenţială în cascadă

Sunt utilizate în orice activitate: în industria bancară și financiară, afaceri turistice, depozite, producție și formare. Sunt o colecție de tabele, au proprietăți clare și sunt supuse unor cerințe stricte. În bazele de date relaționale, tabelele sunt numite relații.

Ce este o cheie primară într-o bază de date

Într-o bază de date, cheia primară a unui tabel este una dintre coloanele acestuia (cheia primară). Să ne uităm la un exemplu despre cum arată asta. Să ne imaginăm o simplă atitudine a studenților (să-i spunem „Studenți”).

Trebuie să identificăm în mod unic un student folosind o singură coloană. Pentru a face acest lucru, informațiile din această coloană trebuie să fie unice pentru fiecare intrare. Însă datele disponibile în acest sens nu ne permit să identificăm fără ambiguitate dosarul, întrucât toponimii, toponimii și studenții cu aceleași nume de familie și prenume pot studia în același curs și facultate. Cheia primară din baza de date este utilizată pentru a identifica cu precizie rândul necesar într-o relație. Cel mai adesea, un câmp numeric este utilizat în această calitate, crescând automat pe măsură ce se introduce o înregistrare (coloana de identificare cu incrementare automată).

Cheie primară simplă și compusă

Cheia primară poate fi simplă sau compusă. Dacă unicitatea unei înregistrări este determinată de valoarea dintr-un singur câmp, așa cum este descris mai sus, avem de-a face cu o cheie simplă. O cheie compusă este o cheie primară a bazei de date constând din două sau mai multe câmpuri. Luați în considerare următoarea atitudine a clienților băncilor.

NUMELE COMPLET.	Data nașterii	Seria pașapoarte	Pașaport ID
Ivanov P.A.	12.05.1996	75	0553009
Sergheev V.T.	14.07.1958	71	4100654
Krasnov L.V.	22.01.2001	73	1265165

Pașapoartele oamenilor pot conține aceleași serii sau numere, dar nu există pașapoarte cu aceeași serie și combinație de numere. Astfel, câmpurile „Seria pașaport” și „Număr pașaport” vor deveni o cheie compusă a relației specificate, identificând în mod unic persoana.

Legături între relații

Deci, o cheie primară într-o bază de date este una sau mai multe coloane ale unui tabel care permite identificarea unică a unui rând în acea relație. Pentru ce este?

Să revenim la primul exemplu cu relația „Elevi”. Pe lângă această relație, baza de date stochează și alte informații, de exemplu, performanța fiecărui elev. Pentru a nu repeta toate informațiile care sunt deja conținute în baza de date, aceștia folosesc o cheie, referitor la înregistrarea dorită. Arata cam asa.

În cele două exemple de relații vedem un câmp ID. Acestea sunt cheile primare din baza de date pentru aceste tabele. După cum puteți vedea, dosarul academic conține doar link-uri către aceste câmpuri din alte tabele, fără a fi nevoie să indicați toate informațiile din acestea.

Cheie naturală și surogat

Cum se determină cheia primară a unui tabel al bazei de date? Cele două exemple pe care le-am analizat - „Studenți” și „Clienți bănci” - ilustrează conceptele de chei naturale și surogat. În tabelul clienților băncii, am definit o cheie formată din câmpurile „Număr” și „Seria pașaport”, folosind coloanele deja existente. Această cheie se numește naturală; nu am făcut nicio modificare sau adăugare pentru ao determina. În cazul relației „Studenți”, niciun câmp unic sau combinație de câmpuri nu ne-a oferit unicitate. Acest lucru ne-a forțat să introducem un câmp suplimentar de cod de student. Această cheie se numește o cheie surogat, pentru care am adăugat o altă coloană de serviciu la tabel. Această coloană nu conține informații utile și servește doar la identificarea înregistrărilor.

Cheie străină și integritatea datelor în baza de date

Toate cele de mai sus ne duc la cheia externă și integritatea bazei de date. Cheia străină este un câmp care se referă la cheia primară a unei relații străine. În tabelul de progres, acestea sunt coloanele „Student” și „Disciplină”. Datele lor ne trimit la tabele externe. Adică câmpul „Student” din relația „Performanță” este o cheie străină, iar în relația „Student” este cheia primară din baza de date.

Un principiu important pentru construirea bazelor de date este integritatea acestora. Iar una dintre regulile sale este integritatea referenţială. Aceasta înseamnă că o cheie externă a unui tabel nu se poate referi la o cheie primară inexistentă a unei alte relații. Nu puteți șterge o înregistrare cu codul 1000 - Ivan Ivanov din relația Student dacă este referită printr-o înregistrare din tabelul de performanță academică. Într-o bază de date construită corespunzător, când încercați să ștergeți, veți primi o eroare că acest câmp este în uz.

Există și alte grupuri de reguli de integritate, precum și alte restricții de baze de date, care merită, de asemenea, atenție și ar trebui luate în considerare de dezvoltatori.

În acest articol vom încerca să luăm în considerare tot ceea ce este legat de chei în SQL: pentru ce sunt crearea și restricțiile cheilor? În general: va fi plictisitor 😉

Planul pentru azi este:

În teoria bazelor de date relaționale - chei Acestea sunt anumite entități create pentru a stabili anumite restricții care mențin integritatea și disponibilitatea datelor în tabelele bazei de date.

Cu cuvinte simple, cheile sunt în sql sunt create pentru a indica funcționalitatea suplimentară pentru o coloană. Fie că este vorba de unicitate sau de faptul că coloana face referire la un alt tabel (cheie externă).

Cheia principala

O coloană care trebuie să fie unică în baza de date este marcată cu o cheie primară. Cheia primară sau cheia primară înseamnă că valoarea coloanei cheii primare nu poate fi repetată în tabel. Astfel, această cheie vă permite să identificați în mod unic o înregistrare în tabel fără teama că valoarea coloanei se va repeta. Doar un exemplu: să presupunem că aveți un tabel de utilizatori. Acest tabel are următoarele câmpuri: nume complet, anul nașterii, telefon. Cum să identifici un utilizator? Parametri precum numele complet și numărul de telefon nu pot fi de încredere. La urma urmei, putem avea mai mulți utilizatori nu numai cu același nume de familie, ci și cu același prenume. Numărul de telefon se poate schimba în timp, iar utilizatorul cu numărul de telefon poate să nu fie cel din baza noastră de date.

Acesta este motivul pentru care a fost inventată cheia primară. Odată atribuit un identificator unic și asta este tot. ÎN mySql pe exemplul căruia executăm toate exemplele din teren INCREMENT AUTO nu poate fi setat decât dacă indicați că aceasta este o cheie primară.

Nu cred că merită menționat faptul că un câmp marcat ca cheie primară nu poate fi gol atunci când se creează o înregistrare.

Cheie externă ( cheie externă)

Mai sunt ceva cheie externă (cheie externă). Se mai numește și referință. Este necesar să legați tabele între ele.

Dacă te uiți la imaginea de mai sus, cheia străină va fi câmpul furnizorului din tabelul de pantofi. De obicei, atunci când creați un tabel, specificați o coloană cu valori întregi unice. Cum am făcut-o când am creat tabelul furnizor

Coloană furnizor_id va fi unic pentru fiecare intrare. Valoarea acestuia va apărea pe coloană furnizor in masa pantofi. Vă sugerez să vedeți imediat un exemplu despre cum este creată o cheie străină.

Crearea unei chei externe

create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), provider int, cheie străină (furnizor) referințe furnizor (furnizor_id));

După cum puteți vedea în exemplul de mai sus, sintaxa pentru crearea unei chei străine este destul de simplă. Trebuie să adăugați un câmp la tabel, apoi să declarați acest câmp ca o cheie străină și să indicați unde se va referi. În acest caz, câmpul furnizor se va referi la domeniu furnizor_id in masa furnizor

Cheie compusă (cheie compusă)

În ceea ce privește o cheie compusă, acestea sunt mai multe chei primare dintr-un tabel. Astfel, după ce a creat cheie compusă, unicitatea înregistrării va fi verificată de câmpurile care sunt combinate în această cheie.

Există situații în care, la introducerea într-un tabel, trebuie să verificați unicitatea unei înregistrări folosind mai multe câmpuri simultan. Acesta este motivul pentru care a fost inventată o cheie compozită. De exemplu, voi crea un tabel simplu cu cheie compusă pentru a arăta sintaxa:

Creare test tabel (câmp_1 int, câmp_2 text, câmp_3 bigint, cheie primară (câmp_1, câmp_3));

În exemplul de mai sus, două câmpuri sunt combinate într-o cheie compusă și nu vor exista înregistrări în tabel cu aceste câmpuri identice.

Asta e totul despre cheile în SQL. Acest mic tutorial este o pregătire pentru articol în care vom analiza în detaliu cum să combinați tabelele astfel încât acestea să formeze o singură bază de date.

O cheie externă este o cheie folosită pentru a uni două tabele. Uneori este numită și cheie de referință.

O cheie externă este o coloană sau o combinație de coloane ale căror valori corespund unei chei primare dintr-un alt tabel.

Relația dintre 2 tabele corespunde unei chei primare într-unul dintre tabele cu o cheie străină în al doilea tabel.

Dacă un tabel are o cheie primară definită pentru orice câmp(e), atunci nu puteți avea două înregistrări care au aceeași valoare pentru acel câmp(e).

Exemplu

Să ne uităm la structura următoarelor două tabele.

tabel CLIENȚI

CREATE TABLE CLIENTS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));

tabel COMENZI

CREATE TABLE COMANDI (ID INT NU NUL, DATA DATETIME, CUSTOMER_ID INT referințe CUSTOMERS(ID), AMOUNT double, PRIMARY KEY (ID));

Dacă tabelul ORDERS a fost deja creat și cheia externă nu a fost încă setată, atunci sintaxa este utilizată pentru a seta cheia externă prin modificarea tabelului.

ALTERĂ COMENZI TABEL ADĂUGĂ CHEIE STRĂINĂ (ID_client) REFERINȚE CLIENTI (ID);

Eliminarea unei constrângeri de cheie străină

Pentru a elimina o constrângere de cheie străină, utilizați următoarea sintaxă SQL.

ALTER TABLE ORDERS DROP CHEIE STRĂINĂ;

În acest subiect, folosind două tabele ca exemplu, sunt definite conceptele de bază ale bazelor de date relaționale și anume:

cheia principala;
cheie externă;
cheie simplă și compusă;
atitudine, tipuri de relații;
chei artificiale și naturale;
tabelele principale (master) și subordonate (detaliu).

Date de intrare

Să fie dată o bază de date cu angajații întreprinderii, care constă din două tabele. Primul tabel conține date despre angajat. Al doilea tabel conține informații despre salariul angajatului.

Tabelele au următoarea structură.

"Muncitor". Conține datele angajaților "Salariu". Conține informații despre salariile angajaților.

Întrebare răspuns

1. Ce este o cheie primară într-un tabel de bază de date? Pentru ce sunt folosite cheile primare?

Când lucrați cu tabele în baze de date relaționale, este de dorit (necesar) ca fiecare tabel să aibă așa-numitul cheia principala.

Cheia principala este un câmp care este utilizat pentru a asigura unicitatea datelor din tabel. Aceasta înseamnă că valoarea (informația) din câmpul cheie primară din fiecare rând (înregistrare) din tabel poate fi unică.

Unicitatea este necesară pentru a evita ambiguitatea atunci când nu se știe ce înregistrare de tabel poate fi accesată dacă există înregistrări duplicat în tabel (două înregistrări au aceleași valori în toate câmpurile tabelului).

Exemplu. Pentru tabelul „Angajat”, puteți introduce un câmp suplimentar, care va fi cheia principală. Totuși, câmpul (atribut) „Număr de personal” asigură și unicitatea. Din moment ce, teoretic, nu pot exista două numere de personal identice. În practică, pot exista cazuri în care același număr de personal este introdus din greșeală și valorile tuturor câmpurilor din tabel sunt aceleași. Ca rezultat, două înregistrări identice vor apărea în tabel. Pentru a evita o astfel de eroare, este mai bine să creați un câmp suplimentar de contor în tabel, care va asigura unicitatea.

De asemenea, puteți introduce un câmp suplimentar pentru tabelul „Salariu”, care va fi cheia principală.

2. Care este relația dintre tabele? Exemplu

Tabelele din modelul de date relaționale pot avea relații între ele. Astfel de conexiuni se numesc relații. Pentru tabelele „Angajat” și „Salariu”, puteți stabili o conexiune folosind câmpul „Număr de personal”.

Exemplu. Să analizăm tabelele „Angajat” și „Salariu”. În aceste tabele, puteți stabili o relație între tabele pe baza câmpului Număr personal. Adică, legătura dintre tabele are loc pe baza câmpului (atributului) „Număr de personal”.

Aceasta înseamnă următoarele. Dacă trebuie să găsiți salariile acumulate în tabelul „Salariu” pentru angajatul Ivanov I.I., atunci trebuie să efectuați următorii pași:

afla numarul de personal al angajatului Ivanov I.I. în tabelul „Angajat”. Valoarea numărului de personal este 7585;
în tabelul „Salariu”, găsiți toate valorile care sunt egale cu 7585 (număr de personal);
selectați din tabelul „Salariu” toate valorile câmpului „Acumulat” care corespund numărului de personal 7585.

Orez. 1. Ilustrarea relației dintre tabele. Numărul de personal 2145 din tabelul „Angajat” este afișat în tabelul „Salariu”

Orez. 2. Conexiune (relație) între câmpurile tabelului

3. Ce este o cheie externă? Exemplu

Conceptul de „cheie străină” este important atunci când luăm în considerare tabelele înrudite.

Cheie externă– este unul sau mai multe câmpuri (atribute) care sunt primare într-un alt tabel și a căror valoare este înlocuită cu valorile cheii primare a altui tabel.

Exemplu. Să existe o relație între tabelele „Angajat” și „Salariu” din câmpul „Număr de personal”. În acest caz, câmpul „Număr de personal” din tabelul „Angajat” poate fi cheia principală, iar câmpul „Număr de personal” din tabelul „Salariu” poate fi o cheie străină. Aceasta înseamnă că valorile câmpului „Număr de personal” din tabelul „Salariu” sunt înlocuite cu valorile câmpului „Număr de personal” din tabelul „Angajat”.

4. Ce este o cheie externă recursivă?

Cheie străină recursive este o cheie externă care face referire la același tabel căruia îi aparține. În acest caz, câmpul (atributul) care corespunde cheii externe este cheia aceleiași relații (link).

5. Pot fi cheile primare și străine simple sau compuse (complexe)?

Cheile primare, secundare și străine pot fi fie simple, fie compuse (complexe). Chei simple– acestea sunt chei care conțin un singur câmp (un singur atribut). Compozit cheile (complexe) sunt chei care conțin mai multe câmpuri (atribute).

6. Care este diferența dintre o cheie artificială și una naturală? Exemplu

Cheie naturală oferă unicitate din însăși esența domeniului subiectului. Există cazuri când valorile înregistrărilor unor câmpuri (câmpuri) din tabel sunt unice. Acest câmp poate fi o cheie naturală.

Cheie artificială este adăugat suplimentar pentru a asigura valori unice. Cel mai adesea, cheia artificială este un câmp de tip contor. Într-un astfel de câmp, atunci când o înregistrare nouă (rând) este adăugată la tabel, valoarea contorului crește cu 1 (sau altă valoare). Dacă o înregistrare este ștearsă din tabel, valoarea maximă a contorului de rânduri nu mai este redusă, ci rămâne așa cum este. De obicei, toate acestea sunt monitorizate de un sistem de gestionare a bazelor de date.

Exemplu.În tabelul „Angajat”, cheia naturală este câmpul (atributul) „Număr de personal”. Câmpul „Număr de personal” este unic în sine, deoarece nu pot exista doi angajați cu același număr de personal.

În tabelul „Salariu”, valoarea din toate cele patru câmpuri se poate repeta accidental. Prin urmare, aici este recomandabil să adăugați un câmp suplimentar de contor, care va fi o cheie artificială. În acest caz, tabelul „Salariu” cu un câmp suplimentar poate arăta cam așa:

unde câmpul „Număr” este o cheie artificială care asigură unicitatea.

7. Care sunt diferitele moduri de a selecta o cheie primară?

Există 3 moduri de a selecta o cheie primară:

utilizați câmpul de creștere (câmpul contorului) ca o cheie artificială;
selectați un câmp din date care poate oferi unicitate;
selectați mai multe câmpuri din date care pot oferi unicitate. În acest caz, cheia va fi numită și complexă (compozită).

8. Ce înseamnă termenii „masă principală” (master) și „masă subordonată” (detaliu)?

Dacă există o relație între tabele, atunci unul dintre ele poate fi cel principal (master), iar celălalt subordonat (detaliu). Tabelul principal afișează toate înregistrările care se încadrează în el. Tabelul slave afișează numai acele înregistrări care se potrivesc cu valoarea cheii tabelului principal, care este activă în prezent (actuală). Dacă înregistrarea curentă a tabelului principal se modifică, atunci setul de înregistrări disponibile din tabelul slave se modifică.

Exemplu. Dacă luăm în considerare tabelele „Angajat” și „Salariu”, atunci tabelul „Angajat” este cel principal, iar tabelul „Salariu” este unul subordonat.

9. Ce tipuri de relații (legături) există între tabele?

Există 4 tipuri principale de relații între tabele:

"unu la unu". În acest caz, fiecare înregistrare a unui tabel corespunde doar unei înregistrări a altui tabel;
"unu la multi". Acesta este atunci când o înregistrare a tabelului principal (master) corespunde mai multor înregistrări a tabelului subordonat (detaliu). Adică, fiecare înregistrare care este cheia primară a unui tabel corespunde mai multor înregistrări ale tabelului aferent;
"multi la unu". Acesta este atunci când mai multe înregistrări ale tabelului principal corespund unei înregistrări a tabelului subordonat;
„mulți la mulți”. Acesta este momentul în care există mai multe înregistrări asociate în ambele tabele.

Exemplu. Dacă luăm în considerare relația dintre tabelele „Angajat” și „Salariu”, atunci această relație este de tip „unu-la-mulți”. Tabelul „Angajați” este cel principal. Tabelul „Salariu” este un tabel subordonat.

Și așa, în liniște, am abordat un subiect foarte important - cheile primare și străine. Dacă primele sunt folosite de aproape toată lumea, atunci cele din urmă sunt cumva ignorate. Dar în zadar. Cheile externe nu sunt o problemă, ele sunt de un real ajutor în integritatea datelor.

1.2.5. Cheia principala

Am vorbit deja mult despre domeniile cheie, dar nu le-am folosit niciodată. Cel mai interesant lucru este că totul a funcționat. Acesta este un avantaj, sau poate un dezavantaj, al bazelor de date Microsoft SQL Server și MS Access. Acest truc nu va funcționa în tabelele Paradox și fără un câmp cheie tabelul va fi doar pentru citire.

Într-o oarecare măsură, cheile sunt constrângeri și ar putea fi luate în considerare împreună cu instrucțiunea CHECK, deoarece declarația are loc într-un mod similar și chiar utilizează instrucțiunea CONSTRAINT. Să ne uităm la acest proces cu un exemplu. Pentru a face acest lucru, vom crea un tabel cu două câmpuri „guid” și „vcName”. Aceasta setează câmpul „ghid” ca cheie primară:

CREATE TABLE Globally_Unique_Data (identificator unic ghid DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (Guid))

Cea mai bună parte aici este linia CONSTRAINT. După cum știm, după acest cuvânt cheie vine numele constrângerii, iar declarația cheii nu face excepție. Pentru a denumi o cheie primară, vă recomand să folosiți un nume precum PK_name, unde nume este numele câmpului care ar trebui să devină cheia primară. Abrevierea PK provine de la Primary Key.

După aceasta, în loc de cuvântul cheie CHECK, pe care l-am folosit în constrângeri, există un operator PRIMARY KEY Acesta este ceea ce indică faptul că nu avem nevoie de o verificare, ci de o cheie primară. Unul sau mai multe câmpuri care vor alcătui cheia sunt indicate în paranteze.

Rețineți că două rânduri nu pot avea aceeași valoare într-un câmp de cheie, în care o constrângere de cheie primară este identică cu o constrângere unică. Aceasta înseamnă că dacă faceți câmpul pentru stocarea numelui de familie ca cheie primară, atunci nu va fi posibil să scrieți două Ivanov cu nume diferite într-un astfel de tabel. Acest lucru încalcă constrângerea cheii primare. Acesta este motivul pentru care cheile sunt constrângeri și sunt declarate în același mod ca o constrângere CHECK. Dar acest lucru nu este valabil numai pentru cheile primare și cheile secundare cu unicitate.

În acest exemplu, cheia primară este un câmp de tip identificator unic (GUID). Valoarea implicită pentru acest câmp este rezultatul procedurii serverului NEWID.

Atenţie

Numai o cheie primară poate fi creată pentru un tabel

Pentru a simplifica exemplele, este indicat să folosiți ca cheie un tip numeric, iar dacă baza de date permite, va fi mai bine dacă este de tip „autoincrement” (număr crescător/descrescător automat). În MS SQL Server acest câmp este IDENTITATE, iar în MS Access este un câmp de tip „contor”.

Următorul exemplu arată cum să creați un tabel de produse cu un câmp întreg cu incrementare automată ca cheie primară:

CREATE TABLE Produse (id int IDENTITY(1, 1), product varchar(50), Price money, Quantity numeric(10, 2), CONSTRAINT PK_id PRIMARY KEY (id))

Acesta este tipul de cheie pe care îl vom folosi cel mai des, deoarece câmpul cheie va stoca numere care sunt ușor de înțeles și vor fi mai ușor și mai vizual de lucrat.

O cheie primară poate consta din mai mult de o coloană. Următorul exemplu creează un tabel în care câmpurile „id” și „Product” formează cheia primară, ceea ce înseamnă că va fi creat un index unic pentru ambele câmpuri:

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

De foarte multe ori programatorii creează o bază de date cu un câmp cheie sub forma unui număr întreg, dar, în același timp, sarcina prevede clar că anumite câmpuri trebuie să fie unice. De ce să nu creați imediat o cheie primară din acele câmpuri care trebuie să fie unice și nu va fi nevoie să creați soluții separate pentru această problemă.

Singurul dezavantaj al unei chei primare cu mai multe coloane este problema creării de relații. Aici trebuie să ieși din ea folosind diverse metode, dar problema poate fi totuși rezolvată. Trebuie doar să introduceți un câmp de tip unicidentifier și să faceți o conexiune folosindu-l. Da, în acest caz, obținem o cheie primară unică și un câmp de tip unicidentificator, dar această redundanță, ca urmare, nu va fi mai mare decât același tabel în care cheia primară este identificatorul unic și este stabilită o constrângere de unicitate pe câmpurile care trebuie fi unic. Ce sa aleg? Depinde de sarcina specifică și cu ce vă simțiți mai confortabil să lucrați.

1.2.6. Cheie externă

O cheie externă este, de asemenea, o constrângere CONSTRAINT și reprezintă relația dintre două tabele. Să presupunem că aveți două tabele:

Nume – conține numele persoanelor și constă din câmpuri de identificare (câmp cheie), nume.
Telefoane este un tabel de telefon care constă dintr-un identificator (câmp cheie), o cheie străină pentru conectarea la tabelul de nume și un câmp șir pentru stocarea numărului de telefon.

O persoană poate avea mai multe telefoane, așa că am împărțit stocarea datelor în tabele diferite. Figura 1.4 arată vizual relația dintre două tabele. Dacă ați lucrat deja cu tabele legate, atunci acest lucru va fi suficient pentru dvs. Dacă auziți despre conexiuni pentru prima dată, atunci să încercăm să aruncăm o privire mai atentă asupra problemei.

De exemplu, să luăm o masă de trei persoane. Tabelul 1.3 prezintă conținutul tabelului „Nume”. Există doar trei linii și fiecare are propria sa cheie principală unică. Pentru unicitate, atunci când creăm un tabel, vom face din cheie un câmp care crește automat.

Tabelul 1.3 Conținutul tabelului Nume

Tabelul 1.4. Conținutul tabelului Telefoane

Tabelul 1.4 conține cinci numere de telefon. Câmpul cheie principală conține, de asemenea, o cheie principală unică, care poate fi, de asemenea, incrementată automat. O cheie secundară este o relație cu cheia primară a tabelului de nume. Cum funcționează această conexiune? Petrov are numărul 1 ca cheie principală în tabelul Nume. În tabelul Telefoane, în cheia secundară, căutăm numărul 1 și obținem numerele de telefon ale lui Petrov. Același lucru este valabil și pentru restul intrărilor. Vizual conexiunea poate fi văzută în Figura 1.5.

Acest tip de stocare a datelor este foarte convenabil. Dacă nu ar fi posibil să creăm tabele înrudite, atunci în tabelul Nume ar trebui să introducem toate numerele de telefon într-un singur câmp. Acest lucru este incomod din punct de vedere al utilizării, întreținerii și regăsirii datelor.

Puteți crea mai multe câmpuri de nume într-un tabel, dar se pune întrebarea - câte. O persoană poate avea doar 1 telefon, dar eu, de exemplu, am 3, fără să număr pe cele de serviciu. Un număr mare de câmpuri duce la redundanța datelor.

Puteți crea un rând separat cu numele de familie pentru fiecare telefon din tabelul Nume, dar acest lucru este ușor doar pentru un exemplu atât de simplu, când trebuie să introduceți doar numele de familie și puteți face cu ușurință mai multe intrări pentru Petrov cu mai multe telefoane. numere. Ce se întâmplă dacă există 10 sau 20 de câmpuri? Deci, crearea a două tabele legate printr-o cheie străină poate fi văzută în Listarea 1.6.

Lista 1.6. Crearea de tabele legate printr-o cheie străină

CREATE TABLE Nume (idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Telefoane (idPhone int IDENTITY(1,1), idName int, vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone), CONSTRAINT FK_idName CHEIE STRĂINĂ (idName) REFERINȚE Nume (idName))

Vă rugăm să examinați cu atenție conținutul listării. Este destul de interesant pentru că folosește unii dintre operatorii pe care i-am acoperit deja și un exemplu suplimentar ar fi de ajutor. Pentru ambele tabele se creează un câmp cheie, care vine primul, este de tip int și este incrementat automat, începând de la 1 în trepte de unu. Câmpul cheie devine cheia principală folosind o constrângere CONSTRAINT.

În descrierea tabelului Telefoane, ultima linie conține o nouă declarație pentru noi și anume declararea unei chei străine folosind operatorul CHEIE STRĂINĂ. După cum puteți vedea, aceasta este și o limitare și puțin mai târziu veți vedea de ce. Câmpul tabelului care ar trebui să fie legat de un alt tabel este indicat în paranteze. După aceasta urmează cuvântul cheie REFERINȚE (link), numele tabelului cu care ar trebui să fie conexiunea (Nume) și între paranteze numele câmpului ("idName"). Astfel, am realizat o conexiune, care este prezentată în Figura 1.4.

Atenţie!

O cheie externă poate face referire doar la cheia primară sau la constrângerea unică a altui tabel. Aceasta înseamnă că după cuvântul cheie REFERENCES trebuie să existe un nume de tabel și doar o cheie primară sau un câmp cu o constrângere UNIQUE poate fi specificat în paranteze. Alte câmpuri nu pot fi specificate.

Acum, dacă puteți completa tabelele cu date. Următoarele trei echipe adaugă cele trei nume pe care le-am văzut în tabelul 1.3:

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

Dacă ați lucrat deja cu SQL, puteți adăuga intrări pentru tabelul de telefon. Voi omite aceste comenzi, dar le puteți vedea în fișierul foreign_keys.sql din directorul Chapter1 de pe CD.

Sarcina noastră acum este să vedem care sunt acțiunile restrictive ale unei chei străine, să ne dăm seama. Am specificat o relație explicită între două câmpuri din tabele diferite. Dacă încercați să adăugați o înregistrare la tabelul telefonic cu un identificator în câmpul „idName” care nu există în câmpul cu același nume (numele ar putea fi schimbat în altul) în tabelul cu nume de familie, va apărea o eroare . Acest lucru va rupe relația dintre cele două tabele, iar constrângerea cheii externe nu va permite existența înregistrărilor fără relație.

Restricția se aplică și la modificarea sau ștergerea înregistrărilor. De exemplu, dacă încercați să ștergeți un rând cu numele de familie Petrov, va apărea o eroare de constrângere a cheii străine. Nu puteți șterge înregistrările care au rânduri legate extern. În primul rând, trebuie să ștergeți toate numerele de telefon pentru această intrare și numai după aceea va fi posibilă ștergerea liniei cu numele de familie Petrov.

Când creați o cheie străină, puteți specifica ON DELETE CASCADE sau ON UPDATE CASCADE. În acest caz, dacă ștergeți înregistrarea lui Petrov din tabelul Nume sau modificați identificatorul, atunci toate înregistrările din tabelul Telefoane asociate rândului lui Petrov vor fi actualizate automat. Nu. Nu, trebuie scris cu majuscule: NU faceți niciodată asta. Totul trebuie șters sau modificat manual. Dacă un utilizator șterge accidental o intrare din tabelul Nume, atunci telefoanele corespunzătoare sunt și ele șterse. Nu are rost să creezi o cheie străină dacă jumătate din restricțiile acesteia dispar! Totul trebuie făcut manual și nu se recomandă niciodată schimbarea identificatorilor.

Ștergerea tabelelor în sine ar trebui să înceapă și cu tabelul subordonat, adică Telefoane, și numai atunci puteți șterge tabelul principal cu Nume.

În cele din urmă, vă voi arăta cum să obțineți o potrivire minunată între nume și numere de telefon din două tabele:

SELECTAȚI vcName, vcPhone FROM Nume, Telefoane WHERE Names.idName=Phones.idName

Despre astfel de interogări vom vorbi mai detaliat în Capitolul 2. Deocamdată, am dat un exemplu doar pentru a putea vedea puterea tabelelor aferente.

Un tabel poate conține până la 253 de chei străine, ceea ce este suficient chiar și pentru cele mai complexe baze de date. Personal, a trebuit să lucrez cu baze de date în care numărul de chei externe nu depășea 7 pe tabel. Dacă este mai mult, atunci cel mai probabil baza de date este proiectată incorect, deși există excepții.

Tabelul în sine poate avea, de asemenea, maximum 253 de chei externe. Cheile externe dintr-un tabel sunt mai puțin comune, de obicei nu mai mult de 3. Cel mai adesea, un tabel poate avea multe legături către alte tabele.

O cheie externă poate face referire la același tabel în care este creată. De exemplu, aveți un tabel cu titlurile posturilor într-o organizație, așa cum se arată în Tabelul 1.5. Tabelul este format din trei câmpuri: cheie primară, cheie străină și titlul postului. Orice organizație poate avea multe funcții, dar ar fi destul de logic să-și afișeze numele și structura de subordonare într-un singur tabel. Pentru a face acest lucru, cheia externă trebuie să fie asociată cu cheia primară a tabelului de poziții.

Tabelul 1.5. Tabel cu link intern

Drept urmare, obținem că directorul general are o cheie străină zero, adică. această poziție stă în fruntea tuturor celorlalți. Pentru directorul comercial și directorul de afaceri generale, cheia externă indică rândul directorului general. Aceasta înseamnă că aceste două poziții raportează direct directorului general. Și așa mai departe.

Să vedem cum putem crea toate acestea ca o interogare SQL:

CREATE TABLE Poziții (idPosition int IDENTITY(1,1), idParentPosition int, vcName varchar(30), CONSTRAINT PK_idPosition PRIMARY KEY (idPosition), CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) REFERINȚE Poziții) (idPosition)

După cum puteți vedea, cheia externă face referire pur și simplu la același tabel pe care îl creăm noi. Pe CD, în directorul Chapter1, puteți vedea în fișierul foreign_keys_to_self.sql un exemplu de creare a acestui tabel, completarea lui cu date și afișarea pozițiilor ținând cont de subordonarea acestora. În capitolul următor vom analiza mai detaliat posibilitatea de a lucra cu astfel de tabele.

Relație unu la unu

Până acum, ne-am uitat la relația clasică, când un rând din tabelul de date principal corespunde unui rând din tabelul aferent. Această relație se numește unu-la-mulți. Dar există și alte conexiuni, iar acum ne vom uita la alta - unu la unu, când o înregistrare din tabelul principal este conectată la o înregistrare a altuia. Pentru a implementa acest lucru, este suficient să legați cheile primare ale ambelor tabele. Deoarece cheile primare nu pot fi repetate, în ambele tabele poate fi asociat un singur rând.

Următorul exemplu creează două tabele care au o relație de cheie primară:

CREATE TABLE Nume (idName uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Telefoane (idPhone unicidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT PK_idPhone), (IDPhone PRIMARY), (IDPhone uniqueidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT FK_idPhone CHEIE STRĂINĂ (idPhone) REFERINȚE Nume (idName))

Doar una dintre tabele are nevoie de o cheie străină. Întrucât relația este unul la unu, nu contează în ce tabel să o creezi.

multi la multi

Cea mai complexă relație este multi-la-mulți, unde multe înregistrări dintr-un tabel se potrivesc cu multe înregistrări dintr-un alt tabel. Pentru a implementa acest lucru, două tabele nu sunt suficiente;

Mai întâi trebuie să înțelegem când poate fi folosită o relație multi-la-mulți? Să presupunem că aveți două tabele: o listă cu locuitorii casei și o listă cu numerele de telefon. Un apartament poate avea mai multe numere, ceea ce înseamnă că un nume de familie poate avea două numere de telefon. Se pare că există o relație unu-la-mulți. Pe de altă parte, într-un apartament pot fi două familii (un apartament comun sau doar un chiriaș care folosește telefonul proprietarului), ceea ce înseamnă că legătura dintre telefon și rezident este, de asemenea, una la mulți. Iar varianta cea mai dificilă este să ai două telefoane într-un apartament comun. În acest caz, ambele numere sunt folosite de mai mulți rezidenți ai apartamentului. Deci, se dovedește că „multe” familii pot folosi „multe” telefoane (comunicare multi-la-multe).

Cum să implementezi o relație multi-la-mulți? La prima vedere, acest lucru este imposibil în modelul relațional. Cu aproximativ 10 ani în urmă, am petrecut mult timp căutând diferite opțiuni și, ca rezultat, am creat pur și simplu un tabel care a fost umplut cu date redundante. Dar într-o zi, mi s-a dat o sarcină, datorită căreia a apărut o soluție excelentă din condiții - trebuia să creez două tabele de rezidenți ai apartamentelor și numere de telefon și să implementez doar o cheie primară în ele. Cheile externe nu sunt necesare în acest tabel. Dar legătura dintre mese ar trebui să fie printr-o a treia masă de legătură. La prima vedere, acest lucru este dificil și neclar, dar odată ce înțelegeți această metodă, veți vedea toată puterea acestei soluții.

Tabelele 1.6 și 1.7 prezintă exemple de tabele de nume de familie și, respectiv, de telefon. Și Tabelul 1.8 arată tabelul de legătură.

Tabelul 1.6. Tabelul numelui

Tabelul 1.7. Masa de telefon

Tabelul 1.8. Masa de telefon

Să vedem acum cum va fi logica de căutare a datelor într-o relație multi-la-mulți. Să spunem că trebuie să găsim toate telefoanele care îi aparțin lui Ivanov. Cheia primară a lui Ivanov este egală cu 1. Găsim în tabelul de legătură toate înregistrările pentru care câmpul „Relație cu numele” este egal cu 1. Acestea vor fi înregistrările 1 și 2. În aceste înregistrări în câmpul „Relația cu telefonul” există sunt identificatorii 1 și, respectiv, 2, și Aceasta înseamnă că Ivanov deține numerele din masa telefonică, care sunt situate în rândurile 1 și 2.

Acum să rezolvăm problema inversă - să determinăm cine are acces la numărul de telefon 567575677. Acest număr din tabelul de telefon are cheia 3. Căutăm toate înregistrările din tabelul de legătură, unde în câmpul „Conexiune la telefon” este egal cu 3. Acestea sunt înregistrări cu numerele 4 și 5, care în câmpul „Name Link” conțin valorile 2 și, respectiv, 3. Dacă te uiți acum la tabelul numelor de familie, îi vei vedea pe Petrov și Sidorov la numerele 2 și 3. Aceasta înseamnă că acești doi rezidenți folosesc numărul de telefon 567575677.

Examinați toate cele trei tabele și asigurați-vă că înțelegeți numerele de telefon cărora le aparțin rezidenților și invers. Dacă vezi această legătură, vei înțelege că este simplă cât trei bănuți și o poți implementa rapid în proiectele tale.

CREATE TABLE Nume (idName uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Telefoane (idPhone uniqueidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT PK_idPhone)) (IDPhone IDPhone)) CREATE TABLE LinkTable (idLinkTable unicidentifier DEFAULT NEWID(), idName unicidentifier, idPhone unicidentifier, CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable), CONSTRAINT FK_idPhone FOREIGN KEY (idPhone) REFERINȚE Telefoane (idPhone CONSTRAINT FOREIGN REFERENCE)_Name s (idName ) )

Tabelul de legătură are două chei străine care se leagă la nume și tabelele telefonice și o cheie primară care asigură că înregistrările sunt unice.

Am ales câmpul GUID ca cheie primară, deoarece este mai convenabil pentru rezolvarea acestei probleme. Faptul este că trebuie să inserăm înregistrări în două tabele și în ambele cazuri trebuie să specificăm aceeași cheie. Valoarea GUID poate fi generată și apoi utilizată la inserarea datelor în ambele tabele.

De asemenea, puteți utiliza un câmp care crește automat ca cheie, dar în acest caz problema este puțin mai dificil de rezolvat, sau mai degrabă, este incomod să rezolvați problema. De exemplu, atunci când adăugați un număr de telefon, trebuie mai întâi să introduceți rândul corespunzător în tabel, apoi să îl găsiți, să determinați cheia care a fost atribuită rândului și apoi să faceți conexiunea.

În această etapă ne limităm doar la crearea de tabele, dar în secțiunea 2.8 vom reveni la acest subiect și vom învăța și vom învăța cum să lucrăm cu tabele înrudite. Lucrul cu o relație unu-la-unu și unu-la-mulți nu este foarte diferit, deoarece doar două tabele sunt implicate în această schemă. Relațiile multi-la-mulți sunt puțin mai complicate din cauza tabelului de legături, așa că le vom acoperi separat în Secțiunea 2.27.