Mikä on tietokannan ensisijainen avain? Avaimet-taulukon ensisijaisia ja vieraita avaimia koskevat rajoitukset

Vieras avain on avain, jota käytetään kahden taulukon yhdistämiseen. Sitä kutsutaan joskus myös viiteavaimeksi.

Vieras avain on sarake tai sarakkeiden yhdistelmä, jonka arvot vastaavat toisen taulukon ensisijaista avainta.

Kahden taulukon välinen suhde vastaa toisessa taulukossa olevaa ensisijaista avainta ja toisessa taulukossa vierasavain.

Jos taulukolle on määritetty ensisijainen avain jollekin kentälle, sinulla ei voi olla kahta tietuetta, joilla on sama arvo kyseiselle kentille.

Esimerkki

Katsotaanpa seuraavan kahden taulukon rakennetta.

ASIAKKAAT pöytä

LUO TAULUKKOASIAKKAAT (TUNNUS INT EI NULL, NIMI VARCHAR (20) EI NULL, IKÄ INT EI NULL, OSOITE CHAR (25) , PALKAN DESIMAALI (18, 2), ENSISIJAINEN AVAIN (ID));

TILAUSpöytä

LUO TAULUTILAUKSET (ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT viittaukset ASIAKKAAT(ID), SUMMA kaksinkertainen, PRIMARY KEY (ID));

Jos ORDERS-taulukko on jo luotu, eikä vieraa-avainta ole vielä asetettu, käytetään syntaksia asettamaan vierasavain muuttamalla taulukkoa.

MUUTA TABLETILAUKSIA LISÄÄ ULKOINEN AVAIN (ASIAKKAAN_ID) VIITTEET ASIAKKAAT (ID);

Vieraan avaimen rajoitteen poistaminen

Voit poistaa vieraiden avainten rajoituksen käyttämällä seuraavaa SQL-syntaksia.

ALTER TABLE TILAUKSET PUDOTA ULKOMAAN AVAIN;

Nämä ovat sähköisiä tietovarastoja, joihin päästään yhdellä tai useammalla tietokoneella. Tyypillisesti tietokannat luodaan tallentamaan ja käyttämään tietoja, jotka sisältävät tietoa tietystä aihealueesta, toisin sanoen jostakin ihmisen toiminnan alueesta tai osasta todellista maailmaa.

DBMS ovat ohjelmistotyökaluja tietokantojen luomiseen, täyttämiseen, päivittämiseen ja poistamiseen.

Tietokantaan tallennetun tiedon yksikkö on taulukko. Jokainen taulukko on kokoelma rivejä ja sarakkeita, joissa rivit vastaavat objektin esiintymää, tiettyä tapahtumaa tai ilmiötä ja sarakkeet vastaavat kohteen, tapahtuman tai ilmiön attribuutteja (ominaisuuksia, ominaisuuksia, parametreja). Jokainen rivi sisältää tietoja tietystä tapahtumasta.

Tietokantatermeissä taulukon sarakkeita kutsutaan kentiksi ja sen rivejä tietueiksi.

Yksittäisten tietokantataulukoiden välillä voi olla suhteita, eli edellisen taulukon tiedot voidaan lisätä toiseen. Tietokantoja, joilla on suhteita yksittäisten taulukoiden välillä, kutsutaan relaatioiksi. Sama taulukko voi olla päätaulukko suhteessa yhteen tietokantataulukkoon ja alitaulukko suhteessa toiseen.

Suhteilla yhdistetyt taulukot toimivat vuorovaikutuksessa isäntä-orja -periaatteen mukaisesti. Sama taulukko voi olla yhden tietokantataulukon päätaulukko ja toisen alitaulukko.

Esine on jotain olemassa olevaa ja erotettavissa olevaa, jolla on joukko ominaisuuksia. Objektin ja toisen objektin välinen ero määräytyy tiettyjen ominaisuusarvojen perusteella.

Essence – esineen heijastus henkilön tai tietokoneen muistissa.

Attribuutti – kokonaisuuden minkä tahansa ominaisuuden tietty arvo.

Ala on tietueen yksittäinen elementti, joka tallentaa tietyn attribuutin arvon.

Viestintäkenttä – Tämä on kenttä, jolla kaksi taulukkoa liittyvät toisiinsa.

Ensisijaiset ja toissijaiset avaimet

Jokaisella tietokantataulukolla voi olla ensisijainen avain - tämä on kenttä tai kenttäkokoelma, joka yksilöi tietueen.

Tietokantataulukon ensisijaisen avaimen arvon on oltava yksilöllinen, eli taulukossa ei saa olla kahta tai useampaa tietuetta, joilla on sama perusavaimen arvo.

Ensisijaisten avainten avulla on helpompi luoda suhteita taulukoiden välille. Koska ensisijaisen avaimen on oltava yksilöllinen, kaikkia taulukon kenttiä ei voida käyttää siihen.

Jos taulukossa ei ole kenttiä, joiden arvot ovat yksilöllisiä, perusavaimen luomiseksi siihen lisätään yleensä ylimääräinen numeerinen kenttä, jonka arvot DBMS voi hallita harkintansa mukaan.

Toissijaiset avaimet asetetaan kentillä, joita käytetään usein tietojen haussa tai lajittelussa: toissijaisiin avaimiin rakennetut indeksit auttavat järjestelmää löytämään tarvittavat arvot, jotka on tallennettu vastaaviin kenttiin, paljon nopeammin.

Toisin kuin ensisijaiset avaimet, toissijaisten avainten kentät voivat sisältää ei-ainutlaatuista tietoa.

Taulukoiden väliset relaatiosuhteet

Yksi yhteen. Yksi-yhteen-suhde syntyy, kun yksi päätaulukon tietue vastaa yhtä alitaulukon tietuetta.

Tämä suhde on paljon harvinaisempi kuin yksi-moneen-suhde. Sitä käytetään, jos et halua tietokantataulukon paisuvan toissijaisen taulukon kanssa. Yksi-yhteen-suhde johtaa useisiin lukutoimintoihin, jotta voidaan lukea toisiinsa liittyviä tietoja useista taulukoista, mikä hidastaa vaadittujen tietojen hakua. Lisäksi tietokantoja, jotka sisältävät taulukoita, joissa on yksi-yhteen-suhde, ei voida pitää täysin normalisoituina.

Kuten yksi-moneen-suhde, yksi-yhteen-suhde voi olla joko kova tai pehmeä.

Avaimet ovat relaatiotietokannan peruselementtejä, koska ne muodostavat suhteen taulukkoparin välille ja tarjoavat yksilöllisen tunnisteen jokaiselle taulukon tietueelle. Avaimet ovat tärkeämpiä kuin suhteiden luominen; ne auttavat myös viitteellisen eheyden kanssa, ja ne ovat tärkeä osa taulukkotason eheyttä. Taulukot tallentavat valtavia datapaloja, jotka yleensä kattavat tuhansia tietueita, jotka kaikki ovat lajittelemattomia ja hajanaisia. Tiettyjen tietojen hakeminen näistä useista tietueista voi toisinaan olla vaikeaa tai mahdotonta. Tässä avaimet näkyvät. Tässä tarkastellaan kahta erittäin tärkeää relaatiotietokantaskeema-avainta ja niiden välistä eroa: ensisijainen avain ja vieras avain.

Mikä on ensisijainen avain?

Ensisijainen avain on erityinen avain, joka yksilöi taulukon jokaisen tietueen. Relaatiotietokannassa on erittäin tärkeää, että jokaisella taulukon rivillä on yksilöllinen tunniste, ja ensisijainen avain on yksinkertaisesti se, mitä tarvitset taulukon monikon yksilölliseen tunnistamiseen. Tuple on kokoelma arvoattribuutteja relaatiotietokannassa. Ensisijainen avain voi viitata sarakkeeseen tai sarakkeiden joukkoon relaatiotietokantataulukossa, jota käytetään implisiittisesti tunnistamaan kaikki taulukon tietueet. Ensisijaisen avaimen on oltava yksilöllinen jokaiselle tietueelle, koska se toimii yksilöllisenä tunnisteena, eikä se saa sisältää Null-arvoja. Jokaisessa tietokannassa on oltava yksi ja vain yksi ensisijainen avain.

Mikä on vierasavain?

Vierasavain viittaa tietokantatietueen kenttiin tai kenttien kokoelmaan, joka yksilöi toisen taulukon toisen tietokantatietueen avainkentän. Yksinkertaisesti sanottuna se muodostaa suhteen tietokannan kahdessa eri taulukossa olevien tietueiden välille. Se voi olla taulukon sarake, joka osoittaa ensisijaisen avaimen sarakkeisiin, mikä tarkoittaa, että taulukossa määritetty vierasavain viittaa jonkin muun taulukon ensisijaiseen avaimeen. Linkit ovat tärkeitä relaatiotietokannoissa tietueiden välisten suhteiden luomisessa, joita tarvitaan tietokantojen lajittelussa. Vierailla avaimilla on tärkeä rooli relaatiotietokantojen normalisoinnissa, varsinkin kun taulukot tarvitsevat pääsyn muihin taulukoihin.

Ero ensisijaisen avaimen ja viiteavaimen välillä

Ensisijaisen avaimen ja vieraan avaimen perusteet

Ensisijainen avain on relaatiotietokannan erityinen avain, joka toimii yksilöllisenä tunnisteena jokaiselle tietueelle, mikä tarkoittaa, että se tunnistaa yksilöllisesti jokaisen taulukon rivin/tietueen ja sen arvon on oltava yksilöllinen jokaiselle taulukon riville. Toisaalta vierasavain on yhden taulukon kenttä, joka yhdistää kaksi taulukkoa. Se viittaa sarakkeeseen tai sarakeryhmään, joka yksilöi toisen taulukon tai saman taulukon rivin.

Ensisijaisen avaimen ja viiteavaimen suhde

Ensisijainen avain tunnistaa yksilöllisesti tietueen relaatiotietokantataulukossa, kun taas vierasavain viittaa kenttään taulukossa, joka on toisen taulukon ensisijainen avain. Ensisijaisen avaimen on oltava yksilöllinen ja määritettävässä taulukossa saa olla vain yksi ensisijainen avain, kun taas taulukossa sallitaan useampi kuin yksi vierasavain.

Päällekkäiset ensisijaisen avaimen ja viiteavaimen arvot

Ensisijainen avain on yhdistelmä UNIQUE- ja Not Null -rajoituksia, joten relaatiotietokantataulukon ensisijainen avainkenttä ei voi sallia päällekkäisiä arvoja. Kahdella rivillä ei voi olla päällekkäisiä arvoja ensisijaisen avaimen attribuutille. Toisin kuin ensisijainen avain, vierasavain voi sisältää päällekkäisiä arvoja, ja relaatiotietokannan taulukko voi sisältää useamman kuin yhden vierasavaimen.

NULL ensisijainen avain ja vierasavain

Yksi tärkeimmistä eroista näiden kahden välillä on, että toisin kuin ensisijaiset avaimet, viiteavaimet voivat sisältää myös NULL-arvoja. Relaatiotietokannan taulukossa voi olla vain yksi ensisijainen avain, joka ei ole mitätöitävissä.

Ensisijaisen avaimen ja viiteavaimen väliaikainen taulukko

Ensisijaisen avaimen rajoitus voidaan määrittää implisiittisesti väliaikaisille taulukoille ja niiden muuttujille, kun taas vieraan avaimen rajoitusta ei voida soveltaa paikallisiin tai globaaleihin väliaikaisiin taulukoihin.

Ensisijaisen avaimen ja viiteavaimen poistaminen

Ensisijaisen avaimen arvoa ei voi poistaa päätaulukosta, jota kutsutaan viiteavaimeksi alitaulukossa. Ennen kuin voit pudottaa ylätason taulukon, sinun on ensin pudotettava alatason taulukko. Sitä vastoin vieraan avaimen arvo voidaan poistaa alitaulukosta, vaikka arvo kuuluisi emotaulukon ensisijaiseen avaimeen.

Ensisijainen avain tai viiteavain: vertailutaulukko

Pääavainten yhteenveto

Avaimilla on ratkaiseva rooli tietokantaskeeman olemassaolossa suhteiden luomiseksi taulukoiden välillä ja taulukon sisällä. Avaimet muodostavat suhteita ja varmistavat erityyppisiä eheyttä, erityisesti taulukkotason ja suhdetason eheyttä. Ensinnäkin he uskovat, että taulukko sisältää yksilöllisiä tietueita, ja kenttien, joita käytät taulukoiden välisten suhteiden luomiseen, on sisällettävä vastaavat arvot. Ensisijainen avain ja vierasavain ovat kaksi tärkeintä ja yleisintä avaintyyppiä, joita käytetään relaatiotietokannassa. Ensisijainen avain on erityinen avain, jota käytetään yksilöimään taulukon tietueet, kun taas vierasavainta käytetään kahden taulukon välisen suhteen luomiseen. Molemmat ovat rakenteeltaan identtisiä, mutta niillä on eri roolit relaatiotietokantaskeemassa.

Ja niin, hiljaa, lähestyimme erittäin tärkeää aihetta - ensisijaisia ja vieraita avaimia. Jos ensimmäistä käyttävät melkein kaikki, jälkimmäiset jätetään jotenkin huomiotta. Mutta turhaan. Vieraat avaimet eivät ole ongelma, ne ovat todellinen apu tiedon eheydessä.

1.2.5. Pääavain

Olemme jo puhuneet paljon keskeisistä aloista, mutta emme ole koskaan käyttäneet niitä. Mielenkiintoisinta on, että kaikki toimi. Tämä on Microsoft SQL Server- ja MS Access -tietokantojen etu tai ehkä haitta. Tämä temppu ei toimi Paradox-taulukoissa ja ilman avainkenttää taulukko on vain luku -tilassa.

Avaimet ovat jossain määrin rajoitteita, ja niitä voitaisiin tarkastella yhdessä CHECK-käskyn kanssa, koska ilmoitus tapahtuu samalla tavalla ja jopa käyttää CONSTRAINT-käskyä. Tarkastellaan tätä prosessia esimerkin avulla. Tätä varten luomme taulukon kahdesta kentästä "guid" ja "vcName". Tämä asettaa "guid"-kentän ensisijaiseksi avaimeksi:

LUO TAULUKKO Globally_Unique_Data (guid yksilöllinen tunniste OLETUS NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (Guid))

Parasta tässä on CONSTRAINT-linja. Kuten tiedämme, tämän avainsanan jälkeen tulee rajoitteen nimi, eikä avaimen ilmoitus ole poikkeus. Ensisijaisen avaimen nimeämiseksi suosittelen käyttämään nimeä, kuten PK_nimi, jossa nimi on sen kentän nimi, josta tulee ensisijainen avain. Lyhenne PK tulee sanoista Primary Key.

Tämän jälkeen rajoituksissa käyttämämme CHECK-avainsanan sijaan on PRIMARY KEY -operaattori. Tämä osoittaa, että emme tarvitse tarkistusta, vaan ensisijaisen avaimen. Suluissa on merkitty yksi tai useampi avaimen muodostava kenttää.

Muista, että kahdella rivillä ei voi olla samaa arvoa avainkentässä, jossa ensisijaisen avaimen rajoite on identtinen yksilöllisen rajoitteen kanssa. Tämä tarkoittaa, että jos teet sukunimen tallennuskentän ensisijaiseksi avaimeksi, tällaiseen taulukkoon ei voi kirjoittaa kahta Ivanovia eri nimellä. Tämä rikkoo ensisijaisen avaimen rajoitusta. Tästä syystä avaimet ovat rajoituksia ja ne ilmoitetaan samalla tavalla kuin TARKISTA rajoitus. Mutta tämä ei päde vain primaari- ja toissijaisiin avaimiin, joilla on ainutlaatuisuus.

Tässä esimerkissä ensisijainen avain on yksilöllinen tunniste (GUID) -tyyppinen kenttä. Tämän kentän oletusarvo on NEWID-palvelinproseduurin tulos.

Huomio

Taulukolle voidaan luoda vain yksi ensisijainen avain

Esimerkkien yksinkertaistamiseksi avaimena on suositeltavaa käyttää numeerista tyyppiä, ja jos tietokanta sallii, on parempi, jos se on "autoincrement"-tyyppinen (automaattisesti kasvava/pienentävä luku). MS SQL Serverissä tämä kenttä on IDENTITY, ja MS Accessissa se on "laskuri"-tyyppinen kenttä.

Seuraava esimerkki näyttää kuinka luodaan tuotetaulukko, jonka ensisijaisena avaimena on automaattisesti kasvava kokonaislukukenttä:

LUO TAULUKKO Tuotteet (id int IDENTITY(1, 1), product varchar(50), hinta raha, määrä numeerinen (10, 2), CONSTRAINT PK_id PRIMARY KEY (id))

Juuri tämäntyyppisiä avaimia käytämme useimmiten, koska avainkenttään tallennetaan numeroita, jotka on helppo ymmärtää ja joiden kanssa työskentely on helpompaa ja visuaalisempaa.

Ensisijainen avain voi koostua useammasta kuin yhdestä sarakkeesta. Seuraava esimerkki luo taulukon, jossa kentät "id" ja "Product" muodostavat ensisijaisen avaimen, mikä tarkoittaa, että molempiin kenttiin luodaan yksilöllinen indeksi:

LUO TAULUKKO Tuotteet1 (id int IDENTITY(1, 1), Product varchar(50), Hinta raha, Määrä numeerinen (10, 2), CONSTRAINT PK_id PRIMARY KEY (ID, [Tuotteen nimi]))

Hyvin usein ohjelmoijat luovat tietokannan, jossa on avainkenttä kokonaisluvun muodossa, mutta samalla tehtävässä sanotaan selvästi, että tiettyjen kenttien on oltava yksilöllisiä. Miksei heti luoda ensisijaista avainta niistä kentistä, joiden täytyy olla yksilöllisiä, eikä tähän ongelmaan tarvitse luoda erillisiä ratkaisuja.

Monisarakkeisen ensisijaisen avaimen ainoa haittapuoli on suhteiden luomisen ongelma. Täällä sinun on päästävä eroon eri menetelmillä, mutta ongelma voidaan silti ratkaista. Sinun tarvitsee vain kirjoittaa yksilöllinen tunniste -tyyppinen kenttä ja muodostaa yhteys käyttämällä sitä. Kyllä, tässä tapauksessa saamme yksilöllisen ensisijaisen avaimen ja kentän, jonka tyyppi on yksilöllinen tunniste, mutta tämä redundanssi ei tästä syystä ole suurempi kuin sama taulukko, jossa ensisijainen avain on yksilöllinen tunniste, ja kenttiin on asetettu yksilöllisyyden rajoitus. olla ainutlaatuinen. Mitä valita? Riippuu tietystä tehtävästä ja siitä, minkä kanssa työskentelet mukavammin.

1.2.6. Ulkoinen avain

Vieras avain on myös CONSTRAINT-rajoite ja edustaa kahden taulukon välistä suhdetta. Oletetaan, että sinulla on kaksi pöytää:

Nimet – sisältää ihmisten nimet ja koostuu tunnistekentistä (avainkenttä), nimi.
Phones on puhelintaulukko, joka koostuu tunnisteesta (avainkenttä), vierasavaimesta nimitaulukkoon yhdistämistä varten ja merkkijonokentästä puhelinnumeron tallentamista varten.

Yhdellä henkilöllä voi olla useita puhelimia, joten jaoimme tietovaraston eri taulukoihin. Kuva 1.4 näyttää visuaalisesti kahden taulukon välisen suhteen. Jos olet jo työskennellyt linkitettyjen taulukoiden kanssa, tämä riittää sinulle. Jos kuulet yhteyksistä ensimmäistä kertaa, yritämme tarkastella ongelmaa tarkemmin.

Otetaan esimerkiksi kolmen hengen pöytä. Taulukko 1.3 näyttää "Nimet"-taulukon sisällön. Rivejä on vain kolme ja jokaisella on oma ainutlaatuinen pääavain. Ainutlaatuisuuden vuoksi, kun luomme taulukkoa, teemme avaimesta automaattisesti kasvavan kentän.

Taulukko 1.3 Nimet-taulukon sisältö

Taulukko 1.4. Puhelimet-taulukon sisältö

Taulukko 1.4 sisältää viisi puhelinnumeroa. Pääavainkenttä sisältää myös ainutlaatuisen pääavaimen, jota voidaan myös automaattisesti kasvattaa. Toissijainen avain on suhde Nimet-taulukon ensisijaiseen avaimeen. Miten tämä yhteys toimii? Petrovilla on numero 1 ensisijaisena avaimena Nimet-taulukossa Puhelimet-taulukossa toissijaisesta avaimesta etsitään numero 1 ja saadaan Petrovin puhelinnumerot. Sama koskee muita merkintöjä. Visuaalisesti kytkentä näkyy kuvassa 1.5.

Tämän tyyppinen tietojen tallennus on erittäin kätevää. Jos ei olisi mahdollista luoda aiheeseen liittyviä taulukoita, niin Nimet-taulukossa meidän pitäisi syöttää kaikki puhelinnumerot yhteen kenttään. Tämä on hankalaa käytön, ylläpidon ja tiedonhaun kannalta.

Voit luoda useita Nimet-kenttiä taulukkoon, mutta herää kysymys - kuinka monta. Yhdellä henkilöllä voi olla vain 1 puhelin, mutta minulla on esimerkiksi 3, työpuhelinta lukuun ottamatta. Suuri määrä kenttiä johtaa tietojen redundanssiin.

Voit luoda erillisen rivin sukunimellä jokaiselle puhelimelle Nimet-taulukkoon, mutta tämä on helppoa vain niin yksinkertaisessa esimerkissä, kun sinun tarvitsee syöttää vain sukunimi ja voit helposti tehdä useita merkintöjä Petroville usealla puhelimella numeroita. Entä jos kenttiä on 10 tai 20? Joten kahden vieraalla avaimella linkitetyn taulukon luominen voidaan nähdä Listauksessa 1.6.

Listaus 1.6. Vierasavaimella linkitettyjen taulukoiden luominen

CREATE TABLE Nimet (idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Puhelimet (idPhone int IDENTITY(1,1), idName int, vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone), CONSTRAINT FK_idName FOREIGN KEY (idName) VIITTEET Nimet (idName))

Tarkista listauksen sisältö huolellisesti. Se on varsin mielenkiintoinen, koska se käyttää joitain operaattoreita, joita olemme jo käsitelleet, ja lisäesimerkki olisi hyödyllinen. Molemmille taulukoille luodaan avainkenttä, joka tulee ensin, on tyyppiä int ja kasvaa automaattisesti alkaen 1:stä yhden askelin. Avainkenttä tehdään pääavaimeksi CONSTRAINT-rajoitteen avulla.

Puhelimet-taulukon kuvauksessa viimeisellä rivillä on meille uusi ilmoitus, nimittäin vierasavaimen ilmoitus FORIGN KEY -operaattorilla. Kuten näet, tämä on myös rajoitus, ja vähän myöhemmin näet miksi. Taulukkokenttä, joka pitäisi linkittää toiseen taulukkoon, on merkitty suluissa. Tämän jälkeen tulee avainsana REFERENCES (linkki), sen taulukon nimi, johon yhteyden tulee olla (Names) ja suluissa kentän nimi ("idName"). Näin ollen olemme tehneet yhteyden, joka näkyy kuvassa 1.4.

Huomio!

Vieras avain voi viitata vain toisen taulukon ensisijaiseen avaimeen tai ainutlaatuiseen rajoitteeseen. Tämä tarkoittaa, että REFERENCES-avainsanan jälkeen tulee olla taulukon nimi ja suluissa voidaan määrittää vain ensisijainen avain tai kenttä, jolla on AINOASTAINEN rajoitus. Muita kenttiä ei voi määrittää.

Jos nyt voit täyttää taulukot tiedoilla. Seuraavat kolme joukkuetta lisäävät kolme nimeä, jotka näimme taulukossa 1.3:

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

Jos olet jo työskennellyt SQL:n kanssa, voit lisätä merkintöjä puhelintaulukkoon. Jätän nämä komennot pois, mutta näet ne ulko_avaimet.sql-tiedostossa CD-levyn Chapter1-hakemistossa.

Tehtävämme on nyt nähdä, mitä vieraan avaimen rajoittavat toiminnot ovat, selvitetään se. Olemme määrittäneet eksplisiittisen suhteen kahden kentän välille eri taulukoissa. Jos yrität lisätä puhelintaulukkoon tietueen, jonka "idName"-kentässä on tunniste, jota ei ole sukunimien taulukon samannimisessä kentässä (nimi voidaan vaihtaa toiseksi), tapahtuu virhe. . Tämä katkaisee kahden taulukon välisen suhteen, ja vieraan avaimen rajoitus ei salli tietueiden olemassaoloa ilman suhdetta.

Rajoitus koskee myös tietueiden muuttamista tai poistamista. Jos esimerkiksi yrität poistaa rivin sukunimellä Petrov, tapahtuu vieraan avaimen rajoitusvirhe. Et voi poistaa tietueita, joissa on ulkoisesti liittyviä rivejä. Ensin sinun on poistettava kaikki tämän merkinnän puhelinnumerot, ja vasta sen jälkeen on mahdollista poistaa rivi sukunimellä Petrov.

Kun luot viiteavaimen, voit määrittää POISTA CASCADE tai UPDATE CASCADE. Jos tässä tapauksessa poistat Petrovin tietueen Nimet-taulukosta tai vaihdat tunnistetta, kaikki Petrovin riviin liittyvät Puhelimet-taulukon tietueet päivitetään automaattisesti. Ei koskaan. Ei, se on kirjoitettava isoilla kirjaimilla: ÄLÄ KOSKAAN tee tätä. Kaikki on poistettava tai muutettava manuaalisesti. Jos käyttäjä vahingossa poistaa merkinnän Nimet-taulukosta, myös vastaavat puhelimet poistetaan. Ei ole mitään järkeä luoda vierasavainta, jos puolet sen rajoituksista katoaa! Kaikki on tehtävä manuaalisesti, eikä tunnisteiden vaihtamista koskaan suositella.

Itse taulukoiden poistamisen tulisi myös alkaa alataulukosta eli Puhelimet, ja vasta sitten voit poistaa pää Nimet -taulukon.

Lopuksi näytän sinulle, kuinka saat kauniisti yhteen nimet ja puhelinnumerot kahdesta taulukosta:

SELECT vcName, vcPhone FROM Nimet, Phones WHERE Names.idName=Phones.idName

Puhumme tällaisista kyselyistä tarkemmin luvussa 2. Toistaiseksi annoin esimerkin vain, jotta voit nähdä toisiinsa liittyvien taulukoiden tehon.

Taulukko voi sisältää jopa 253 vierasavainta, mikä riittää monimutkaisimmillekin tietokantoille. Henkilökohtaisesti jouduin työskentelemään tietokantojen kanssa, joissa vieraiden avainten määrä ei ylittänyt 7:ää taulukkoa kohden. Jos se on enemmän, tietokanta on todennäköisesti suunniteltu väärin, vaikka poikkeuksiakin on.

Taulukossa itsessään voi myös olla korkeintaan 253 vierasavainta. Taulukossa olevat vierasavaimet ovat harvinaisempia, yleensä enintään 3. Useimmiten taulukossa voi olla monia linkkejä muihin taulukoihin.

Vierasavain voi viitata samaan taulukkoon, jossa se on luotu. Sinulla on esimerkiksi taulukko työnimikkeistä organisaatiossa, kuten taulukossa 1.5 näkyy. Taulukko koostuu kolmesta kentästä: ensisijainen avain, viiteavain ja tehtävänimike. Jokaisella organisaatiolla voi olla useita tehtäviä, mutta olisi varsin loogista näyttää niiden nimet ja alaisuusrakenne yhdessä taulukossa. Tätä varten vierasavain on liitettävä paikkataulukon ensisijaiseen avaimeen.

Taulukko 1.5. Taulukko sisäisellä linkillä

Tuloksena saamme, että pääjohtajalla on nolla vierasavain, ts. tämä asema on kaikkien muiden kärjessä. Kaupalliselle johtajalle ja pääjohtajalle vieras avain osoittaa pääjohtajan riville. Tämä tarkoittaa, että nämä kaksi tehtävää raportoivat suoraan toimitusjohtajalle. Ja niin edelleen.

Katsotaanpa, kuinka voimme luoda kaiken tämän SQL-kyselynä:

LUO TAULUKKO Positiot (idPosition int IDENTITY(1,1), idParentPosition int, vcName varchar(30), CONSTRAINT PK_idPosition PRIMARY KEY (idPosition), CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) idParentPosition.

Kuten näet, vierasavain yksinkertaisesti viittaa samaan taulukkoon, jota olemme luomassa. CD-levyllä Chapter1-hakemistossa on tiedostossa Foreign_keys_to_self.sql esimerkki tämän taulukon luomisesta, sen täyttämisestä tiedoilla ja paikkojen näyttämisestä niiden alisteisuus huomioon ottaen. Seuraavassa luvussa tarkastelemme mahdollisuutta työskennellä tällaisten taulukoiden kanssa yksityiskohtaisemmin.

Yksi suhde yhteen

Toistaiseksi olemme tarkastelleet klassista suhdetta, jolloin yksi päätietotaulukon rivi vastaa yhtä riviä liittyvästä taulukosta. Tätä suhdetta kutsutaan yksi-moneen. Mutta on muitakin yhteyksiä, ja nyt tarkastellaan toista - yksi yhteen, kun yksi päätaulukon tietue on yhdistetty toisen tietueeseen. Tämän toteuttamiseksi riittää molempien taulukoiden ensisijaisten avainten linkittäminen. Koska ensisijaisia avaimia ei voi toistaa, molemmissa taulukoissa voidaan yhdistää vain yksi rivi.

Seuraava esimerkki luo kaksi taulukkoa, joilla on ensisijainen avainsuhde:

LUO TAULUKKO Nimet (idName yksilöllinen tunniste OLETUS NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Puhelimet (idPhone yksilöllinen tunniste DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINTIDP (CONSIMARYIDhone) RAJOITE FK_idPhone ULKOAvain (idPhone) VIITTEET Nimet (idName))

Vain yksi taulukoista tarvitsee vierasavaimen. Koska suhde on yksi yhteen, ei ole väliä missä taulukossa se luodaan.

monista moniin

Monimutkaisin suhde on monta moneen, jossa monet yhden taulukon tietueet vastaavat monia toisen taulukon tietueita. Tämän toteuttamiseksi kaksi taulukkoa ei riitä.

Ensinnäkin meidän on ymmärrettävä, milloin moni-moneen-suhdetta voidaan käyttää? Oletetaan, että sinulla on kaksi taulukkoa: luettelo talon asukkaista ja luettelo puhelinnumeroista. Yhdessä asunnossa voi olla useampi kuin yksi numero, mikä tarkoittaa, että yhdellä sukunimellä voi olla kaksi puhelinnumeroa. Osoittautuu, että on olemassa yksi-moneen suhde. Toisaalta yhdessä asunnossa (yhteisasunto tai vain vuokralainen, joka käyttää omistajan puhelinta) voi olla kaksi perhettä, mikä tarkoittaa, että puhelimen ja asukkaan välinen yhteys on myös yksi moniin. Ja vaikein vaihtoehto on olla kaksi puhelinta yhteisessä asunnossa. Tässä tapauksessa useat asunnon asukkaat käyttävät molempia numeroita. Joten käy ilmi, että "monet" perheet voivat käyttää "monia" puhelimia (monien välinen viestintä).

Kuinka toteuttaa moni-moneen-suhde? Ensi silmäyksellä tämä on mahdotonta relaatiomallissa. Noin 10 vuotta sitten vietin pitkän aikaa eri vaihtoehtojen etsimisessä ja tuloksena loin yksinkertaisesti yhden taulukon, joka oli täynnä redundantteja tietoja. Mutta eräänä päivänä minulle annettiin yksi tehtävä, jonka ansiosta ehdoista syntyi erinomainen ratkaisu - minun piti luoda kaksi taulukkoa asunnon asukkaista ja puhelinnumeroista ja toteuttaa niihin vain ensisijainen avain. Vieraita avaimia ei tarvita tässä taulukossa. Pöytien välisen yhteyden tulisi kuitenkin olla kolmannen, yhdistävän pöydän kautta. Ensi silmäyksellä tämä on vaikeaa ja epäselvää, mutta kun ymmärrät tämän menetelmän, näet tämän ratkaisun täyden tehon.

Taulukoissa 1.6 ja 1.7 on esimerkkejä sukunimi- ja puhelinnumerotaulukoista. Ja Taulukko 1.8 näyttää linkitystaulukon.

Taulukko 1.6. Sukunimitaulukko

Taulukko 1.7. Puhelinpöytä

Taulukko 1.8. Puhelinpöytä

Katsotaan nyt, millainen datahakulogiikka tulee olemaan monta moneen -suhteessa. Oletetaan, että meidän on löydettävä kaikki Ivanoville kuuluvat puhelimet. Ivanovin ensisijainen avain on yhtä suuri kuin 1. Löydämme linkitystaulukosta kaikki tietueet, joiden "Suhde nimeen" -kenttä on yhtä suuri kuin 1. Nämä ovat tietueita 1 ja 2. Näissä tietueissa "Suhde puhelimeen" -kentässä on ovat tunnisteet 1 ja 2, ja Tämä tarkoittaa, että Ivanov omistaa puhelintaulukon numerot, jotka sijaitsevat riveillä 1 ja 2.

Ratkaistaan nyt käänteinen ongelma - selvitetään, kenellä on pääsy puhelinnumeroon 567575677. Tällä puhelinnumerotaulukon numerolla on avain 3. Etsimme kaikki tietueet linkitystaulukosta, jossa "Puhelinyhteys"-kentässä se on yhtä kuin 3. Nämä ovat tietueita numeroilla 4 ja 5, jotka "Nimilinkki" -kentässä sisältävät arvot 2 ja 3. Jos katsot nyt sukunimitaulukkoa, näet Petrovin ja Sidorovin numeroissa 2 ja 3. Tämä tarkoittaa, että nämä kaksi asukasta käyttävät puhelinnumeroa 567575677.

Tarkista kaikki kolme taulukkoa ja varmista, että ymmärrät, mitkä puhelinnumerot kuuluvat mille asukkaalle ja päinvastoin. Jos näet tämän yhteyden, ymmärrät, että se on niin yksinkertaista kuin kolme penniä ja voit nopeasti toteuttaa sen projekteissasi.

LUO TAULUKKO Nimet (idName yksilöllinen tunniste OLETUS NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Puhelimet (idPhone yksilöllinen tunniste OLETUSUUSI(), vcPhone varchar(10), CONSTRAINTHONIDP(10) CONSTRAINTHONID CREATE TABLE LinkTable (idLinkTable-yksilöllinen tunniste DEFAULT NEWID(), idName yksilöllinen tunniste, idPhone yksilöllinen tunniste, CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable), CONSTRAINT FK_idPhone FOREIGN KEY (CONSTRAINT FK_idPhone FOREIGN KEY) N KEY (idName REF) ERENCES Nimet (idName ) )

Linkitystaulukossa on kaksi vierasavainta, jotka linkittävät nimiin ja puhelinnumerotaulukoihin, ja yksi ensisijainen avain, joka varmistaa tietueiden yksilöllisyyden.

Valitsin GUID-kentän ensisijaiseksi avaimeksi, koska se on kätevämpi ratkaista tämän ongelman. Tosiasia on, että meidän on lisättävä tietueita kahteen taulukkoon ja molemmissa tapauksissa meidän on määritettävä sama avain. GUID-arvo voidaan luoda ja käyttää sitten lisättäessä tietoja molempiin taulukoihin.

Voit myös käyttää avaimena automaattisesti kasvavaa kenttää, mutta tässä tapauksessa ongelma on hieman vaikeampi ratkaista, tai pikemminkin ongelman ratkaiseminen on hankalaa. Kun esimerkiksi lisäät puhelinnumeroa, sinun on ensin lisättävä vastaava rivi taulukkoon, löydettävä se, määritettävä riville määritetty avain ja muodostettava sitten yhteys.

Tässä vaiheessa rajoitamme vain taulukoiden luomiseen, mutta osiossa 2.8 palaamme tähän aiheeseen ja opimme työskentelemään vastaavien taulukoiden kanssa. Yksi-yhteen- ja yksi-moneen-suhteiden käyttäminen ei ole kovin erilaista, koska tässä järjestelmässä on vain kaksi taulukkoa. Useat moneen -suhteet ovat hieman monimutkaisempia linkitystaulukon vuoksi, joten käsittelemme sitä erikseen osiossa 2.27.

Viimeisin päivitys: 07/02/2017

Tietokannat voivat sisältää taulukoita, jotka on yhdistetty eri linkeillä. Suhde edustaa assosiaatiota erityyppisten entiteettien välillä.

Kun valitset suhdetta, valitse pää- tai ylätaulukko (ensisijainen avaintaulukko / päätaulukko) ja riippuvainen alitaulukko (vieraan avaintaulukko / alitaulukko). Alataulukko riippuu päätaulukosta.

Vieraita avaimia käytetään viestinnän järjestämiseen. Vieras avain edustaa yhtä tai useampaa saraketta yhdestä taulukosta, joka on myös mahdollinen avain toisesta taulukosta. Vierasavaimen ei tarvitse vastata päätaulukon ensisijaista avainta. Vaikka pääsääntöisesti riippuvaisen taulukon vierasavain osoittaa päätaulukon ensisijaiseen avaimeen.

Taulukoiden väliset suhteet ovat seuraavan tyyppisiä:

Yksi yhteen

Yksi moniin

monista moniin(Monet moniin)

Yksi yhteen kommunikaatio

Tämän tyyppistä yhteyttä ei löydy usein. Tässä tapauksessa yhden entiteetin objekti voidaan liittää vain yhteen toisen entiteetin objektiin. Esimerkiksi joillakin sivustoilla käyttäjällä voi olla vain yksi blogi. Eli syntyy suhde: yksi käyttäjä - yksi blogi.

Usein tämäntyyppinen suhde sisältää yhden suuren taulukon jakamisen useisiin pieniin. Ensisijainen ylätaulukko sisältää tässä tapauksessa edelleen usein käytettyjä tietoja, kun taas lapsiriippuvainen taulukko tallentaa tyypillisesti tiedot, joita käytetään harvemmin.

Tässä suhteessa riippuvan taulukon ensisijainen avain on samalla vierasavain, joka viittaa päätaulukon ensisijaiseen avaimeen.

Esimerkiksi Käyttäjät-taulukko edustaa käyttäjiä, ja siinä on seuraavat sarakkeet:

UserId (tunnus, ensisijainen avain)

Nimi (käyttäjänimi)

Ja Blogit-taulukko edustaa käyttäjien blogeja, ja siinä on seuraavat sarakkeet:

BlogId (tunniste, ensisijainen ja viiteavain)

Nimi (blogin nimi)

Tässä tapauksessa BlogId-sarake tallentaa arvon UserId-sarakkeesta käyttäjätaulukosta. Toisin sanoen BlogId-sarake toimii sekä ensisijaisena että vieraana avaimena.

Yksi suhde moneen

Tämä on yleisin yhteystyyppi. Tämän tyyppisessä suhteessa useat alatason taulukon rivit riippuvat yhdestä ylätason taulukon rivistä. Esimerkiksi yhdessä blogissa voi olla useita artikkeleita. Tässä tapauksessa blogitaulukko on vanhempi ja artikkelitaulukko lapsi. Eli yksi blogi - monia artikkeleita. Tai toinen esimerkki, useat jalkapalloilijat voivat pelata jalkapallojoukkueessa. Ja samaan aikaan yksi jalkapalloilija voi pelata vain yhdessä joukkueessa kerrallaan. Eli yksi joukkue - monta pelaajaa.

Otetaan esimerkiksi taulukko nimeltä Artikkelit, joka edustaa blogiartikkeleita ja jossa on seuraavat sarakkeet:

Artikkelitunnus (tunnus, ensisijainen avain)

BlogId (vieraan avain)

Otsikko (artikkelin otsikko)

Teksti (artikkeliteksti)

Tässä tapauksessa artikkelitaulukon BlogId-sarake tallentaa arvon blogitaulukon BlogId-sarakkeesta.

monesta moneen suhdetta

Tämän tyyppisellä suhteella yksi taulukon A rivi voidaan liittää useisiin riveihin taulukosta B. Yksi rivi taulukosta B puolestaan voidaan liittää useisiin riveihin taulukosta A. Tyypillinen esimerkki ovat opiskelijat ja kurssit: yksi opiskelija voi suorittaa useita kursseja, ja vastaavasti useita opiskelijoita voi ilmoittautua yhdelle kurssille.

Toinen esimerkki ovat artikkelit ja tunnisteet: yhdelle artikkelille voidaan määrittää useita tunnisteita ja useille artikkeleille yksi tunniste.

Mutta SQL Serverissä tietokantatasolla emme voi muodostaa suoraa useista moneen -suhdetta kahden taulukon välille. Tämä tehdään ylimääräisen esitystaulukon kautta. Joskus tämän esitystaulukon tiedot edustavat erillistä kokonaisuutta.

Esimerkiksi artikkelien ja tunnisteiden tapauksessa olkoon Tunnisteet-taulukko, jossa on kaksi saraketta:

TagId(tunniste, ensisijainen avain)

Teksti (tunnisteteksti)

Olkoon myös välitaulukko ArticleTags, jossa on seuraavat kentät:

TagId (tunniste, ensisijainen ja viiteavain)

ArticleIdId (tunniste, ensisijainen ja viiteavain)

Teknisesti saamme kaksi yksi-moneen -suhdetta. Artikkelintunnisteet-taulukon TagId-sarake viittaa TagId-taulukon TagId-sarakkeeseen. Ja ArticleId-sarake ArticleTags-taulukosta viittaa Artikkelit-taulukon ArticleId-sarakkeeseen. Toisin sanoen ArticleTags-taulukon TagId- ja ArticleId-sarakkeet edustavat yhdistettyä ensisijaista avainta ja ovat myös vieraita avaimia suhteelle Artikkelit- ja Tunnisteet-taulukoiden kanssa.

Viitetietojen eheys

Ensisijaisia ja vieraita avaimia vaihdettaessa tulee huomioida seuraava seikka: viitetietojen eheys(viittaus eheys). Sen perusideana on kaksi taulukkoa tietokannassa, jotka tallentavat samat tiedot johdonmukaisuuden säilyttämiseksi. Tietojen eheys edustaa oikein rakennettuja taulukoiden välisiä suhteita ja niiden välisiä oikeita linkkejä. Missä tapauksissa tietojen eheys voidaan loukata:

Poistopoikkeama(poistopoikkeama). Tapahtuu, kun rivi poistetaan päätaulukosta. Tässä tapauksessa riippuvaisen taulukon vierasavain jatkaa viittausta poistettuun riviin päätaulukosta

Asennuspoikkeama(syöttöhäiriö). Tapahtuu, kun rivi lisätään riippuvaiseen taulukkoon. Tässä tapauksessa riippuvaisen taulukon vierasavain ei vastaa minkään päätaulukon rivin ensisijaista avainta.

Päivitys anomaliat(päivityspoikkeama). Tällaisella poikkeavalla saman taulukon useat rivit voivat sisältää tietoja, jotka kuuluvat samaan objektiin. Kun muutat tietoja yhdellä rivillä, se voi olla ristiriidassa toisen rivin tietojen kanssa.

Poistopoikkeama

Poistopoikkeaman ratkaisemiseksi sinun on asetettava vierasavaimelle toinen kahdesta rajoituksesta:

Jos riippuvaisen taulukon rivi vaatii välttämättä rivin päätaulukosta, vierasavaimelle asetetaan kaskadipoisto. Eli kun rivi poistetaan päätaulukosta, siihen liittyvät rivit poistetaan riippuvasta taulukosta.

Jos riippuvaisen taulukon rivi ei salli suhdetta päätaulukon riviin (eli tällainen suhde on valinnainen), vierasavain asetetaan arvoon NULL, kun vastaava rivi poistetaan päätaulukosta. Vierasavaimen sarakkeen on oltava tyhjä.

Asennuspoikkeama

Jotta lisäyspoikkeama voidaan ratkaista lisättäessä tietoja riippuvaan taulukkoon, vierasavainta edustavan sarakkeen on oltava tyhjä. Ja näin ollen, jos lisätyllä objektilla ei ole yhteyttä päätaulukkoon, vierasavaimen sarake sisältää NULL-arvon.