Kunci asing

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

• Ringkasan kunci asing di Spanner
• Jenis kunci asing
• Perbandingan jenis kunci asing
• Memilih jenis kunci asing yang akan digunakan
• Menggunakan kunci asing yang diterapkan
• Menggunakan kunci asing informasi
• Indeks pendukung
• Perubahan skema yang berjalan lama

Ringkasan kunci asing di Spanner

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

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.

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 antartabel yang dipetakan ke hubungan dunia nyata berikut:

• Pelanggan melakukan pemesanan.

• Pesanan dilakukan untuk sebuah 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 memesan produk yang tidak Anda jual.

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

Menentukan integritas referensi dengan kunci asing

Berikut ini pemeriksaan ulang contoh pemrosesan pesanan, dengan detail yang lebih banyak ditambahkan ke desain, seperti yang ditunjukkan pada Gambar 2.

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. Ini berarti bahwa Spanner akan menggagalkan transaksi apa pun yang mencoba menyisipkan atau memperbarui baris di tabel Orders yang memiliki CustomerId atau ProductId yang tidak ditemukan di tabel Customers dan Products. Hal ini juga akan membuat transaksi yang mencoba memperbarui atau menghapus baris di tabel Customers dan Products gagal, yang akan membatalkan ID di 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 di tabel Orders yang memiliki CustomerId atau ProductId yang tidak ditemukan di 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, bukan 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 mereferensikan kolom yang dirujuk dari tabel yang dirujuk.

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

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

• Kolom pereferensi dan kolom yang dirujuk harus berjenis 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 mereferensikan diri). Contohnya adalah tabel Employee dengan kolom ManagerId yang mereferensikan kolom EmployeeId tabel.

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

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

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

Jenis kunci asing

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

Kunci asing yang diterapkan

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

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

• Menghapus baris dari tabel yang direferensikan yang direferensikan oleh baris dalam tabel pereferensian 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 diinginkan untuk pengoptimalan kueri. Meskipun kunci tabel yang dirujuk harus unik untuk kunci asing informasi, integritas referensi tidak diterapkan. Jika ingin memvalidasi integritas referensi secara selektif saat menggunakan kunci asing informasi, Anda harus mengelola logika validasi di sisi klien. Untuk informasi selengkapnya, lihat Menggunakan kunci asing informasi.

Gunakan kata kunci NOT ENFORCED untuk menentukan bahwa kunci asing GoogleSQL adalah informasi. PostgreSQL tidak mendukung kunci asing informatif.

Perbandingan jenis kunci asing

Baik yang diterapkan maupun informatif 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 informatif:

• Penegakan. Kunci asing yang diterapkan memvalidasi dan menjamin integritas referensional pada operasi tulis. Kunci asing informasi tidak memvalidasi atau menjamin integritas referensi.

• 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 terbatas 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 informatif:

	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 pendukung.	Kunci asing informasi memerlukan penyimpanan hingga satu indeks pendukung.
Membuat indeks pendukung pada kolom tabel yang dirujuk 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 operasi tulis, tetapi dapat memengaruhi hasil kueri saat kunci asing informatif digunakan untuk pengoptimalan kueri. Anda dapat menggunakan validasi sisi klien atau kunci asing yang diterapkan untuk memastikan integritas referensi.

Memilih jenis kunci asing yang akan digunakan

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

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

Sebaiknya pertimbangkan kunci asing informasi jika setiap 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 memengaruhi performa secara signifikan. Berikut adalah contoh kapan Anda mungkin ingin mempertimbangkan penggunaan kunci asing informasi:

  • Sumber data upstream Anda mengikuti model konsistensi akhir. Dalam hal ini, pembaruan yang dilakukan di sistem sumber mungkin tidak segera ditampilkan 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 pereferensian. Pembaruan pada baris ini dapat menggunakan banyak resource karena Spanner harus memvalidasi atau, dalam beberapa kasus, menghapus semua baris yang terkait dengan pemeliharaan integritas referensi. Dalam skenario ini, update dapat 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 informatif dan yang diterapkan, lihat topik berikut:

• Perubahan skema yang berjalan lama
• Indeks pendukung

Membuat tabel baru dengan kunci asing informasi

Anda membuat dan menghapus serta 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 informasi selengkapnya tentang pernyataan DDL, lihat referensi DDL.

Catatan: Anda juga dapat menambahkan dan menghapus kunci asing menggunakan konsol Google Cloud. Pilih edit as text dari antarmuka pembuatan tabel, dan masukkan DDL untuk mengelola kunci asing untuk tabel Anda.

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 penerapan integritas referensi yang ketat.

Jika Anda mengaktifkan pengoptimal kueri untuk menggunakan informasi kunci asing informatif, penting untuk memahami bahwa ketepatan pengoptimalan bergantung pada data yang konsisten dengan model logis yang dijelaskan oleh kunci asing informatif. Jika ada inkonsistensi, hasil kueri mungkin tidak mencerminkan data 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 salah. 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} menggantikan opsi database berdasarkan 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 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 informatif dan yang diterapkan, lihat topik berikut:

• Perubahan skema yang berjalan lama
• Indeks pendukung

Membuat tabel baru dengan kunci asing yang diterapkan

Anda membuat dan menghapus serta menerapkan 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.

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.

Catatan: Anda juga dapat menambahkan dan menghapus kunci asing menggunakan konsol Google Cloud. Pilih edit as text dari antarmuka pembuatan tabel, dan masukkan DDL untuk mengelola kunci asing untuk tabel Anda.

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 dirujuk dihapus atau diperbarui. Spanner mendukung penggunaan tindakan ON DELETE CASCADE. Dengan tindakan ON DELETE CASCADE kunci asing, saat Anda menghapus baris yang berisi kunci asing yang direferensikan, semua baris yang mereferensikan kunci tersebut juga akan dihapus 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 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 atau ON DELETE NO ACTION.

• Anda dapat membuat kueri 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 operasi tulis terlihat oleh operasi berikutnya dalam transaksi.

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

Spanner memvalidasi semua batasan referensi 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 dirujuk sebelum menyisipkan baris perujuknya. Saat menggunakan mutation API, mutasi di-buffer hingga transaksi di-commit. Validasi kunci asing yang diterapkan ditangguhkan hingga transaksi di-commit. Dalam hal ini, Anda dapat 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 perubahan transaksi dan mempertahankan indeks. Penyimpanan tambahan juga diperlukan untuk setiap indeks.

Tindakan penghapusan kaskade yang berjalan lama

Saat Anda menghapus baris dari tabel yang dirujuk, Spanner harus menghapus semua baris dalam tabel perujuk yang mereferensikan baris yang dihapus. Hal ini dapat menyebabkan efek cascading, yaitu satu operasi penghapusan menghasilkan ribuan operasi penghapusan lainnya. Menambahkan batasan kunci asing dengan tindakan penghapusan cascade ke tabel atau membuat tabel dengan batasan kunci asing dengan tindakan penghapusan cascade dapat memperlambat operasi penghapusan.

Batas mutasi terlampaui untuk penghapusan cascade kunci asing

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

Perbandingan kunci asing yang diterapkan dan interleaving tabel

Interleaving tabel Spanner adalah pilihan yang tepat untuk banyak hubungan induk-turunan tempat kunci utama tabel turunan menyertakan kolom kunci utama tabel induk. Lokasi yang sama untuk baris turunan dengan baris induknya dapat meningkatkan performa secara signifikan.

Kunci asing adalah solusi induk-turunan yang lebih umum dan menangani kasus penggunaan tambahan. Tabel 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, hubungan kunci asing tidak menyiratkan lokasi yang sama untuk tabel di lapisan penyimpanan.

Pertimbangkan contoh yang menggunakan tabel Orders yang ditentukan sebagai berikut:

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 kualitas desain dengan memperkenalkan tabel OrderItems yang berisi entri untuk setiap produk yang dipesan pelanggan. Anda dapat memperkenalkan kunci asing lain yang diterapkan untuk mewakili hubungan one-to-many baru antara Orders dan OrderItems. Namun, Anda juga tahu bahwa Anda sering ingin menjalankan kueri di seluruh pesanan dan item pesanannya masing-masing. Karena co-location data ini meningkatkan performa, Anda sebaiknya membuat hubungan parent-child menggunakan kemampuan interleaving tabel Spanner.

Berikut cara menentukan tabel OrderItems, yang diselingi 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 skema database yang diperbarui sebagai hasil dari pengenalan tabel baru ini, OrderItems, yang diselingi dengan Orders. Di sini Anda juga dapat melihat hubungan one-to-many antara kedua tabel tersebut.

Gambar 4. Penambahan tabel OrderItems yang diselingi

Dalam konfigurasi ini, Anda dapat memiliki beberapa entri OrderItems dalam setiap urutan, dan entri OrderItems untuk setiap urutan saling tumpang-tindih, sehingga berada di lokasi yang sama dengan urutan. Dengan cara ini, secara fisik menyisipkan Orders dan OrderItems dapat meningkatkan performa, yang secara efektif melakukan pra-penggabungan tabel dan memungkinkan Anda mengakses baris terkait secara bersamaan sekaligus meminimalkan akses disk. Misalnya, Spanner dapat melakukan join berdasarkan kunci utama secara lokal, sehingga meminimalkan akses disk dan traffic jaringan.

Jika jumlah mutasi dalam transaksi melebihi 80.000, transaksi akan gagal. Penghapusan berantai yang besar tersebut berfungsi dengan baik untuk tabel dengan hubungan "interleaved in parent", tetapi tidak untuk tabel dengan hubungan kunci asing. Jika 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 dirujuk akan memicu penghapusan jutaan baris, Anda harus mendesain skema dengan tindakan penghapusan kaskade dengan "interleaved in parent".

Catatan: Google merekomendasikan agar Anda memilih untuk mewakili hubungan induk-turunan sebagai tabel sisipan atau sebagai kunci asing, tetapi tidak keduanya. Menggunakan keduanya berlebihan dan dapat menghabiskan resource penyimpanan dan komputasi tambahan untuk memverifikasi batasan dan mempertahankan indeks pendukung.

Tabel perbandingan

Tabel berikut merangkum perbandingan antara kunci asing yang diterapkan dan interleaving tabel. Anda dapat menggunakan informasi ini untuk memutuskan hal 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 induk yang didukung	0 .. 1	0 .. N
Menyimpan data induk dan turunan secara bersamaan	Ya	Tidak
Mendukung penghapusan berurutan	Ya	Ya
Mode pencocokan null	Lulus jika semua nilai pereferensian tidak berbeda dengan nilai yang direferensikan. Nilai null tidak berbeda dengan nilai null; nilai null berbeda dengan nilai non-null.	Lulus jika ada nilai pereferensian yang null. Lulus jika semua nilai pereferensian non-null, dan tabel yang dirujuk memiliki baris dengan nilai yang sama dengan nilai pereferensian. Gagal jika tidak ada baris yang cocok yang ditemukan.
Waktu Penegakan	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. Interleaving tabel tidak dapat dihapus setelah dibuat, kecuali jika Anda menghapus seluruh tabel turunan.	Ya

Indeks pendukung

Kunci asing tidak menggunakan indeks yang dibuat pengguna. Sebagai gantinya, mereka membuat indeks pendukungnya sendiri. Kunci asing yang diterapkan dan informatif membuat indeks pendukung dengan cara yang 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 dirujuk.

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

Untuk kunci asing yang diterapkan dan informatif, kunci asing biasanya mereferensikan kunci utama tabel yang dirujuk, sehingga indeks untuk tabel yang dirujuk biasanya tidak diperlukan. Oleh karena itu, kunci asing informatif 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 data yang ada 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 setiap kunci asing. Indeks pendukung dihapus saat kunci asing yang menggunakannya dihapus. Anda tidak dapat mengubah atau menghapus indeks pendukung.

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 pendukung.

Di luar kueri SQL yang langsung memanggil skema informasi, konsol Google 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. Untuk 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 informatif berada dalam tabel baru atau yang sudah ada:

Jenis tabel	Kunci asing yang diterapkan	Kunci asing informasi
Baru	Spanner mengisi ulang indeks yang dirujuk sesuai kebutuhan untuk setiap kunci asing.	Spanner mengisi ulang indeks yang dirujuk sesuai kebutuhan untuk setiap kunci asing.
Pelanggan Lama	Spanner mengisi ulang indeks yang mereferensikan dan direferensikan sesuai kebutuhan. Spanner juga memvalidasi data yang ada di 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.

Berikut ini 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 Anda melakukan hal berikut:

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

Menambahkan batasan baru dan menghapus batasan lama akan mencegah masalah Operasi Perubahan 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 pendukung 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 pengisian ulang indeks, memvalidasi batasan indeks unik, atau memvalidasi batasan referensi kunci asing.

Catatan: Jika Anda tidak menghapus batasan kunci asing yang lebih lama, batasan kunci asing tambahan memerlukan validasi yang menyebabkan overhead yang tidak perlu. Selain itu, jika pelanggaran batasan UNIQUE mencegah pembuatan indeks yang direferensikan, menambahkan batasan baru mungkin akan gagal.

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

Langkah berikutnya

• Pelajari cara Membuat dan mengelola hubungan kunci asing.

• Pelajari skema informasi lebih lanjut.