KEKANGAN: integriti pangkalan data

Kawalan integriti pangkalan data

Satu lagi perkara penting yang perlu diketahui tentang pangkalan data ialah CONSTRAINS. Dengan bantuan kekangan, anda boleh mengawal perubahan data dalam jadual anda dan mengekalkan integriti dan konsistensinya.

Apakah ketekalan data apabila kita bercakap tentang pangkalan data?

Mari ambil kedai dalam talian kami dengan pekerja, produk dan jadual tugas . Kita sudah tahu bahawa mungkin terdapat tugas dalam jadual tugas yang tidak diberikan kepada sesiapa: id_pekerja bagi baris tersebut ialah NULL.

Tetapi apa yang berlaku jika terdapat entri dalam jadual tugas dengan id_pekerja sama dengan, katakan, 115? Lagipun, kami tidak mempunyai pekerja seperti itu. Kami tidak mempunyai pekerja dengan id = 115 dalam jadual pekerja. Pada masa yang sama, pautan kepada pekerja dengan id ini terdapat dalam jadual tugas. Ini adalah contoh ketidakkonsistenan data .

Jadi bagaimana kita mendamaikan data ini? Sebaik-baiknya, pelayan SQL, dengan sebarang perubahan data, mengawal semua nuansa ini. Dan terdapat peluang sedemikian, ia dipanggil FOREIGN_KEY.

Jika beberapa lajur dalam jadual anda mengandungi bukan sahaja nombor, tetapi baris id daripada jadual lain, maka ini boleh ditentukan secara eksplisit.

Menambah KUNCI ASING

Kunci sedemikian boleh ditambah pada jadual pada peringkat penciptaannya, dan selepas itu, menggunakan ALTER TABLE. Formatnya tidak berbeza secara asas. Kami akan membentangkan kedua-dua pilihan.

Bentuk umum kunci/peraturan tersebut ialah:

FOREIGN KEY (column)
  	REFERENCES table(column)

Mari tambahkan kunci/peraturan ini pada jadual tugas untuk memastikan semua id_pekerja daripada jadual merujuk kepada entri sedia ada dalam jadual pekerja. Skrip ini akan kelihatan seperti ini:

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

Dan jika kami memutuskan untuk menambah peraturan ini semasa membuat jadual tugas, maka kod itu akan kelihatan seperti ini:

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

Sebenarnya, terdapat situasi apabila rentetan yang kita rujuk mempunyai kunci komposit yang unik: contohnya, "Nama dan tahun lahir" atau "productCatogoryId dan productId". Kemudian FOREIGN KEY boleh ditulis seperti ini:

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

KUNCI ASING dan menukar data

Sekarang bayangkan situasi di mana kami memutuskan untuk mengemas kini beberapa data dalam jadual pekerja dan id pekerja kami telah berubah. Apakah yang akan berlaku kepada data dalam jadual tugas? Betul, mereka akan menjadi tidak relevan, dan integriti pangkalan data kami akan dilanggar.

Untuk mengelakkan perkara ini daripada berlaku, anda boleh memberitahu SQL Server untuk menukar employee_id semua baris dalam semua jadual yang merujuk kepada id yang diubah khusus ini apabila id dalam jadual pekerja berubah.

Skrip sedemikian dipanggil OnUpdate dan OnDelete . Apa yang perlu dilakukan jika id rekod berubah, dan apa yang perlu dilakukan jika rekod dipadamkan?

Dengan penyingkiran, tidak semuanya begitu mudah. Jika anda mempunyai objek bergantung yang diwakili oleh rentetan dalam pangkalan data yang merujuk antara satu sama lain, maka pelbagai jenis senario tingkah laku adalah mungkin apabila memadamkan satu objek.

Katakan kita memadamkan pengguna tapak, yang bermaksud kita mesti memadamkan semua surat-menyurat peribadinya. Tetapi tidak mungkin kita harus mengalih keluar semua komen umum beliau.

Atau pekerja berhenti. Pelik jika dia berhenti dan pada masa yang sama semua tugas yang diberikan kepadanya hilang dari pangkalan data. Tetapi jika mereka tetap dilantik bukan olehnya, ia juga akan menjadi buruk. Adalah lebih tepat untuk menjadikannya supaya pekerja itu boleh berhenti selepas menyerahkan semula semua tugasnya kepada orang lain.

Berikut ialah cara kita boleh menerangkan senario ini menggunakan FOREIGN KEY. Bentuk umum kunci/peraturan tersebut ialah:

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

Apa yang perlu dilakukan sekiranya rekod (ON DELETE) dipadam atau ditukar (ON UPDATE)? Secara keseluruhan, terdapat 5 pilihan untuk pelayan SQL bertindak dalam setiap situasi ini:

Begini cara kami boleh mengubah suai jadual tugas kami:

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

Apa yang tertulis di sini:

ON UPDATE CASCADE : Jika kunci id dalam jadual pekerja berubah, maka tukar juga employee_id dalam jadual tugas yang merujuknya.

ON DELETE RESTRICT : Jika baris sedang dipadamkan daripada jadual pekerja dan dirujuk daripada jadual tugas, maka elakkan baris itu daripada dipadamkan daripada jadual pekerja.