Batasan Kunci Utama dan Asing
Berlaku untuk:
SQL Server 2016 (13.x) dan Azure SQL DatabaseAzure 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 mungkin 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 ProductIDVendorID. 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 operasi DELETE DML. Operasi UPDATE 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.
TIDAK ADA TINDAKAN
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 pemicu ALIH-ALIH DELETE. ON UPDATE CASCADE tidak dapat ditentukan untuk tabel yang memiliki PEMicu ALIH-ALIH PEMBARUAN.
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 ALIH-ALIH pemicu PEMBARUAN.
ATUR 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 ALIH-ALIH pemicu PEMBARUAN.
CASCADE, SET NULL, SET DEFAULT dan NO ACTION dapat digabungkan pada tabel yang memiliki hubungan referensial satu sama lain. Jika Mesin Database tidak menemukan TINDAKAN, mesin akan berhenti dan mengembalikan tindakan CASCADE terkait, ATUR NULL, dan ATUR DEFAULT. Saat pernyataan DELETE menyebabkan kombinasi tindakan CASCADE, SET NULL, SET DEFAULT, dan NO ACTION, semua tindakan CASCADE, SET NULL, dan SET DEFAULT diterapkan sebelum Mesin Database memeriksa TINDAKAN TIDAK ADA.
Pemicu dan Tindakan Referensial Berjencar
Tindakan referensial berjentik mengaktifkan pemicu AFTER UPDATE atau AFTER DELETE dengan cara berikut:
Semua tindakan referensial bertingkat yang secara langsung disebabkan oleh DELETE atau UPDATE asli dilakukan terlebih dahulu.
Jika ada pemicu AFTER yang ditentukan pada tabel yang terpengaruh, pemicu ini akan 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 tindakan UPDATE 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 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 operasi UPDATE atau DELETE pada tabel lain, tindakan ini dapat memulai rantai berskala sekunder. Rantai sekunder ini diproses untuk setiap operasi UPDATE atau DELETE pada satu waktu setelah semua pemicu pada semua rantai utama diaktifkan. Proses ini mungkin berulang secara rekursif untuk operasi UPDATE atau DELETE berikutnya.
Melakukan operasi CREATE, ALTER, DELETE, atau bahasa definisi data (DDL) lainnya di dalam pemicu dapat menyebabkan pemicu DDL diaktifkan. Ini kemudian dapat 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 pemicu AFTER yang ditembakkan dalam rantai tersebut, dan operasi DELETE atau UPDATE yang membuat rantai digulung balik.
Tabel yang memiliki pemicu ALIH-ALIH TIDAK dapat juga memiliki klausul REFERENCES yang menentukan tindakan berjentik. Namun, pemicu AFTER pada tabel yang ditargetkan oleh tindakan berskala dapat menjalankan pernyataan INSERT, UPDATE, atau DELETE pada tabel atau tampilan lain yang mengaktifkan pemicu ALIH-ALIH ditentukan pada objek tersebut.
Langkah berikutnya
Tabel berikut mencantumkan tugas umum yang terkait dengan kunci primer dan batasan kunci asing.
| Tugas | Artikel |
|---|---|
| Menjelaskan cara membuat kunci primer. | Membuat Kunci Primer |
| Menjelaskan cara menghapus kunci primer. | Hapus Kunci Primer |
| Menjelaskan cara mengubah kunci primer. | Ubah Kunci Primer |
| Menjelaskan cara membuat hubungan kunci asing | Membuat Hubungan Kunci Asing |
| Menjelaskan cara memodifikasi hubungan kunci asing. | Ubah Hubungan Kunci Asing |
| Menjelaskan cara menghapus hubungan kunci asing. | Menghapus Hubungan Kunci Asing |
| Menjelaskan cara melihat properti kunci asing. | Lihat Properti Kunci Asing |
| Menjelaskan cara menonaktifkan batasan kunci asing untuk replikasi. | Nonaktifkan Batasan Kunci Asing untuk Replikasi |
| Menjelaskan cara menonaktifkan batasan kunci asing selama pernyataan INSERT atau UPDATE. | Nonaktifkan Batasan Kunci Asing dengan Pernyataan INSERT dan UPDATE |
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk