Kunci asing

Dokumen ini menjelaskan kunci asing di Spanner, dan cara menggunakannya untuk menerapkan integritas referensial dalam database Anda. Topik berikut membantu Anda mempelajari kunci asing dan cara menggunakannya:

Ringkasan kunci asing di Spanner

Kunci asing menentukan hubungan antartabel. Anda dapat menggunakan kunci asing untuk memastikan integritas data hubungan ini di Spanner tetap terjaga.

Bayangkan Anda adalah developer utama untuk bisnis e-commerce. Anda sedang mendesain database untuk memproses pesanan pelanggan. Database harus menyimpan informasi tentang setiap pesanan, pelanggan, dan produk. Gambar 1 mengilustrasikan struktur database dasar untuk aplikasi.

Struktur dasar database pemrosesan pesanan.

Gambar 1. Diagram database pemrosesan pesanan

Anda menentukan tabel Customers untuk menyimpan informasi pelanggan, tabel Orders untuk melacak semua pesanan, dan tabel Products untuk menyimpan informasi tentang setiap produk.

Gambar 1 juga menunjukkan link di antara tabel yang dipetakan ke hubungan dunia nyata berikut:

  • Pelanggan melakukan pemesanan.

  • Pesanan dibuat untuk suatu produk.

Anda memutuskan bahwa database Anda menerapkan aturan berikut untuk memastikan bahwa pesanan dalam sistem Anda valid.

  • Anda tidak dapat membuat pesanan untuk pelanggan yang tidak ada.

  • Pelanggan tidak dapat melakukan pemesanan untuk produk yang tidak Anda sediakan.

Saat menerapkan aturan, atau batasan ini, Anda mempertahankan integritas referensial data Anda. Jika database mempertahankan integritas referensial, semua upaya untuk menambahkan data yang tidak valid, yang akan menghasilkan link atau referensi yang tidak valid di antara data, akan gagal. Integritas referensial mencegah error pengguna. Secara default, Spanner menggunakan kunci asing untuk menerapkan integritas referensial.

Menentukan integritas referensial dengan kunci asing

Berikut ini adalah contoh pemrosesan pesanan lagi, dengan detail lebih lanjut yang ditambahkan ke desain, seperti yang ditunjukkan pada Gambar 2.

Skema database dengan kunci asing

Gambar 2. Diagram skema database dengan kunci asing

Desain kini menampilkan nama dan jenis kolom di setiap tabel. Tabel Orders juga menentukan dua hubungan kunci asing. FK_CustomerOrder mengharapkan semua baris di Orders memiliki CustomerId yang valid. Kunci asing FK_ProductOrder mengharapkan semua nilai ProductId dalam tabel Orders valid. Tabel berikut memetakan batasan ini kembali ke aturan dunia nyata yang ingin Anda terapkan.

Nama Kunci Asing Batasan Deskripsi dunia nyata
FK_CustomerOrder Mengharapkan semua baris di Orders memiliki CustomerId yang valid Pelanggan yang valid melakukan pemesanan
FK_ProductOrder Mengharapkan semua baris di Orders memiliki ProductId yang valid Pesanan dibuat untuk produk yang valid

Spanner menerapkan batasan yang ditentukan menggunakan kunci asing yang diterapkan. Artinya, Spanner akan menggagalkan transaksi apa pun yang mencoba menyisipkan atau memperbarui baris dalam tabel Orders yang memiliki CustomerId atau ProductId yang tidak ditemukan dalam tabel Customers dan Products. Tindakan ini juga menggagalkan transaksi yang mencoba memperbarui atau menghapus baris dalam tabel Customers dan Products yang akan membatalkan ID dalam tabel Orders. Untuk mengetahui detail selengkapnya tentang cara Spanner memvalidasi batasan, lihat bagian Validasi batasan transaksi.

Tidak seperti kunci asing yang diterapkan, Spanner tidak memvalidasi batasan pada kunci asing informasi. Artinya, jika Anda menggunakan kunci asing informasi dalam skenario ini, transaksi yang mencoba menyisipkan atau memperbarui baris dalam tabel Orders yang memiliki CustomerId atau ProductId yang tidak ditemukan dalam tabel Customers dan Products tidak divalidasi dan transaksi tidak gagal. Selain itu, tidak seperti kunci asing yang diterapkan, kunci asing informasi hanya didukung oleh GoogleSQL, dan tidak didukung oleh PostgreSQL.

Karakteristik kunci asing

Berikut adalah daftar karakteristik kunci asing di Spanner.

  • Tabel yang menentukan kunci asing adalah tabel referensi, dan kolom kunci asing adalah kolom referensi.

  • Kunci asing merujuk ke kolom yang direferensikan dari tabel yang direferensikan.

  • Seperti dalam contoh, Anda dapat memberi nama setiap batasan kunci asing. Jika Anda tidak menentukan nama, Spanner akan membuat nama untuk Anda. Anda dapat membuat kueri nama yang dihasilkan dari INFORMATION_SCHEMA Spanner. Nama batasan dicakup ke skema, bersama dengan nama untuk tabel dan indeks, dan harus unik dalam skema.

  • Jumlah kolom yang mereferensikan dan direferensikan harus sama. Urutan itu penting. Misalnya, kolom referensi pertama merujuk ke kolom yang direferensikan pertama dan kolom referensi kedua merujuk ke kolom yang direferensikan kedua.

  • Kolom referensi dan kolom yang direferensikan harus memiliki jenis yang sama. Anda harus dapat mengindeks kolom.

  • Anda tidak dapat membuat kunci asing pada kolom dengan opsi allow_commit_timestamp=true.

  • Kolom array tidak didukung.

  • Kolom JSON tidak didukung.

  • Kunci asing dapat mereferensikan kolom dari tabel yang sama (kunci asing referensi mandiri). Contohnya adalah tabel Employee dengan kolom ManagerId yang mereferensikan kolom EmployeeId tabel.

  • Kunci asing juga dapat membentuk hubungan sirkular antara tabel, dengan dua tabel saling mereferensikan, baik secara langsung maupun tidak langsung. Tabel yang direferensikan harus ada sebelum membuat kunci asing. Artinya, setidaknya satu kunci asing harus ditambahkan menggunakan pernyataan ALTER TABLE.

  • Kunci yang direferensikan harus unik. Spanner menggunakan PRIMARY KEY tabel yang direferensikan jika kolom yang direferensikan untuk kunci asing cocok dengan kolom kunci utama tabel yang direferensikan. Jika Spanner tidak dapat menggunakan kunci utama tabel yang direferensikan, Spanner akan membuat UNIQUE NULL_FILTERED INDEX di atas kolom yang direferensikan.

  • Kunci asing tidak menggunakan indeks sekunder yang telah Anda buat. Sebagai gantinya, mereka membuat indeks cadangan mereka sendiri. Indeks pendukung dapat digunakan dalam evaluasi kueri, termasuk dalam perintah force_index eksplisit. Anda dapat mengkueri nama indeks pendukung dari INFORMATION_SCHEMA Spanner. Untuk mengetahui informasi selengkapnya, lihat Indeks cadangan.

Jenis kunci asing

Ada dua jenis kunci asing, yaitu diterapkan dan informasional. Kunci asing yang diterapkan adalah default dan menerapkan integritas referensial. Kunci asing informasional tidak menerapkan integritas referensial dan paling baik digunakan untuk mendeklarasikan model data logis yang dimaksudkan untuk pengoptimalan kueri. Untuk mengetahui detail selengkapnya, lihat bagian kunci asing yang diterapkan dan informasional berikut serta tabel perbandingan jenis kunci asing.

Kunci asing yang diterapkan

Kunci asing yang diterapkan, jenis kunci asing default di Spanner, menerapkan integritas referensial. Karena kunci asing yang diterapkan menerapkan integritas referensial, kunci tersebut menyebabkan upaya untuk melakukan hal berikut gagal:

  • Menambahkan baris ke tabel yang merujuk yang memiliki nilai kunci asing yang tidak ada dalam tabel yang dirujuk akan gagal.

  • Penghapusan baris dari tabel yang direferensikan yang direferensikan oleh baris dalam tabel yang mereferensikan akan gagal.

Semua kunci asing PostgreSQL diterapkan. Kunci asing GoogleSQL diterapkan secara default. Karena kunci asing diterapkan secara default, penggunaan kata kunci ENFORCED untuk menentukan bahwa kunci asing GoogleSQL diterapkan bersifat opsional.

Kunci asing informasi

Kunci asing informasional digunakan untuk mendeklarasikan model data logis yang dimaksudkan untuk pengoptimalan kueri. Meskipun kunci tabel yang direferensikan harus unik untuk kunci asing informasi, integritas referensial tidak diterapkan. Jika Anda ingin memvalidasi integritas referensial secara selektif saat menggunakan kunci asing informasi, Anda harus mengelola logika validasi di sisi klien. Untuk mengetahui informasi selengkapnya, lihat Menggunakan kunci asing informasi.

Gunakan kata kunci NOT ENFORCED untuk menentukan bahwa kunci asing GoogleSQL bersifat informasional. PostgreSQL tidak mendukung kunci asing informasi.

Perbandingan jenis kunci asing

Diterapkan dan informasional memiliki manfaat. Bagian berikut membandingkan dua jenis kunci asing dan menyertakan beberapa praktik terbaik.

Perbedaan kunci asing tingkat tinggi

Secara umum, berikut adalah beberapa perbedaan antara kunci asing yang diterapkan dan informasional:

  • Penegakan. Kunci asing yang diterapkan memvalidasi dan menjamin integritas referensial pada penulisan. Kunci asing informasi tidak memvalidasi atau menjamin integritas referensial.

  • Penyimpanan. Kunci asing yang diterapkan mungkin memerlukan penyimpanan tambahan untuk indeks pendukung pada tabel yang dibatasi.

  • Throughput tulis. Kunci asing yang diterapkan mungkin menimbulkan lebih banyak overhead di jalur tulis daripada kunci asing informasi.

  • Pengoptimalan kueri. Kedua jenis kunci asing dapat digunakan untuk pengoptimalan kueri. Jika pengoptimal diizinkan untuk menggunakan kunci asing informasi, hasil kueri mungkin tidak mencerminkan data sebenarnya jika data tidak cocok dengan hubungan kunci asing informasi (misalnya, jika beberapa kunci yang dibatasi tidak memiliki kunci yang direferensikan yang cocok dalam tabel yang direferensikan).

Tabel perbedaan kunci asing

Tabel berikut mencantumkan perbedaan mendetail antara kunci asing yang diterapkan dan kunci asing informasi:

Kunci asing yang diterapkan Kunci asing informasi
Kata kunci ENFORCED NOT ENFORCED
Didukung oleh GoogleSQL Ya. Kunci asing di GoogleSQL diterapkan secara default. Ya.
Didukung oleh PostgreSQL Ya. Kunci asing di PostgreSQL hanya dapat diterapkan. Tidak.
Penyimpanan Kunci asing yang diterapkan memerlukan penyimpanan hingga dua indeks cadangan. Kunci asing informasi memerlukan penyimpanan hingga satu indeks pendukung.
Membuat indeks pendukung pada kolom tabel yang direferensikan jika diperlukan Ya. Ya.
Membuat indeks pendukung pada kolom tabel referensi jika diperlukan Ya. Tidak.
Dukungan tindakan kunci asing Ya. Tidak.
Memvalidasi dan menerapkan integritas referensial Ya. Tidak. Tidak adanya validasi akan meningkatkan performa penulisan, tetapi dapat memengaruhi hasil kueri saat kunci asing informasi digunakan untuk pengoptimalan kueri. Anda dapat menggunakan validasi sisi klien atau kunci asing yang diterapkan untuk memastikan integritas referensial.

Memilih jenis kunci asing yang akan digunakan

Anda dapat menggunakan panduan berikut untuk memutuskan jenis kunci asing yang akan digunakan:

Sebaiknya mulai dengan kunci asing yang diterapkan. Kunci asing yang diterapkan menjaga data dan model logis tetap konsisten setiap saat. Kunci asing yang diterapkan adalah opsi yang direkomendasikan, kecuali jika tidak sesuai untuk kasus penggunaan Anda.

Sebaiknya pertimbangkan kunci asing informasi jika masing-masing hal berikut benar:

  • Anda ingin menggunakan model data logis yang dijelaskan oleh kunci asing informasi dalam pengoptimalan kueri.

  • Mempertahankan integritas referensial yang ketat tidak praktis atau berdampak signifikan pada performa. Berikut adalah contoh kapan Anda mungkin ingin mempertimbangkan untuk menggunakan kunci asing informasi:

    • Sumber data upstream Anda mengikuti model konsistensi tertunda. Dalam hal ini, update yang dibuat di sistem sumber mungkin tidak langsung terlihat di Spanner. Karena update mungkin tidak langsung, inkonsistensi singkat dalam hubungan kunci asing mungkin terjadi.

    • Data Anda berisi baris yang direferensikan yang memiliki banyak hubungan referensi. Pembaruan pada baris ini dapat menggunakan banyak resource karena Spanner harus memvalidasi atau, dalam beberapa kasus, menghapus semua baris yang terkait dengan pemeliharaan integritas referensial. Dalam skenario ini, update mungkin memengaruhi performa Spanner dan memperlambat transaksi serentak.

  • Aplikasi Anda dapat menangani potensi inkonsistensi data dan dampaknya terhadap hasil kueri.

Menggunakan kunci asing informasi

Topik berikut hanya untuk kunci asing informasi. Untuk topik yang berlaku untuk kunci asing informasional dan yang diterapkan, lihat referensi berikut:

Membuat tabel baru dengan kunci asing informasi

Anda membuat dan menghapus kunci asing informasi dari database Spanner menggunakan pernyataan DDL. Anda menambahkan kunci asing ke tabel baru dengan pernyataan CREATE TABLE. Demikian pula, Anda dapat menambahkan atau menghapus kunci asing dari tabel yang ada dengan pernyataan ALTER TABLE.

Contoh berikut membuat tabel baru dengan kunci asing informasi menggunakan GoogleSQL. Kunci asing informasi tidak didukung oleh PostgreSQL.

GoogleSQL 

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  ProductId INT64 NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
   REFERENCES Customers (CustomerId) NOT ENFORCED
 ) PRIMARY KEY (OrderId);

PostgreSQL 

Not Supported

Untuk contoh selengkapnya tentang cara membuat dan mengelola kunci asing, lihat Membuat dan mengelola hubungan kunci asing. Untuk mengetahui informasi selengkapnya tentang pernyataan DDL, lihat referensi DDL.

Menggunakan kunci asing informasi untuk pengoptimalan kueri

Kunci asing yang diterapkan dan kunci asing informasi dapat digunakan oleh pengoptimal kueri untuk meningkatkan performa kueri. Dengan menggunakan kunci asing informasi, Anda dapat memanfaatkan rencana kueri yang dioptimalkan tanpa overhead penegakan integritas referensial yang ketat.

Jika Anda mengaktifkan pengoptimal kueri untuk menggunakan informasi kunci asing informasional, penting untuk dipahami bahwa kebenaran pengoptimalan bergantung pada ketersediaan data yang konsisten dengan model logis yang dijelaskan oleh kunci asing informasional. Jika terjadi inkonsistensi, hasil kueri mungkin tidak mencerminkan data yang sebenarnya. Contoh inkonsistensi adalah saat nilai dalam kolom yang dibatasi tidak memiliki nilai yang cocok dalam kolom yang direferensikan.

Secara default, pengoptimal kueri menggunakan kunci asing NOT ENFORCED. Untuk mengubahnya, tetapkan opsi database use_unenforced_foreign_key_for_query_optimization ke false. Berikut adalah contoh GoogleSQL yang menunjukkan hal ini (kunci asing informasi tidak tersedia di PostgreSQL):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

Petunjuk pernyataan kueri boolean @{use_unenforced_foreign_key} mengganti opsi database per kueri yang mengontrol apakah pengoptimal menggunakan kunci asing NOT ENFORCED. Menonaktifkan petunjuk ini atau opsi database dapat berguna saat memecahkan masalah hasil kueri yang tidak terduga. Berikut ini menunjukkan cara menggunakan @{use_unenforced_foreign_key}:

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

Menggunakan kunci asing yang diterapkan

Topik berikut hanya untuk kunci asing yang diterapkan. Untuk topik yang berlaku untuk kunci asing informasional dan yang diterapkan, lihat referensi berikut:

Membuat tabel baru dengan kunci asing yang diterapkan

Anda membuat dan menghapus kunci asing yang diterapkan dari database Spanner menggunakan DDL. Anda menambahkan kunci asing ke tabel baru dengan pernyataan CREATE TABLE. Demikian pula, Anda dapat menambahkan kunci asing ke, atau menghapus kunci asing dari, tabel yang ada dengan pernyataan ALTER TABLE.

Anda membuat dan menghapus kunci asing dari database Spanner menggunakan DDL. Anda menambahkan kunci asing ke tabel baru dengan pernyataan CREATE TABLE. Demikian pula, Anda menambahkan kunci asing ke, atau menghapus kunci asing dari, tabel yang ada dengan pernyataan ALTER TABLE.

Berikut adalah contoh pembuatan tabel baru dengan kunci asing yang diterapkan.

GoogleSQL 

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE Orders (
OrderId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductId INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
  REFERENCES Customers (CustomerId) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL 

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

CREATE TABLE Orders (
OrderId BIGINT NOT NULL,
CustomerId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
  REFERENCES Customers (CustomerId),
PRIMARY KEY (OrderId)
);

Untuk contoh selengkapnya tentang cara membuat dan mengelola kunci asing, lihat Membuat dan mengelola hubungan kunci asing.

Tindakan kunci asing

Tindakan kunci asing hanya dapat ditentukan pada kunci asing yang diterapkan.

Tindakan kunci asing mengontrol apa yang terjadi pada kolom yang dibatasi saat kolom yang dirujuknya dihapus atau diperbarui. Spanner mendukung penggunaan tindakan ON DELETE CASCADE dan ON DELETE SET NULL.

Dengan tindakan ON DELETE CASCADE, saat Anda menghapus baris yang direferensikan oleh kunci asing, semua baris yang mereferensikan kunci tersebut juga akan dihapus dalam transaksi yang sama.

Dengan tindakan ON DELETE SET NULL, saat Anda menghapus baris yang direferensikan oleh kunci asing, kolom referensi di semua baris yang mereferensikan kunci tersebut akan ditetapkan ke NULL dalam transaksi yang sama.

Anda dapat menambahkan kunci asing dengan tindakan saat membuat database menggunakan DDL. Gunakan pernyataan CREATE TABLE untuk menambahkan kunci asing dengan tindakan ke tabel baru. Demikian pula, Anda dapat menggunakan pernyataan ALTER TABLE untuk menambahkan tindakan kunci asing ke tabel yang ada atau untuk menghapus tindakan kunci asing. Berikut adalah contoh cara membuat tabel baru dengan tindakan kunci asing.

GoogleSQL 

CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL 

CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
  REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

Berikut adalah daftar karakteristik tindakan kunci asing di Spanner.

  • Tindakan kunci asing adalah ON DELETE CASCADE, ON DELETE SET NULL, atau ON DELETE NO ACTION.

  • Anda tidak dapat menggunakan ON DELETE SET NULL pada kolom referensi yang merupakan bagian dari kunci utama tabel referensi, yang ditandai NOT NULL, atau kolom yang dihasilkan.

  • Anda dapat mengkueri INFORMATION_SCHEMA untuk menemukan batasan kunci asing yang memiliki tindakan.

  • Menambahkan tindakan kunci asing pada batasan kunci asing yang ada tidak didukung. Anda harus menambahkan batasan kunci asing baru dengan tindakan.

Validasi batasan

Validasi batasan hanya berlaku untuk kunci asing yang diterapkan.

Spanner memvalidasi batasan kunci asing yang diterapkan saat transaksi di-commit, atau saat efek penulisan terlihat oleh operasi berikutnya dalam transaksi.

Nilai yang disisipkan ke dalam kolom referensi dicocokkan dengan nilai tabel yang direferensikan dan kolom yang direferensikan. Baris dengan nilai referensi NULL tidak diperiksa, yang berarti Anda dapat menambahkannya ke tabel referensi.

Spanner memvalidasi semua batasan referensial kunci asing yang diterapkan dan berlaku saat mencoba memperbarui data menggunakan pernyataan DML atau API. Semua perubahan yang tertunda akan di-roll back jika ada batasan yang tidak valid.

Validasi terjadi segera setelah setiap pernyataan DML. Misalnya, Anda harus menyisipkan baris yang direferensikan sebelum menyisipkan baris yang mereferensikannya. Saat menggunakan API mutasi, mutasi akan di-buffer hingga transaksi di-commit. Validasi kunci asing yang diterapkan ditangguhkan hingga transaksi di-commit. Dalam hal ini, Anda diizinkan untuk menyisipkan baris referensi terlebih dahulu.

Setiap transaksi dievaluasi untuk modifikasi yang memengaruhi batasan kunci asing yang diterapkan. Evaluasi ini mungkin memerlukan permintaan tambahan ke server. Indeks pendukung juga memerlukan waktu pemrosesan tambahan untuk mengevaluasi modifikasi transaksi dan untuk mempertahankan indeks. Penyimpanan tambahan juga diperlukan untuk setiap indeks.

Tindakan penghapusan bertingkat yang berjalan lama

Saat Anda menghapus baris dari tabel yang direferensikan, Spanner harus menghapus semua baris dalam tabel yang mereferensikan baris yang dihapus. Hal ini dapat menyebabkan efek beruntun, di mana satu operasi penghapusan menghasilkan ribuan operasi penghapusan lainnya. Menambahkan batasan kunci asing dengan tindakan penghapusan bertingkat ke tabel atau membuat tabel dengan batasan kunci asing dengan tindakan penghapusan bertingkat dapat memperlambat operasi penghapusan.

Tindakan hapus set null yang berjalan lama

Saat Anda menghapus baris dari tabel yang direferensikan, Spanner akan memperbarui semua baris dalam tabel yang mereferensikan yang memiliki tindakan ON DELETE SET NULL dengan menetapkan kolom yang mereferensikan ke NULL. Hal ini dapat menyebabkan efek beruntun, di mana satu operasi penghapusan menghasilkan ribuan operasi pembaruan. Menambahkan batasan kunci asing ON DELETE SET NULL ke tabel atau membuat tabel dengan batasan tersebut dapat memperlambat operasi penghapusan.

Batas mutasi terlampaui untuk penghapusan bertingkat kunci asing

Menghapus data dalam jumlah besar menggunakan penghapusan bertingkat kunci asing dapat memengaruhi performa. Hal ini karena setiap data yang dihapus akan memicu penghapusan semua data yang terkait dengannya. Jika Anda perlu menghapus banyak data menggunakan penghapusan bertingkat kunci asing, hapus baris dari tabel turunan secara eksplisit sebelum menghapus baris dari tabel induk. Hal ini mencegah transaksi gagal karena batas mutasi.

Batas mutasi terlampaui untuk penghapusan kunci asing yang ditetapkan ke null

Menghapus baris yang memicu sejumlah besar update menggunakan tindakan ON DELETE SET NULL kunci asing dapat memengaruhi performa. Hal ini karena setiap baris yang dihapus dalam tabel yang direferensikan akan memicu pembaruan semua baris yang mereferensikannya dalam tabel yang mereferensikan. Jika Anda perlu menghapus baris yang memicu banyak update menggunakan ON DELETE SET NULL, perbarui baris yang mereferensikan secara eksplisit ke NULL atau hapus baris tersebut sebelum menghapus baris yang direferensikan. Praktik ini mencegah transaksi gagal karena batas mutasi.

Penggunaan resource untuk tindakan penghapusan kunci asing

ON DELETE CASCADE dan ON DELETE SET NULL memiliki biaya resource dan mutasi yang berbeda, bergantung pada skema Anda:

  • Mutasi tabel dasar: ON DELETE SET NULL memperbarui setiap kolom, sehingga setiap baris referensi yang diperbarui dihitung sebagai beberapa mutasi (untuk kunci baris dan setiap kolom yang ditetapkan ke NULL). ON DELETE CASCADE dihitung sebagai satu mutasi per baris yang dihapus, atau satu mutasi per rentang kunci yang dihapus jika kolom referensi adalah awalan dari kunci utama tabel referensi.
  • Mutasi indeks: Kedua tindakan menggunakan satu mutasi penghapusan per baris pada indeks cadangan kunci asing. Untuk indeks sekunder lainnya pada tabel referensi, ON DELETE SET NULL hanya mengubah indeks yang menyertakan kolom referensi, sedangkan ON DELETE CASCADE menghapus entri dari setiap indeks sekunder pada tabel.
  • Pembacaan: ON DELETE SET NULL selalu membaca indeks pendukung kunci asing untuk menemukan baris yang cocok. ON DELETE CASCADE dapat melewati pembacaan ini dan menghapus rentang kunci secara langsung saat kolom yang mereferensikan adalah awalan dari kunci utama tabel yang mereferensikan.
  • Cascading: ON DELETE SET NULL tidak pernah di-cascade ke tabel turunan yang disisipkan karena kolom kunci utama tidak dapat ditetapkan ke NULL. Tindakan ini hanya memicu tindakan lebih lanjut jika kunci asing lain mereferensikan kolom yang diperbarui. ON DELETE CASCADE menghapus baris referensi lengkap, sehingga juga di-cascade ke tabel turunan yang disisipkan dengan ON DELETE CASCADE dan ke kunci asing downstream.

Perbandingan kunci asing yang diterapkan dan penyisipan tabel

Penyisipan tabel Spanner adalah pilihan yang baik untuk banyak hubungan induk-turunan, dengan kunci utama tabel turunan menyertakan kolom kunci utama tabel induk. Penempatan bersama baris turunan dengan baris induknya dapat meningkatkan performa secara signifikan.

Kunci asing adalah solusi induk-turunan yang lebih umum dan menangani kasus penggunaan tambahan. Kunci asing tidak terbatas pada kolom kunci utama, dan tabel dapat memiliki beberapa hubungan kunci asing, baik sebagai induk dalam beberapa hubungan maupun turunan dalam hubungan lainnya. Namun, relasi kunci asing tidak menyiratkan penempatan bersama tabel di lapisan penyimpanan.

Pertimbangkan contoh yang menggunakan tabel Orders yang ditentukan sebagai berikut:

Skema database dengan kunci asing

Gambar 3. Diagram skema database dengan kunci asing yang diterapkan

Desain pada Gambar 3 memiliki beberapa batasan. Misalnya, setiap pesanan hanya dapat berisi satu item pesanan.

Bayangkan pelanggan Anda ingin dapat memesan lebih dari satu produk per pesanan. Anda dapat meningkatkan desain dengan memperkenalkan tabel OrderItems yang berisi entri untuk setiap produk yang dipesan pelanggan. Anda dapat memperkenalkan kunci asing lain yang diterapkan untuk merepresentasikan hubungan one-to-many baru ini antara Orders dan OrderItems. Namun, Anda juga tahu bahwa Anda sering ingin menjalankan kueri di seluruh pesanan dan item pesanan masing-masing. Karena penempatan bersama data ini meningkatkan performa, Anda perlu membuat hubungan induk-turunan menggunakan kemampuan penyisipan tabel Spanner.

Berikut cara menentukan tabel OrderItems, yang disisipkan dengan Orders.

GoogleSQL 

CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

CREATE TABLE OrderItems (
OrderId INT64 NOT NULL,
ProductId INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId)
) PRIMARY KEY (OrderId, ProductId),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

PostgreSQL 

CREATE TABLE Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

CREATE TABLE OrderItems (
OrderId BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId),
PRIMARY KEY (OrderId, ProductId)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

Gambar 4 adalah representasi visual dari skema database yang diperbarui sebagai hasil dari pengenalan tabel baru ini, OrderItems, yang disisipkan dengan Orders. Di sini, Anda juga dapat melihat hubungan one-to-many antara kedua tabel tersebut.

Skema database yang menunjukkan hubungan one-to-many antara Orders dan tabel OrderItems baru yang disisipkan

Gambar 4. Penambahan tabel OrderItems yang disisipkan

Dalam konfigurasi ini, Anda dapat memiliki beberapa entri OrderItems di setiap pesanan, dan entri OrderItems untuk setiap pesanan disisipkan, sehingga ditempatkan bersama dengan pesanan. Melakukan interleaving Orders dan OrderItems secara fisik dengan cara ini dapat meningkatkan performa, melakukan pra-penggabungan tabel secara efektif, dan memungkinkan Anda mengakses baris terkait secara bersamaan sekaligus meminimalkan akses disk. Misalnya, Spanner dapat melakukan penggabungan berdasarkan kunci utama secara lokal, sehingga meminimalkan akses disk dan traffic jaringan.

Jika jumlah mutasi dalam transaksi melebihi 80.000, transaksi akan gagal. Penghapusan bertingkat yang besar seperti ini berfungsi dengan baik untuk tabel dengan hubungan "disisipkan di induk", tetapi tidak untuk tabel dengan hubungan kunci asing. Jika Anda memiliki hubungan kunci asing dan perlu menghapus sejumlah besar baris, Anda harus menghapus baris dari tabel turunan secara eksplisit terlebih dahulu.

Jika Anda memiliki tabel pengguna dengan hubungan kunci asing ke tabel lain, dan menghapus baris dari tabel yang direferensikan akan memicu penghapusan jutaan baris, Anda harus mendesain skema dengan tindakan penghapusan bertingkat dengan "disisipkan di induk".

Tabel perbandingan

Tabel berikut merangkum perbandingan antara kunci asing yang diterapkan dan penyisipan tabel. Anda dapat menggunakan informasi ini untuk memutuskan apa yang tepat untuk desain Anda.

Jenis hubungan induk-turunan Interleaving Tabel Kunci asing yang diterapkan
Dapat menggunakan kunci utama Ya Ya
Dapat menggunakan kolom non-kunci utama Tidak Ya
Jumlah orang tua yang didukung 0 .. 1 0 .. N
Menyimpan data induk dan turunan bersama-sama Ya Tidak
Mendukung penghapusan beruntun Ya Ya
Mode pencocokan null Lulus jika semua nilai referensi tidak berbeda dengan nilai yang direferensikan.
Nilai null tidak berbeda dengan nilai null; nilai null berbeda dengan nilai non-null.		 Berhasil jika ada nilai referensi yang null.
Berhasil jika semua nilai referensi bukan null, dan tabel yang direferensikan memiliki baris dengan nilai yang sama dengan nilai referensi.
Gagal jika tidak ada baris yang cocok yang ditemukan.
Waktu Penegakan Kebijakan Per operasi saat menggunakan API mutasi.
Per pernyataan saat menggunakan DML.		 Per transaksi saat menggunakan API mutasi.
Per pernyataan saat menggunakan DML.
Dapat dihapus Tidak. Penyisipan tabel tidak dapat dihapus setelah dibuat, kecuali jika Anda menghapus seluruh tabel turunan. Ya

Indeks pendukung

Kunci asing tidak menggunakan indeks buatan pengguna. Namun, mereka membuat indeks cadangan mereka sendiri. Kunci asing yang diterapkan dan informasional membuat indeks pendukung secara berbeda di Spanner:

  • Untuk kunci asing yang diterapkan, Spanner dapat membuat hingga dua indeks pendukung sekunder untuk setiap kunci asing, satu untuk kolom referensi, dan yang kedua untuk kolom yang direferensikan.

  • Untuk kunci asing informasi, Spanner dapat membuat hingga satu indeks pendukung jika diperlukan untuk kolom yang direferensikan. Kunci asing informasi tidak membuat indeks pendukung untuk kolom referensi.

Untuk kunci asing yang diterapkan dan bersifat informasi, kunci asing biasanya merujuk ke kunci utama tabel yang dirujuk, sehingga indeks untuk tabel yang dirujuk biasanya tidak diperlukan. Oleh karena itu, kunci asing informasi biasanya tidak memiliki indeks pendukung. Jika diperlukan, indeks pendukung yang dibuat untuk tabel yang direferensikan adalah indeks UNIQUE NULL_FILTERED. Pembuatan kunci asing akan gagal jika ada data yang melanggar batasan keunikan indeks.

Kunci asing informasi tidak memiliki indeks pendukung untuk tabel referensi. Untuk kunci asing yang diterapkan, indeks pendukung untuk tabel referensi adalah NULL_FILTERED.

Jika dua atau beberapa kunci asing memerlukan indeks pendukung yang sama, Spanner akan membuat satu indeks untuk masing-masing kunci asing tersebut. Indeks cadangan akan dihapus saat kunci asing yang menggunakannya dihapus. Anda tidak dapat mengubah atau menghapus indeks cadangan.

Spanner menggunakan skema informasi dari setiap database untuk menyimpan metadata tentang indeks pendukung. Baris dalam INFORMATION_SCHEMA.INDEXES yang memiliki nilai SPANNER_IS_MANAGED true menjelaskan indeks cadangan.

Di luar kueri SQL yang langsung memanggil skema informasi, konsolGoogle Cloud tidak menampilkan informasi apa pun tentang indeks pendukung database.

Perubahan skema yang berjalan lama

Menambahkan kunci asing yang diterapkan ke tabel yang ada, atau membuat tabel baru dengan kunci asing, dapat menyebabkan operasi yang berjalan lama. Jika tabel baru, tabel tidak dapat ditulis hingga operasi yang berjalan lama selesai.

Tabel berikut menunjukkan apa yang terjadi di Spanner saat kunci asing yang diterapkan dan informasional berada dalam tabel baru atau yang sudah ada:

Jenis tabel Kunci asing yang diterapkan Kunci asing informasi
Baru Spanner mengisi ulang indeks yang direferensikan sesuai kebutuhan untuk setiap kunci asing. Spanner mengisi ulang indeks yang direferensikan sesuai kebutuhan untuk setiap kunci asing.
Yang sudah ada Spanner mengisi ulang indeks referensi dan yang direferensikan sesuai kebutuhan. Spanner juga memvalidasi data yang ada dalam tabel untuk memastikan bahwa data tersebut mematuhi batasan integritas referensial kunci asing. Perubahan skema akan gagal jika ada data yang tidak valid. Spanner mengisi ulang indeks yang direferensikan sesuai kebutuhan dan tidak memvalidasi data yang ada dalam tabel.

Opsi berikut tidak didukung:

  • Menambahkan tindakan kunci asing ke batasan kunci asing yang diterapkan yang ada.
  • Mengubah penerapan kunci asing yang ada.

Untuk kedua kasus tersebut, sebaiknya lakukan hal berikut:

  1. Tambahkan batasan baru dengan tindakan atau penerapan yang diperlukan.
  2. Hapus batasan lama.

Menambahkan batasan baru dan menghapus batasan lama akan mencegah masalah Operasi Pengubahan Batasan yang Berjalan Lama. Misalnya, Anda ingin menambahkan tindakan DELETE CASCADE pada kunci asing yang ada. Setelah Anda membuat kunci asing baru dengan tindakan ON DELETE CASCADE, efek dari kedua batasan tersebut adalah tindakan DELETE CASCADE. Kemudian, Anda dapat menghapus batasan lama dengan aman.

Menghapus batasan dapat menyebabkan penghapusan indeks cadangan kunci asing jika indeks tidak digunakan oleh batasan kunci asing lainnya. Oleh karena itu, jika Anda menghapus batasan lama terlebih dahulu, menambahkan batasan kunci asing yang sama dengan tindakan nanti dapat menyebabkan operasi yang berjalan lama, seperti mengisi ulang indeks, memvalidasi batasan indeks unik, atau memvalidasi batasan referensial kunci asing.

Anda dapat membuat kueri INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE untuk memeriksa status pembuatan kunci asing.

Langkah berikutnya