Batasan kunci primer dan asing

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL Database Azure SQL Managed Instance yang lebih baru

Kunci primer dan kunci asing adalah dua jenis batasan yang dapat digunakan untuk menegakkan integritas data dalam tabel SQL Server. Ini adalah objek database penting.

Batasan kunci primer

Tabel biasanya memiliki kolom atau kombinasi kolom yang berisi nilai yang secara unik mengidentifikasi setiap baris dalam tabel. Kolom ini, atau kolom, disebut kunci primer (PK) tabel dan memberlakukan integritas entitas tabel. Karena batasan kunci primer menjamin data unik, batasan kunci utama sering ditentukan pada kolom identitas.

Saat Anda menentukan batasan kunci utama untuk tabel, Mesin Database memberlakukan keunikan data dengan membuat indeks unik secara otomatis untuk kolom kunci utama. Indeks ini juga mengizinkan akses cepat ke data saat kunci utama digunakan dalam kueri. Jika batasan kunci primer ditentukan pada lebih dari satu kolom, nilai dapat diduplikasi dalam satu kolom, tetapi setiap kombinasi nilai dari semua kolom dalam definisi batasan kunci utama harus unik.

Seperti yang ditunjukkan dalam ilustrasi berikut, ProductID kolom dan VendorID dalam Purchasing.ProductVendor tabel membentuk batasan kunci primer komposit untuk tabel ini. Ini memastikan bahwa setiap baris dalam ProductVendor tabel memiliki kombinasi unik dan ProductID VendorID. Ini mencegah penyisipan baris duplikat.

• Tabel hanya dapat berisi satu batasan kunci primer.
• Kunci primer tidak boleh melebihi 16 kolom dan panjang kunci total 900 byte.
• Indeks yang dihasilkan oleh batasan kunci utama tidak dapat menyebabkan jumlah indeks pada tabel melebihi 999 indeks non-kluster dan 1 indeks berkluster.
• Jika terkluster atau tidak ditentukan untuk batasan kunci primer, kluster digunakan jika tidak ada indeks berkluster pada tabel.
• Semua kolom yang ditentukan dalam batasan kunci utama harus didefinisikan sebagai bukan null. Jika nullability tidak ditentukan, semua kolom yang berpartisipasi dalam batasan kunci utama memiliki nullability yang diatur ke tidak null.
• Jika kunci primer didefinisikan pada kolom jenis yang ditentukan pengguna CLR, implementasi jenis tersebut harus mendukung pengurutan biner.

Batasan kunci asing

Kunci asing (FK) adalah kolom atau kombinasi kolom yang digunakan untuk membuat dan menerapkan tautan antara data dalam dua tabel untuk mengontrol data yang dapat disimpan dalam tabel kunci asing. Dalam referensi kunci asing, tautan dibuat di antara dua tabel saat kolom atau kolom yang menyimpan nilai kunci utama untuk satu tabel direferensikan oleh kolom atau kolom di tabel lain. Kolom ini menjadi kunci asing dalam tabel kedua.

Misalnya, Sales.SalesOrderHeader tabel memiliki tautan kunci asing ke Sales.SalesPerson tabel karena ada hubungan logis antara pesanan penjualan dan tenaga penjualan. Kolom SalesPersonID dalam SalesOrderHeader tabel cocok dengan kolom SalesPerson kunci utama tabel. Kolom SalesPersonID dalam SalesOrderHeader tabel adalah kunci asing untuk SalesPerson tabel. Dengan membuat hubungan kunci asing ini, nilai untuk SalesPersonID tidak dapat disisipkan ke SalesOrderHeader dalam tabel jika belum ada dalam SalesPerson tabel.

Tabel dapat mereferensikan maksimum 253 tabel dan kolom lainnya sebagai kunci asing (referensi keluar). SQL Server 2016 (13.x) meningkatkan batas jumlah tabel dan kolom lain yang dapat mereferensikan kolom dalam satu tabel (referensi masuk), dari 253 menjadi 10.000. (Memerlukan setidaknya 130 tingkat kompatibilitas.) Peningkatan memiliki batasan berikut:

• Lebih dari 253 referensi kunci asing hanya didukung untuk DELETE operasi DML. UPDATE operasi dan MERGE tidak didukung.

• Tabel dengan referensi kunci asing ke dirinya sendiri masih terbatas pada 253 referensi kunci asing.

• Lebih besar dari 253 referensi kunci asing saat ini tidak tersedia untuk indeks penyimpan kolom, tabel yang dioptimalkan memori, Stretch Database, atau tabel kunci asing yang dipartisi.

  Penting

  Stretch Database tidak digunakan lagi di SQL Server 2022 (16.x) dan Azure SQL Database. Fitur ini akan dihapus dalam versi Mesin Database di masa mendatang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

Indeks pada batasan kunci asing

Tidak seperti batasan kunci primer, membuat batasan kunci asing tidak secara otomatis membuat indeks yang sesuai. Namun, membuat indeks secara manual pada kunci asing sering berguna karena alasan berikut:

• Kolom kunci asing sering digunakan dalam kriteria gabungan ketika data dari tabel terkait digabungkan dalam kueri dengan mencocokkan kolom atau kolom dalam batasan kunci asing dari satu tabel dengan kolom kunci utama atau unik atau kolom di tabel lain. Indeks memungkinkan Mesin Database menemukan data terkait dengan cepat dalam tabel kunci asing. Namun, membuat indeks ini tidak diperlukan. Data dari dua tabel terkait dapat digabungkan bahkan jika tidak ada batasan kunci primer atau kunci asing yang didefinisikan di antara tabel, tetapi hubungan kunci asing antara dua tabel menunjukkan bahwa kedua tabel telah dioptimalkan untuk digabungkan dalam kueri yang menggunakan kunci sebagai kriterianya.

• Perubahan pada batasan kunci utama diperiksa dengan batasan kunci asing dalam tabel terkait.

Integritas referensial

Meskipun tujuan utama dari batasan kunci asing adalah untuk mengontrol data yang dapat disimpan dalam tabel kunci asing, itu juga mengontrol perubahan pada data dalam tabel kunci utama. Misalnya, jika baris untuk salesperson dihapus dari Sales.SalesPerson tabel, dan ID tenaga penjual digunakan untuk pesanan penjualan dalam Sales.SalesOrderHeader tabel, integritas relasional antara kedua tabel rusak; pesanan penjualan tenaga penjual yang dihapus terputus dalam SalesOrderHeader tabel tanpa tautan ke data dalam SalesPerson tabel.

Batasan kunci asing mencegah situasi ini. Batasan memberlakukan integritas referensial dengan menjamin bahwa perubahan tidak dapat dilakukan pada data dalam tabel kunci utama jika perubahan tersebut membatalkan tautan ke data dalam tabel kunci asing. Jika upaya dilakukan untuk menghapus baris dalam tabel kunci utama atau untuk mengubah nilai kunci utama, tindakan gagal saat nilai kunci primer yang dihapus atau diubah sesuai dengan nilai dalam batasan kunci asing dari tabel lain. Agar berhasil mengubah atau menghapus baris dalam batasan kunci asing, Anda harus terlebih dahulu menghapus data kunci asing dalam tabel kunci asing atau mengubah data kunci asing dalam tabel kunci asing, yang menautkan kunci asing ke data kunci utama yang berbeda.

Integritas referensial bertingkat

Dengan menggunakan batasan integritas referensial berjendela, Anda dapat menentukan tindakan yang diambil Mesin Database saat pengguna mencoba menghapus atau memperbarui kunci tempat kunci asing yang ada menunjuk. Tindakan berskala berikut dapat ditentukan.

• NO ACTION

  Mesin Database menimbulkan kesalahan dan tindakan hapus atau perbarui pada baris dalam tabel induk digulung balik.

• CASCADE

  Baris terkait diperbarui atau dihapus dalam tabel referensi saat baris tersebut diperbarui atau dihapus dalam tabel induk. CASCADE tidak dapat ditentukan jika kolom tanda waktu adalah bagian dari kunci asing atau kunci yang dirujuk. ON DELETE CASCADE tidak dapat ditentukan untuk tabel yang memiliki INSTEAD OF DELETE pemicu. ON UPDATE CASCADE tidak dapat ditentukan untuk tabel yang memiliki INSTEAD OF UPDATE pemicu.

• SET NULL

  Semua nilai yang membentuk kunci asing diatur ke NULL saat baris terkait dalam tabel induk diperbarui atau dihapus. Agar batasan ini dijalankan, kolom kunci asing harus dapat diubah ke null. Tidak dapat ditentukan untuk tabel yang memiliki INSTEAD OF UPDATE pemicu.

• SET DEFAULT

  Semua nilai yang membentuk kunci asing diatur ke nilai defaultnya jika baris terkait dalam tabel induk diperbarui atau dihapus. Agar batasan ini dijalankan, semua kolom kunci asing harus memiliki definisi default. Jika kolom dapat diubah ke null, dan tidak ada nilai default eksplisit yang ditetapkan, NULL menjadi nilai default implisit kolom. Tidak dapat ditentukan untuk tabel yang memiliki INSTEAD OF UPDATE pemicu.

CASCADE, , SET NULLSET DEFAULT, dan NO ACTION dapat digabungkan pada tabel yang memiliki hubungan referensial satu sama lain. Jika Mesin Database menemukan NO ACTION, mesin akan berhenti dan mengembalikan CASCADEtindakan terkait , SET NULL, dan SET DEFAULT . DELETE Ketika pernyataan menyebabkan kombinasi CASCADE, , SET NULL, SET DEFAULTatau NO ACTION tindakan, semua CASCADEtindakan , , SET NULLdan SET DEFAULT diterapkan sebelum Mesin Database memeriksa apa pun NO ACTION.

Pemicu dan tindakan referensial berjentik

Tindakan referensial berjentik menembakkan AFTER UPDATE atau AFTER DELETE memicu dengan cara berikut:

• Semua tindakan referensial bertingkat yang langsung disebabkan oleh aslinya DELETE atau UPDATE dilakukan terlebih dahulu.

• Jika ada pemicu yang AFTER ditentukan pada tabel yang terpengaruh, pemicu ini diaktifkan setelah semua tindakan berkala dilakukan. Ini memicu kebakaran dalam urutan yang berlawanan dari tindakan berkala. Jika ada beberapa pemicu pada satu tabel, pemicu tersebut diaktifkan dalam urutan acak, kecuali ada pemicu pertama atau terakhir khusus untuk tabel. Urutan ini seperti yang ditentukan dengan menggunakan sp_settriggerorder.

• Jika beberapa rantai berjendela berasal dari tabel yang merupakan target langsung dari suatu UPDATE tindakan atau DELETE , urutan di mana rantai ini menembakkan pemicu masing-masing tidak ditentukan. Namun, satu rantai selalu menembakkan semua pemicunya sebelum rantai lain mulai menembak.

• Pemicu AFTER pada tabel yang merupakan target langsung dari tindakan UPDATE atau DELETE diaktifkan terlepas dari apakah ada baris yang terpengaruh. Tidak ada tabel lain yang terpengaruh oleh kaskade dalam kasus ini.

• Jika salah satu pemicu sebelumnya melakukan UPDATE atau DELETE beroperasi pada tabel lain, tindakan ini dapat memulai rantai berkakade sekunder. Rantai sekunder ini diproses untuk setiap UPDATE atau DELETE operasi pada satu waktu setelah semua pemicu pada semua rantai utama diaktifkan. Proses ini dapat diulang secara rekursif untuk operasi atau DELETE berikutnyaUPDATE.

• Melakukan CREATEoperasi , ALTER, DELETE, atau bahasa definisi data (DDL) lainnya di dalam pemicu dapat menyebabkan pemicu DDL diaktifkan. Ini kemudian mungkin melakukan operasi DELETE atau UPDATE yang memulai rantai dan pemicu berkakading tambahan.

• Jika kesalahan dihasilkan di dalam rantai tindakan referensial berjenjang tertentu, kesalahan dimunculkan, tidak ada AFTER pemicu yang ditembakkan dalam rantai tersebut, dan operasi DELETE atau UPDATE yang membuat rantai digulung balik.

• Tabel yang memiliki INSTEAD OF pemicu tidak dapat juga memiliki REFERENCES klausul yang menentukan tindakan berjentik. Namun, AFTER pemicu pada tabel yang ditargetkan oleh tindakan berskala dapat menjalankan INSERTpernyataan , , UPDATEatau DELETE pada tabel lain atau tampilan yang mengaktifkan pemicu yang INSTEAD OF ditentukan pada objek tersebut.

Konten terkait

• Membuat Kunci Primer
• Hapus Kunci Primer
• Ubah Kunci Primer
• Membuat Hubungan Kunci Asing
• Ubah Hubungan Kunci Asing
• Menghapus Hubungan Kunci Asing
• Lihat Properti Kunci Asing
• Nonaktifkan Batasan Kunci Asing untuk Replikasi
• Nonaktifkan Batasan Kunci Asing dengan Pernyataan INSERT dan UPDATE