CONSTRAINT: integritas basis data

Kontrol integritas basis data

Hal lain yang penting untuk diketahui tentang database adalah CONSTRAINS. Dengan bantuan kendala, Anda dapat mengontrol perubahan data di tabel Anda dan mempertahankan integritas dan konsistensinya.

Apa konsistensi data ketika kita berbicara tentang database?

Mari kita ambil toko online kita dengan tabel karyawan, produk, dan tugas . Kita sudah tahu bahwa mungkin ada tugas di tabel tugas yang tidak diberikan kepada siapa pun: employee_id dari baris tersebut adalah NULL.

Tapi apa yang terjadi jika ada entri di tabel tugas dengan employee_id sama dengan, katakanlah, 115? Bagaimanapun, kami tidak memiliki karyawan seperti itu. Kami tidak memiliki karyawan dengan id = 115 di tabel karyawan. Pada saat yang sama, tautan ke karyawan dengan id ini ada di tabel tugas. Ini adalah contoh inkonsistensi data .

Jadi bagaimana kita merekonsiliasi data ini? Idealnya, server SQL, dengan perubahan data apa pun, mengontrol semua nuansa ini. Dan ada peluang seperti itu, namanya FOREIGN_KEY.

Jika beberapa kolom dalam tabel Anda tidak hanya berisi angka, tetapi baris id dari tabel lain, maka ini dapat ditentukan secara eksplisit.

Menambahkan KUNCI ASING

Kunci seperti itu dapat ditambahkan ke tabel baik pada tahap pembuatannya, dan setelahnya, menggunakan ALTER TABLE. Formatnya tidak berbeda secara fundamental. Kami akan menyajikan kedua opsi tersebut.

Bentuk umum dari kunci/aturan tersebut adalah:

FOREIGN KEY (column)
  	REFERENCES table(column)

Mari tambahkan kunci/aturan ini ke tabel tugas untuk memastikan bahwa semua employee_ids dari tabel merujuk ke entri yang ada di tabel karyawan. Skrip ini akan terlihat seperti ini:

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

Dan jika kami memutuskan untuk menambahkan aturan ini pada saat membuat tabel tugas, maka kodenya akan terlihat 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)
);

Ngomong-ngomong, ada situasi ketika string yang kita rujuk memiliki kunci komposit unik: misalnya, "Nama dan tahun lahir" atau "productCatogoryId dan productId". Maka KUNCI ASING dapat ditulis seperti ini:

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

KUNCI ASING dan mengubah data

Sekarang bayangkan situasi di mana kami memutuskan untuk memperbarui beberapa data di tabel karyawan dan id karyawan kami telah berubah. Apa yang akan terjadi pada data di tabel tugas? Benar, mereka akan menjadi tidak relevan, dan integritas database kita akan dilanggar.

Untuk mencegah hal ini terjadi, Anda dapat memberi tahu SQL Server untuk mengubah employee_id dari semua baris di semua tabel yang merujuk ke id yang diubah khusus ini saat id di tabel karyawan berubah.

Skrip semacam itu disebut OnUpdate dan OnDelete . Apa yang harus dilakukan jika id rekaman berubah, dan apa yang harus dilakukan jika rekaman dihapus?

Dengan penghapusan, tidak semuanya sesederhana itu. Jika Anda memiliki objek dependen yang diwakili oleh string dalam database yang merujuk satu sama lain, maka beragam skenario perilaku dimungkinkan saat menghapus satu objek.

Katakanlah kita menghapus pengguna situs, yang berarti kita harus menghapus semua korespondensi pribadinya. Tetapi tidak mungkin kita harus menghapus semua komentar publiknya.

Atau seorang karyawan berhenti. Akan aneh jika dia berhenti dan pada saat yang sama semua tugas yang diberikan kepadanya menghilang dari database. Tetapi jika mereka tetap ditunjuk bukan olehnya, itu juga akan menjadi buruk. Lebih tepat membuatnya agar karyawan tersebut dapat berhenti setelah mengalihkan semua tugasnya kepada orang lain.

Inilah cara kami menjelaskan skenario ini menggunakan FOREIGN KEY. Bentuk umum dari kunci/aturan tersebut adalah:

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

Apa yang harus dilakukan jika terjadi penghapusan (ON DELETE) atau perubahan (ON UPDATE) record? Secara total, ada 5 opsi untuk server SQL untuk bertindak di setiap situasi ini:

Inilah cara kami dapat memodifikasi tabel 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 di tabel karyawan berubah, ubah juga employee_id di tabel tugas yang mereferensikannya.

ON DELETE RESTRICT : Jika sebuah baris dihapus dari tabel karyawan dan direferensikan dari tabel tugas, maka cegah agar baris tersebut tidak dihapus dari tabel karyawan.