Kunci asing

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

• Ringkasan kunci asing di Spanner
• Jenis kunci asing
• Perbandingan jenis kunci asing
• Pilih jenis kunci asing yang akan digunakan
• Menggunakan kunci asing yang diterapkan
• Menggunakan kunci asing informasional
• Mengindeks cadangan
• Perubahan skema yang berjalan lama

Ringkasan kunci asing di Spanner

Kunci asing menentukan hubungan antar tabel. 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.

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

• Pelanggan melakukan pemesanan.

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

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 antar-data, akan gagal. Integritas referensial mencegah error pengguna. Secara default, Spanner menggunakan kunci asing untuk menerapkan integritas referensial.

Menentukan integritas referensial dengan kunci asing

Bagian berikut memeriksa kembali contoh pemrosesan pesanan, dengan lebih banyak detail yang 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 dilakukan untuk produk yang valid

Spanner menerapkan batasan yang ditentukan menggunakan kunci asing yang diterapkan. Artinya, Spanner akan membatalkan 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. Transaksi yang mencoba memperbarui atau menghapus baris di tabel Customers dan Products yang akan membatalkan ID di tabel Orders juga akan gagal. 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 informasional. Artinya, jika Anda menggunakan kunci asing informasi dalam skenario ini, maka 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. Tidak seperti kunci asing yang diterapkan, kunci asing informasional hanya didukung oleh GoogleSQL, dan 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 referenced dari tabel referenced.

• Seperti dalam contoh, Anda dapat memberi nama setiap batasan kunci asing. Jika Anda tidak menentukan nama, Spanner akan membuat nama untuk Anda. Anda dapat mengirim 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 dirujuk dan merujuk harus sama. Urutan sangat penting. Misalnya, kolom referensi pertama merujuk pada kolom yang dirujuk pertama dan kolom referensi kedua merujuk pada kolom yang dirujuk kedua.

• Kolom referensi dan kolom yang direferensikannya 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 mereferensikan diri sendiri). Contohnya adalah tabel Employee dengan kolom ManagerId yang mereferensikan kolom EmployeeId tabel.

• Kunci asing juga dapat membentuk hubungan melingkar antar tabel di mana dua tabel 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 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 pada 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 mengetahui informasi selengkapnya, lihat Mendukung indeks.

Jenis kunci asing

Ada dua jenis kunci asing, diterapkan dan informatif. 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 informatif serta tabel perbandingan jenis kunci asing berikut.

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, upaya untuk melakukan hal berikut akan gagal:

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

• Menghapus baris dari tabel yang dirujuk yang dirujuk oleh baris dalam tabel perujuk 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 informasional

Kunci asing Informasional digunakan untuk mendeklarasikan model data logis yang dimaksudkan untuk pengoptimalan kueri. Meskipun kunci tabel yang dirujuk harus unik untuk kunci asing informasi, integritas referensial tidak diterapkan. Jika Anda ingin memvalidasi integritas referensial secara selektif saat menggunakan kunci asing informasional, 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 informatif. PostgreSQL tidak mendukung kunci asing informasional.

Perbandingan jenis kunci asing

Diterapkan dan informatif sama-sama memiliki manfaat. Bagian berikut membandingkan dua jenis kunci asing dan mencakup beberapa praktik terbaik.

Perbedaan kunci asing tingkat tinggi

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

• 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 dapat 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 informasional, hasil kueri mungkin tidak mencerminkan data sebenarnya jika data tidak cocok dengan hubungan kunci asing informasional (misalnya, jika beberapa kunci yang dibatasi tidak memiliki kunci yang dirujuk yang cocok dalam tabel yang dirujuk).

Tabel perbedaan kunci asing

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

	Kunci asing yang diterapkan	Kunci asing informasional
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 penulisan, tetapi dapat memengaruhi hasil kueri saat kunci asing informasional 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 konsistensi data dan model logis setiap saat. Kunci asing yang diterapkan adalah opsi yang direkomendasikan kecuali jika tidak berfungsi untuk kasus penggunaan Anda.

Sebaiknya pertimbangkan kunci asing informasional 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 informasional:

  • Sumber data upstream Anda mengikuti model konsistensi akhir. Dalam kasus ini, pembaruan yang dilakukan di sistem sumber mungkin tidak langsung ditampilkan di Spanner. Karena update mungkin tidak segera, inkonsistensi singkat dalam hubungan kunci asing mungkin terjadi.

  • Data Anda berisi baris yang dirujuk yang memiliki banyak hubungan perujukan. 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 dapat memengaruhi performa Spanner dan memperlambat transaksi serentak.

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

Menggunakan kunci asing informasional

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

• Perubahan skema yang berjalan lama
• Mengindeks cadangan

Membuat tabel baru dengan kunci asing informasional

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.

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

Menggunakan kunci asing informasional untuk pengoptimalan kueri

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

Jika Anda mengaktifkan pengoptimal kueri untuk memanfaatkan informasi kunci asing informasional, penting untuk memahami bahwa kebenaran pengoptimalan bergantung pada data yang konsisten dengan model logis yang dijelaskan oleh kunci asing informasional. 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 dirujuk.

Secara default, pengoptimal kueri menggunakan kunci asing NOT ENFORCED. Untuk mengubahnya, setel opsi database use_unenforced_foreign_key_for_query_optimization ke false. Berikut adalah contoh GoogleSQL yang menunjukkan hal ini (kunci asing informasional 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 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 topik berikut:

• Perubahan skema yang berjalan lama
• Mengindeks cadangan

Membuat tabel baru dengan kunci asing yang diterapkan

Anda membuat, menghapus, dan 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 sebagai teks dari antarmuka pembuatan tabel, lalu 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 direferensikannya dihapus atau diupdate. Spanner mendukung penggunaan tindakan ON DELETE CASCADE. Dengan tindakan kunci asing ON DELETE CASCADE, saat Anda menghapus baris yang berisi kunci asing yang dirujuk, semua baris yang merujuk 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 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 atau ON DELETE NO ACTION.

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

• Tindakan menambahkan 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 dilakukan, atau saat efek penulisan dibuat terlihat oleh operasi berikutnya dalam transaksi.

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

Spanner memvalidasi semua batasan referensial kunci asing yang diterapkan yang berlaku saat mencoba memperbarui data menggunakan pernyataan DML atau API. Semua perubahan 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 yang merujuknya. Saat menggunakan mutation API, mutasi di-buffer hingga transaksi di-commit. Validasi kunci asing yang diterapkan ditunda hingga transaksi di-commit. Dalam hal ini, Anda dapat menyisipkan baris yang dirujuk terlebih dahulu.

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

Tindakan penghapusan bertingkat yang berjalan lama

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

Batas mutasi terlampaui untuk penghapusan bertingkat kunci asing

Menghapus sejumlah besar data 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 sejumlah besar data menggunakan penghapusan bertingkat kunci asing, hapus baris secara eksplisit dari tabel turunan sebelum menghapus baris dari tabel induk. Hal ini mencegah kegagalan transaksi karena batas mutasi.

Perbandingan kunci asing yang diterapkan dan penyisipan tabel

Penyisipan 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 sebagai turunan dalam hubungan lainnya. Namun, relasi kunci asing tidak mengimplikasikan 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 yang diterapkan lainnya untuk merepresentasikan hubungan one-to-many baru ini antara Orders dan OrderItems. Namun, Anda juga tahu bahwa Anda sering kali ingin menjalankan kueri di seluruh pesanan dan item pesanannya masing-masing. Karena kolokasi data ini meningkatkan performa, Anda sebaiknya 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 diselingi dengan Orders. Di sini, Anda juga dapat melihat hubungan one-to-many antara kedua tabel tersebut.

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 diselingi, sehingga berada di lokasi yang sama dengan pesanan. Menyisipkan Orders dan OrderItems secara fisik dengan cara ini dapat meningkatkan performa, secara efektif menggabungkan tabel sebelumnya dan memungkinkan Anda mengakses baris terkait secara bersamaan sambil meminimalkan akses disk. Misalnya, Spanner dapat melakukan penggabungan menurut 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 baik untuk tabel dengan hubungan "disisipkan di induk", tetapi tidak untuk tabel dengan hubungan kunci asing. Jika memiliki hubungan kunci asing dan Anda perlu menghapus sejumlah besar baris, Anda harus menghapus baris secara eksplisit dari tabel turunan terlebih dahulu.

Jika Anda memiliki tabel pengguna dengan hubungan kunci asing ke tabel lain, dan menghapus baris dari tabel yang dirujuk memicu penghapusan jutaan baris, Anda harus mendesain skema dengan tindakan penghapusan bertingkat dengan "interleaved in parent".

Catatan: Google merekomendasikan agar Anda memilih untuk merepresentasikan hubungan induk-turunan baik sebagai tabel sisipan maupun sebagai kunci asing, tetapi tidak keduanya. Menggunakan keduanya bersifat redundan dan dapat menggunakan penyimpanan dan resource komputasi tambahan untuk memverifikasi batasan dan mempertahankan indeks pendukung.

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	Penyisipan 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 secara bersamaan	Ya	Tidak
Mendukung penghapusan bertingkat	Ya	Ya
Mode pencocokan null	Lulus jika semua nilai yang dirujuk tidak berbeda dari nilai yang dirujuk. Nilai null tidak berbeda dari nilai null; nilai null berbeda dari nilai non-null.	Lulus jika ada nilai referensi yang null. Lulus jika semua nilai referensi tidak null, dan tabel yang dirujuk memiliki baris dengan nilai yang sama dengan nilai referensi. Gagal jika tidak ada baris yang cocok ditemukan.
Waktu Penegakan	Per operasi saat menggunakan mutation API. Per pernyataan saat menggunakan DML.	Per transaksi saat menggunakan Mutation API. Per pernyataan saat menggunakan DML.
Dapat dihapus	Tidak. Penyelarasan tabel tidak dapat dihapus setelah dibuat, kecuali jika Anda menghapus seluruh tabel turunan.	Ya

Indeks cadangan

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

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

Untuk kunci asing yang diterapkan dan informasional, kunci asing biasanya mereferensikan kunci utama tabel yang direferensikan, sehingga indeks untuk tabel yang direferensikan biasanya tidak diperlukan. Oleh karena itu, kunci asing informasi biasanya memiliki nol indeks pendukung. Jika diperlukan, indeks pendukung yang dibuat untuk tabel yang dirujuk 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 yang mereferensikan. Untuk kunci asing yang diterapkan, indeks pendukung untuk tabel referensi adalah NULL_FILTERED.

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

Spanner menggunakan skema informasi 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 secara 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 berjalan lama. Dalam kasus 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 kunci asing informasional 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.
Yang sudah ada	Spanner mengisi ulang indeks yang merujuk dan dirujuk 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 dirujuk sesuai kebutuhan dan tidak memvalidasi data yang ada dalam tabel.

Berikut ini tidak didukung:

• Menambahkan tindakan kunci asing ke batasan kunci asing yang diterapkan.
• 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 mencegah masalah Operasi Ubah 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 kedua batasan 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 indeks, memvalidasi batasan indeks unik, atau memvalidasi batasan referensial kunci asing.

Catatan: Jika Anda tidak menghapus batasan kunci asing yang lebih lama, batasan kunci asing tambahan perlu divalidasi yang menyebabkan overhead yang tidak perlu. Selain itu, jika pelanggaran batasan UNIQUE mencegah pembuatan indeks yang dirujuk, penambahan batasan baru dapat 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 lebih lanjut skema informasi.