MySQL: Näites kasutatud “võõrvõtmeid”

Allpool kuvame mõned lihtsad näited, kuidas seadistada võõraid võtmeid ja kuidas need MySQL-is töötavad. Meie näidetes viidame vanemate ja laste tabelitele allpool. Tabelid sildiga “Klient” ja “Kontakt” tähistavad vastavalt vanemate ja laste tabeleid.


MySQL-is on võõrvõtmete määratlemiseks põhimõtteliselt kaks võimalust:

  1. Võõrklahvide määratlemine CREATE TABLE abil
  2. Võõrklahvide määratlemine ALTER TABLE abil

Lisaks nendele näidetele oleme koos näidetega arutanud ka välismaiste võtmepiirangute ja nende vormindamise kohta.

Igaks juhuks – mis on võõras võti?

Võõras võti on tabelis olev väli (või väljade komplekt), mis identifitseerib unikaalselt mõne teise tabeli rea. Tabelit, milles võõrvõti on määratletud, nimetatakse “alamtabeliks” ja see (sageli) viitab vanemtabeli peamisele võtmele.MySQL võõra võtme näited

Võõrvõti piiranguid saab seejärel kasutada andmete määratlemiseks, kuidas kahe tabeli vahel andmete terviklikkust jõustatakse (nt kui tabelirida kustutatakse või värskendatakse).

Kui seda õigesti teha, säilitatakse primaarvõtme ja võõrvõtme vaheline seos alati, nii et teie andmebaasi ei jäeta kunagi alamtabelis orvuks jäetud kirjeid (nt eemaldatud kliendiga seotud ülejäänud kontaktteavet). Järgmine lihtsustatud (ja mitte täielikult normaliseeritud) kliendiandmebaasi näide aitab meil neid võõraid võtmekontseptsioone visualiseerida.

1. Välisvõtmete määratlemine CREATE TABLE abil

Minge MySQL-i konsooli, kirjutades käskluse mysql õigete kasutaja- ja parooliargumentidega. Vajadusel tippige lisateabe saamiseks “man mysql”.

1 mysql -u -p

Looge andmebaas ja hakake seda kasutama:

1
2

CREATE DATABASE testdb;

kasuta testdb;

Tabelite loomine

Nüüd looge kaks tabelit:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE TABLE klient (

  id INT EI OLE MITTE AUTO_INCREMENT,

  eesnimi varchar (50) EI OLE NULL,

  perekonnanimi varchar (50) EI NULL,

  PRIMARY KEY (id)

) MOOTOR = INNODB;

 

CREATE TABLE kontakt (

  id INT,

  klient_id INT,

  info varchar (50) EI NULL,

  tüüp varchar (50) EI NULL,

  INDEX par_ind (kliendi_id),

  CONSTRAINT fk_customer VÄLISVÕTM (kliendi_id)

  Viited kliendi (id)

  KASKADE KUSTUTAMISEL

  UUENDATUD KASKADI

) MOOTOR = INNODB;

Tabeli struktuuri kontrollimine

Vaatame välja, kuidas meie andmebaasi struktuur välja näeb:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

NÄITA TABELI;

+——————+

| Tables_in_testdb |

+——————+

| kontakt |

| klient |

+——————+

 

KIRJELDA klient;

+———— +———— + +— + +–– +——— +—————-+

| Väli | Tüüp | Null | Võti | Vaikimisi | Lisa |

+———— +———— + +— + +–– +——— +—————-+

| id | int (11) | EI | PRI | NULL | automaatne lisamine |

| eesnimi | varchar (50) | EI | | NULL | |

| perekonnanimi | varchar (50) | EI | | NULL | |

+———— +———— + +— + +–– +——— +—————-+

 

KIRJELDAV kontakt;

+————- +———— +—— + —— +——— +——-+

| Väli | Tüüp | Null | Võti | Vaikimisi | Lisa |

+————- +———— +—— + —— +——— +——-+

| id | int (11) | JAH | | NULL | |

| klient_id | int (11) | JAH | MUL | NULL | |

| info | varchar (50) | EI | | NULL | |

| tüüp | varchar (50) | EI | | NULL | |

+————- +———— +—— + —— +——— +——-+

Pange tähele MUL-i väärtust kontakttabeli veerus Key. See ütleb meile, et väli customer_id on unikaalse registri esimene veerg (see tähendab, et sellel võib olla mitu sama väärtusega rida).

Andmete lisamine tabelisse

Nüüd saame oma kaks andmebaasi tabelit mõne näidisandmega täita. Esiteks klientide tabel:

1
2
3
4

INSERT INTO kliendi (ees- ja perekonnanimi) VÄÄRTUSED

(‘Elaine’, ‘Stevens’),

(‘Mary’, ‘Dittman’),

(‘Jäta vahele’, ‘Stevenson’);

Meie klientide tabel näeb nüüd välja selline:

1
2
3
4
5
6
7
8

VALI * kliendilt;

+—- + ———— +————+

| id | eesnimi | perekonnanimi |

+—- + ———— +————+

| 1 | Elaine | Stevens |

| 2 | Maarja | Dittman |

| 3 | Jäta vahele | Stevenson |

+—- + ———— +————+

Mõned kontaktandmete näidisandmed:

1
2
3
4
5
6
7
8

INSERT INTO kontakti (kliendi_ID, teave, tüüp) VÄÄRTUSED

(„1”, „111–111–111”, „töö”),

(„1”, „111–111–123”, „kodu”),

(‘1’, ‘[email protected]’, ‘e-post’),

(„2”, „222–222–222”, „töö”),

(„2”, „[email protected]”, „e-post”),

(„2”, „[email protected]”, „e-post”),

(‘3’, ‘[email protected]’, ’email’);

Meie kontaktide tabel näeb nüüd välja selline:

1
2
3
4
5
6
7
8
9
10
11
12

VALI * kontaktist;

+—— +———— + +——————— +——-+

| id | klient_id | info | tüüp |

+—— +———— + +——————— +——-+

| NULL | 1 | 111–111–111 | töö |

| NULL | 1 | 111–111–123 | kodu |

| NULL | 1 | [email protected]äide.com | e-post |

| NULL | 2 | 222–222–222 | töö |

| NULL | 2 | [email protected] | e-post |

| NULL | 2 | [email protected]äide.com | e-post |

| NULL | 3 | [email protected] | e-post |

+—— +———— + +——————— +——-+

Nüüd, kui meil on näidisandmed, vaatame, kuidas võõrad võtmed aitavad andmete terviklikkust säilitada.

Soovituslike andmete kustutamine (andmete terviklikkuse säilitamise kohta)

Varem määratlesime välismaise võtmepiirangu järgmiselt:

“VÄLISVÕTMED (kliendi_ID) VIITAB klienti (id) KASKADI KUSTUTAMISEL UUESTI KASKADI”

See tähendab, et kui kustutame konkreetse kliendi, tuleks kustutada ka sellega seotud kontakttabeli read. Samuti kasseerib ON UPDATE CASCADE vanemtabeli kõik värskendused alamtabeli viidatud väljadele (antud juhul kliendi_id).

Proovime esmalt testida kliendi kustutamist:

1 Kustuta kliendilt, kus eesnimi = ‘Jäta vahele’;

Kui vaatame oma tabeleid uuesti, näeme, et mõlemat tabelit muudeti vastavalt vajadusele, kuna ka kõik vahelejätmise kontakti read olid eemaldatud:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

VALI * kliendilt;

+—- + ———– +———-+

| id | eesnimi | perekonnanimi |

+—- + ———– +———-+

| 1 | Elaine | Stevens |

| 2 | Maarja | Dittman |

+—- + ———– +———-+

 

VALI * kontaktist;

+—— +———— + +——————— +——-+

| id | klient_id | info | tüüp |

+—— +———— + +——————— +——-+

| NULL | 1 | 111–111–111 | töö |

| NULL | 1 | 111–111–123 | kodu |

| NULL | 1 | [email protected]äide.com | e-post |

| NULL | 2 | 222–222–222 | töö |

| NULL | 2 | [email protected] | e-post |

| NULL | 2 | [email protected]äide.com | e-post |

+—— +———— + +——————— +——-+

Soovituslike andmete värskendamine (andmete terviklikkuse säilitamise kohta)

Uuendame Elaine’i kliendi ID-d, et testida ON UPDATE CASCADE-i:

1 UPDATE kliendi SET id = 7 KUS eesnimi = ‘Elaine’;

Mõlemas tabelis on nüüd vajalikud muudatused tehtud:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

vali kliendi seast *;

+—- + ———– +———-+

| id | eesnimi | perekonnanimi |

+—- + ———– +———-+

| 7 | Elaine | Stevens |

| 22 | Maarja | Dittman |

+—- + ———– +———-+

 

vali kontakti hulgast *;

+—— +———— + +——————— +——-+

| id | klient_id | info | tüüp |

+—— +———— + +——————— +——-+

| NULL | 7 | 111–111–111 | töö |

| NULL | 7 | 111–111–123 | kodu |

| NULL | 7 | [email protected]äide.com | e-post |

| NULL | 22 | 222–222–222 | töö |

| NULL | 22 | [email protected] | e-post |

| NULL | 22 | [email protected]äide.com | e-post |

+—— +———— + +——————— +——-+

2. Võõra võtme määratlemine ALTER TABLE abil

Samuti on võimalik tabelitesse lisada võõrvõtmeid, kui need on juba loodud. Selle asemel, et teha tabeli loomine ühes etapis, nagu me varem tegime, võime seda teha ka mitme sammuna.

Loomine & Siis tabelite muutmine

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE TABLE klient (

  id INT EI OLE MITTE AUTO_INCREMENT,

  eesnimi varchar (50) EI OLE NULL,

  perekonnanimi varchar (50) EI NULL,

  PRIMARY KEY (id)

) MOOTOR = INNODB;

 

CREATE TABLE kontakt (

  id INT,

  klient_id INT,

  info varchar (50) EI NULL,

  tüüp varchar (50) EI NULL

) MOOTOR = INNODB;

 

ALTER TABLE kontakt ADD INDEX par_ind (kliendi_id);

ALTER TABLE kontakt ADD CONSTRAINT fk_customer

VÄLISVÕTM (kliendi_ID) VIITAB klienti (id) KASKADI KUSTUTAMISEL UUENDAMISE PIIRAMISEL;

Pange tähele, et olemasolevat võõrvõtit ei saa muuta. Esmalt peate olemasoleva võõrvõtme eemaldama selle sümbolinime järgi ja seejärel saate määratleda uue võõrvõtme, nagu me varem tegime.

1 ALTER TABLE kontakt DROP VÄLISVÕTM fk_customer;

Lõppsõnad võõraste võtmete kohta

Võõrvõti on hea andmebaasihalduse lahutamatu osa. Kui olete nüüd huvitatud nende kasutamise täpsematest näidetest, lugege siin jaotist Välisvõtmeklauslite näited.

Sellel lehel on näide, kus tabelis „product_order” on veel kahe tabeli jaoks võõrad võtmed. Üks võõrvõti viitab kaheveerulisele indeksile tabelis „Toode”. Muud viitavad ühe veeru indeksile tabelis „Klient”.

Muude huvitavate välismaiste võtmenäidete leiate siit.

Addendum: välismaised peamised piirangud

Tabelirea andmete eemaldamisel (ON DELETE) või värskendamisel (ON UPDATE) värskendatud võtmete piiranguid kasutatakse tabelites olevate andmete ühtluse tagamiseks.

CREATE TABLE või ALTER TABLE avalduses oleva võõrvõti piirangu määratluse süntaks näeb välja järgmine:

1
2
3
4

[CONSTRAINT [sümbol]] VÄLISVÕTM

[indeksi_nimi] (indeksi_veeru_nimi,…)

VIITED vanema_tabeli_nimi (indeksi_veeru_nimi,…)

[ON DELETE reference_option] [ON UPDATE reference_option]

Osa “CONSTRAINT [sümbol]” on valikuline ja seda saab kasutada võõrvõtme nime määratlemiseks. See on kasulik võõraste võtmete eemaldamiseks nende sümboli nime järgi ja ka selgema vigade logimise jaoks.

Indeksi_nimi väärtus tähistab võõra võtme ID-d ja seda ei arvestata, kui alamtabelis on juba selgesõnaliselt määratletud register, mis võõrvõti toetab. Välisvõtme unikaalseks identifitseerimiseks saab kasutada ühte või mitut veergu.

Võrdlusvõimalus

Kasutatakse nii ON DELETE kui ON UPDATE, viide_valik võib osutada ühele järgmistest:

KASKADE:

See käsk kustutab või värskendab vanemtabeli rida ning kustutab või värskendab automaatselt alamtabelis olevad vastavad read.

PIIRATUS:

See lükkab vanema tabeli toimingud Kustuta või Uuenda. RESTRICTi määratlemine on sama, kui klausli ON DELETE või ON UPDATE väljajätmine. Teisisõnu, tagasilükkamine on MySQL-is vaikimisi toiming.

MITTE TEGEMIST:

Tavalisest SQL-ist pärit märksõna EI TEGEVA on sisuliselt samaväärne MySQL-i käsuga RESTRICT.

SET NULL:

Lõpuks käsk SET NULL kustutab või värskendab vanemtabeli rida ja seab lapsetabeli võõra võtme veeru (või veerud) väärtuseks NULL. See toiming võib olla kasulik olukordades, kus lapselaua ridu ei tohiks vanema tabeli rea eemaldamisel kustutada.

Fotoallikad: www.mvpcoo.com

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map