MySQL: “Strani ključevi” koji se koriste u primjeru

U nastavku ćemo pokazati nekoliko jednostavnih primjera postavljanja stranih ključeva i kako oni rade u MySQL-u. U našim ćemo primjerima u nastavku referencirati tablice roditelja i djece. Tablice s oznakom “Kupac” i “Kontakt” predstavljaju tablice roditelja i djece.


Postoje dva načina za definiranje stranih ključeva u MySQL-u:

  1. Definiranje stranih ključeva s KREATIVNOM TABLOM
  2. Definiranje stranih tipki s ALTER TABLOM

Uz ove primjere, uključili smo i raspravu o tome što su strana ključna ograničenja i kako se oblikuju, uz primjere..

Za svaki slučaj – što je strani ključ?

Strani ključ je polje (ili skup polja) u tablici koja jedinstveno identificira red druge tablice. Tablica u kojoj je definiran strani ključ naziva se “podređena tablica” i odnosi se (često) na primarni ključ u nadređenoj tablici.Primjeri MySQL stranih ključeva

Ograničenja stranih ključeva tada se mogu koristiti za definiranje načina integriranja podataka između dvije tablice (npr. Kada se redak tablice briše ili ažurira).

Ako se pravilno izvrši, uvijek će se održavati veza između primarnog i stranog ključa, tako da vaša baza podataka nikada neće biti ostavljena bez roditelja u dječjoj tablici (npr. Ostatak kontaktnih podataka vezan za uklonjenog kupca). Sljedeći pojednostavljeni (i ne u potpunosti normalizirani) primjer baze podataka kupca pomaže nam da vizualiziramo ove inozemne ključne pojmove.

1. Definiranje stranih ključeva s KREATIVNOM TABLOM

Idite na MySQL konzolu upišite mysql naredbu s ispravnim argumentima korisnika i lozinke. Ako je potrebno, upišite “man mysql” da biste dobili više informacija.

1 mysql -u -p

Stvorite bazu podataka i započnite je upotrebljavati:

1
2

USTVARATI BAZU DATABASE testdb;

koristiti testdb;

Izrada tablica

Sada napravite dvije tablice:

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

USTVARITE TABELU kupca (

  id INT NIJE NULL AUTO_INCREMENT,

  ime varchar (50) NIJE NULL,

  prezime varchar (50) NIJE NULL,

  PRIMARNI KLJUČ (id)

) MOTOR = INNODB;

 

USTVARITE TABELU kontakt (

  id INT,

  kupac_id INT,

  info varchar (50) NIJE NULL,

  vrsta varchar (50) NIJE NULL,

  INDEX par_ind (customer_id),

  CONSTRAINT fk_customer STRANI KLJUČ (customer_id)

  REFERENCE kupca (id)

  NA DELETE CASCADE

  NA AŽURIRANOJ KASKADI

) MOTOR = INNODB;

Provjera strukture tablice

Pogledajmo kako izgleda naša struktura baze podataka:

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

POKAŽI TABELE;

+——+

| Tablice_in_testdb |

+——+

| kontakt |

| kupac |

+——+

 

OPISNI kupac;

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

| Polje | Vrsta | Ništa | Ključ | Zadano | Dodatno |

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

| id | int (11) | NE | PRI | NULL | auto_increment |

| ime | varchar (50) | NE | | NULL | |

| prezime | varchar (50) | NE | | NULL | |

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

 

OPISITE kontakt;

+—– —– + + – + – + — + –+

| Polje | Vrsta | Ništa | Ključ | Zadano | Dodatno |

+—– —– + + – + – + — + –+

| id | int (11) | DA | | NULL | |

| kupac_id | int (11) | DA | MUL | NULL | |

| info | varchar (50) | NE | | NULL | |

| vrsta | varchar (50) | NE | | NULL | |

+—– —– + + – + – + — + –+

Imajte na umu vrijednost MUL u stupcu Key tablice kontakata. To nam govori da je polje customer_id prvi stupac jedinstvenog indeksa (što znači da može imati više redova s ​​istom vrijednošću).

Dodavanje podataka u tablicu

Sada možemo ispuniti naše dvije tablice baze podataka s nekim uzorcima podataka. Prvo, tablica korisnika:

1
2
3
4

ULAZITE U kupca (ime i prezime) VRIJEDNOSTI

(‘Elaine’, ‘Stevens’),

(‘Marija’, ‘Dittman’),

(‘Preskoči’, ‘Stevenson’);

Naša tablica kupaca sada izgleda ovako:

1
2
3
4
5
6
7
8

ODABIR * OD kupca;

+- + —- + —-+

| id | ime | prezime |

+- + —- + —-+

| 1 | Elaine | Stevens |

| 2 | Mary | Dittman |

| 3 | Preskoči | Stevenson |

+- + —- + —-+

Neki uzorci podataka za kontaktnu tablicu:

1
2
3
4
5
6
7
8

POSTAVITE U kontakt (kupac_id, informacije, vrsta) VRIJEDNOSTI

(‘1’, ‘111–111–111’, «posao»),

(‘1’, ‘111–111–123’, «dom»),

(‘1’, ‘[email protected]com’, ’email’),

(“2”, “222-2222222”, “posao”),

(‘2’, ‘[email protected]’, ’email’),

(‘2’, ‘[email protected]’, ’email’),

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

Naša tablica kontakata sada izgleda ovako:

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

ODABIR * OD kontakta;

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

| id | kupac_id | info | vrsta |

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

| NULL | 1 | 111–111–111 | rad |

| NULL | 1 | 111–111–123 | kući |

| NULL | 1 | [email protected] | e-pošta |

| NULL | 2 | 222–222–222 | rad |

| NULL | 2 | [email protected] | e-pošta |

| NULL | 2 | [email protected] | e-pošta |

| NULL | 3 | [email protected] | e-pošta |

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

Sada kada imamo uzorke podataka, pogledajmo kako strani ključevi pomažu u očuvanju integriteta podataka.

Brisanje referentnih podataka (da biste vidjeli kako se čuva integritet podataka)

Ranije smo definirali ograničenje stranog ključa kao:

“STRANI KLJUČ (customer_id) REFERENCE kupca (id) NA IZBORU CASCADE NA UPDATE CASCADE”

To znači da kad izbrišemo određenog kupca, također treba izbrisati povezane redove tablice kontakata. Također, ON UPDATE CASCADE će naknadno izvršiti kaskadiranje svih ažuriranja nadređene tablice u referentna polja u podređenoj tablici (u ovom slučaju customer_id).

Najprije testiramo korisnika:

1 IZUZETI OD kupca GDJE ime = ‘Preskoči’;

Kad ponovo pogledamo naše tablice, vidimo da su obje tablice promijenjene prema potrebi jer su uklonjeni i svi redovi kontakata za Skip:

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

ODABIR * OD kupca;

+- + + —- —-+

| id | ime | prezime |

+- + + —- —-+

| 1 | Elaine | Stevens |

| 2 | Mary | Dittman |

+- + + —- —-+

 

ODABIR * OD kontakta;

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

| id | kupac_id | info | vrsta |

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

| NULL | 1 | 111–111–111 | rad |

| NULL | 1 | 111–111–123 | kući |

| NULL | 1 | [email protected] | e-pošta |

| NULL | 2 | 222–222–222 | rad |

| NULL | 2 | [email protected] | e-pošta |

| NULL | 2 | [email protected] | e-pošta |

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

Ažuriranje referentnih podataka (da biste vidjeli kako se čuva integritet podataka)

Ažurirajmo customer_id tvrtke Elaine kako bismo testirali NASTAVLJENU KASCADU:

1 Ažuriranje korisničkog ID-a = 7 GDJE ime = ‘Elaine’;

Potrebne promjene sada su napravljene u obje tablice:

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

odaberite * od kupca;

+- + + —- —-+

| id | ime | prezime |

+- + + —- —-+

| 7 | Elaine | Stevens |

| 22 | Mary | Dittman |

+- + + —- —-+

 

odaberite * od kontakta;

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

| id | kupac_id | info | vrsta |

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

| NULL | 7 | 111–111–111 | rad |

| NULL | 7 | 111–111–123 | kući |

| NULL | 7 | [email protected] | e-pošta |

| NULL | 22 | 222–222–222 | rad |

| NULL | 22 | [email protected] | e-pošta |

| NULL | 22 | [email protected] | e-pošta |

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

2. Definiranje stranih tipki s ALTER TABLOM

Moguće je i dodavanje stranih ključeva u tablice nakon što su one već kreirane. Umjesto da kreiranje tablice radimo u jednom koraku, kao što smo to činili prije, također to možemo učiniti u više koraka.

stvaranje & Zatim mijenjanje tablica

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

USTVARITE TABELU kupca (

  id INT NIJE NULL AUTO_INCREMENT,

  ime varchar (50) NIJE NULL,

  prezime varchar (50) NIJE NULL,

  PRIMARNI KLJUČ (id)

) MOTOR = INNODB;

 

USTVARITE TABELU kontakt (

  id INT,

  kupac_id INT,

  info varchar (50) NIJE NULL,

  vrsta varchar (50) NIJE NULL

) MOTOR = INNODB;

 

ALTER TABLE kontaktirajte ADD INDEX par_ind (customer_id);

ALTER TABELA kontaktirajte DODAJ KONSTRAINT fk_customer

STRANI KLJUČ (customer_id) REFERENCE kupca (id) NA IZBORU CASCADE NA Ažurnom ograničenju;

Imajte na umu da ne možete promijeniti postojeći inozemni ključ. Prvo morate ukloniti postojeći inozemni ključ njegovim imenom i tada možete definirati novi strani ključ kao što smo prethodno radili.

1 ALTER TABELA kontaktirajte DROP FOREIGN KEY fk_customer;

Završne riječi o stranim ključevima

Strani ključevi sastavni su dio dobrog upravljanja bazama podataka. Ako ste sada zainteresirani za pregled naprednijih primjera njihove uporabe, pogledajte odjeljak Primjeri stranih ključnih klauzula ovdje.

Na toj je stranici primjer u kojoj tablica “product_order” sadrži strane ključeve za dvije druge tablice. Jedan strani ključ odnosi se na indeks s dva stupca u tablici “Proizvod”. Drugi se odnose na indeks jednog stupca u tablici “Kupac”.

Ostale zanimljive inozemne ključne primjere možete pronaći ovdje.

Dodatak: Ograničenja stranih ključeva

Ograničenja stranih ključeva koriste se za održavanje dosljednih podataka u tablicama pri uklanjanju (ON DELETE) ili ažuriranju (ON UPDATE) podataka retka tablice.

Sintaksa za definiciju ograničenja stranog ključa u izrazu CREATE TABLE ili ALTER TABLE izgleda ovako:

1
2
3
4

[CONSTRAINT [simbol]] STRANI KLJUČ

[ime_indeksa] (ime_ime kolumne,…)

REFERENCE roditeljsko_ime_ime (indeks_ime kolumne, …)

[ON DELETE reference_option] [ON UPDATE reference_option]

Dio “CONSTRAINT [simbol]” nije obavezan i može se koristiti za definiranje naziva stranog ključa. Ovo je korisno za uklanjanje stranih ključeva s njihovim nazivom simbola i za stvaranje jasnijeg evidentiranja pogrešaka.

Vrijednost index_name predstavlja ID stranog ključa i zanemaruje se ako na podređenoj tablici već postoji eksplicitno definiran indeks koji može podržati strani ključ. Jedan ili više stupaca mogu se koristiti za jedinstveno prepoznavanje stranog ključa.

Referentna opcija

Koriste i za UKLJUČIVANJE i ON UPDATE, referenca_option može ukazivati ​​na jedno od sljedećeg:

KASKADA:

Ova naredba briše ili ažurira redak iz nadređene tablice te automatski briše ili ažurira podudarne retke u podređenoj tablici.

OGRANIČITI:

Ovaj odbacuje DELETE ili UPDATE operacije za nadređenu tablicu. Navođenje RESTRICT isto je kao izostavljanje klauzule ON DELETE ili ON UPDATE. Drugim riječima, odbacivanje je zadana radnja u MySQL-u.

NEMA AKCIJE:

Ključna riječ iz standardnog SQL-a, NO ACTION je u osnovi jednaka naredbi RESTRICT u MySQL-u.

POSTAVITE NULL:

Konačno, naredba SET NULL briše ili ažurira redak iz nadređene tablice i postavlja stupac s inozemnim ključem (ili stupce) u podređenoj tablici na NULL. Ova radnja može biti korisna u situacijama kada se retci iz podređene tablice ne bi trebali brisati kada se ukloni roditeljski redak tablice.

Izvori fotografija: www.mvpcoo.com

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me