Kunci asing

Topik ini menjelaskan kunci asing di Spanner dan cara menggunakannya untuk menerapkan integritas referensial dalam solusi database Anda.

Ringkasan

{i>Foreign key<i} memungkinkan Anda menentukan hubungan antar tabel. Spanner memastikan integritas data hubungan ini tetap terjaga.

Bayangkan Anda adalah developer utama untuk bisnis e-commerce. Anda sedang merancang {i>database<i} untuk memproses pesanan pelanggan. {i>Database<i} perlu menyimpan informasi tentang setiap pesanan, pelanggan, dan produk. Gambar 1 menunjukkan struktur database dasar untuk aplikasi.

database<i} pemrosesan pesanan." class="l10n-absolute-url-src" l10n-attrs-original-order="src,alt,class" src="https://cloud.google.com/static/spanner/docs/images/order-processing-tables.svg" />

Gambar 1. Diagram {i>database<i} pemrosesan pesanan

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

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

• Pesanan dilakukan oleh pelanggan

• Pesanan telah dilakukan untuk produk

Anda memutuskan bahwa database harus menerapkan aturan berikut untuk memastikan pesanan dalam sistem kami 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, kami mengatakan bahwa kami mempertahankan integritas referensial data. Jika database mempertahankan integritas referensial, semua upaya untuk menambahkan data tidak valid, yang akan mengakibatkan link atau referensi antar data yang tidak valid, akan gagal. Integritas referensial mencegah error pengguna. Spanner menerapkan integritas referensial melalui kunci asing.

Menerapkan integritas referensial dengan kunci asing

Mari kita lihat lagi contoh pemrosesan pesanan, dengan lebih banyak detail yang ditambahkan ke desain seperti yang ditunjukkan pada Gambar 2.

Gambar 2. Diagram skema database kami dengan kunci asing

Desainnya sekarang menampilkan nama dan jenis kolom di setiap tabel. Tabel Orders juga menentukan dua hubungan kunci asing. FK_CustomerOrder memastikan bahwa semua baris di Orders memiliki CustomerID yang valid. Kunci asing FK_ProductOrder memastikan semua nilai ProductID dalam tabel Orders valid. Tabel berikut memetakan batasan ini kembali ke aturan di dunia nyata yang ingin kita terapkan.

Nama Kunci Asing	Batasan	Deskripsi dunia nyata
FK_CustomerOrder	Memastikan semua baris di Orders memiliki CustomerID yang valid	Pesanan dilakukan oleh pelanggan yang valid
FK_ProductOrder	Memastikan semua baris di Orders memiliki ProductID yang valid	Pesanan telah dilakukan untuk produk yang valid

Spanner akan menggagalkan transaksi apa pun yang mencoba menyisipkan atau memperbarui baris di 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 di tabel Customers dan Products yang akan membuat ID dalam tabel Orders menjadi tidak valid. Untuk detail selengkapnya tentang cara Spanner memvalidasi batasan, lihat Validasi batasan transaksi di bawah.

Menentukan kunci asing

Kunci asing dibuat dan dihapus dari database Spanner Anda menggunakan DDL. Kunci asing ditambahkan ke tabel baru dengan pernyataan CREATE TABLE. Demikian pula, Anda dapat menambahkan kunci asing ke, atau menghapus dari, tabel yang ada dengan pernyataan ALTER TABLE. Berikut ini adalah contoh pembuatan tabel baru dengan {i>foreign key<i}.

GoogleSQL

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)
) PRIMARY KEY (OrderID);

PostgreSQL

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 mengetahui contoh lainnya 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.

Berikut adalah daftar karakteristik {i>foreign key<i} di Spanner.

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

• Kunci asing mereferensikan kolom yang dirujuk dari tabel yang direferensikan.

• Seperti contoh di atas, Anda dapat memberi nama setiap batasan kunci asing. Jika Anda tidak menentukan nama, Spanner akan otomatis membuat nama. Nama yang dibuat dapat dikueri dari INFORMATION_SCHEMA Spanner. Misalnya, nama batasan tercakup dalam skema, beserta nama tabel dan indeks dan harus unik dalam skema.

• Jumlah kolom yang direferensikan dan direferensikan harus sama. Urutan bersifat signifikan. Kolom {i>reference <i}pertama{i> <i}mengacu pada kolom{i> <i}yang{i> <i}pertama{i>, <i}yang kedua sampai{i> <i}yang kedua, dst.

• Kolom rujukan dan pasangan yang direferensikan harus berjenis sama. Kolom juga harus dapat diindeks.

• Kunci asing tidak dapat dibuat pada kolom dengan opsi allow_commit_timestamp=true.

• Kolom array tidak didukung.

• Kolom JSON tidak didukung.

• {i>Foreign key<i} dapat mereferensikan kolom dari tabel yang sama ("{i>self-reference<i} (kunci asing)" (foreign key). Contohnya adalah tabel Karyawan dengan kolom ManagerId yang mereferensikan kolom employeeId tabel.

• {i>Foreign key<i} juga dapat membentuk hubungan melingkar antara tabel tempat dua tabel mereferensikan satu sama lain, baik secara langsung maupun tidak langsung. Tabel yang direferensikan harus ada sebelum membuat kunci asing, sehingga setidaknya salah satu kunci asing harus ditambahkan menggunakan pernyataan ALTER TABLE.

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

• Spanner mungkin juga dapat menggunakan kunci utama dari tabel referensi, meskipun hal ini kurang umum. Jika tidak, Spanner akan membuat NULL_FILTERED INDEX di atas kolom referensi.

• {i>Foreign key <i}tidak menggunakan indeks sekunder yang telah Anda buat; {i>foreign key <i}membuat indeks pendukungnya sendiri. Indeks pendukung ini dapat digunakan dalam evaluasi kueri, termasuk dalam perintah force_index eksplisit. Nama indeks pendukung dapat dikueri dari INFORMATION_SCHEMA Spanner. Untuk mengetahui informasi selengkapnya, lihat Indeks pendukung.

Tindakan kunci asing

Tindakan kunci asing memungkinkan Anda mengontrol apa yang terjadi pada kolom yang dibatasi saat kolom yang direferensikannya dihapus atau diperbarui. Spanner mendukung penggunaan tindakan ON DELETE CASCADE. Dengan tindakan kunci asing ON DELETE CASCADE, 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 {i>foreign key<i} dengan sebuah tindakan saat Anda membuat {i>database<i} 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 ini contoh cara membuat tabel baru dengan tindakan {i>foreign key<i}.

GoogleSQL

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

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 Customers (
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CustomerId)
);

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 dapat berupa ON DELETE CASCADE atau ON DELETE NO ACTION.

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

• Menambahkan tindakan {i>foreign key<i} pada batasan kunci asing yang ada tidak didukung. Anda perlu menambahkan batasan kunci asing baru dengan tindakan.

Perubahan skema yang berjalan lama

Menambahkan kunci asing ke tabel yang ada, atau membuat tabel baru dengan kunci asing, dapat menyebabkan operasi yang berjalan lama. Dalam kasus tabel baru, tabel tidak dapat ditulis sampai operasi yang berjalan lama selesai.

Untuk tabel baru dengan kunci asing, Spanner harus mengisi ulang indeks yang direferensikan sesuai kebutuhan untuk setiap kunci asing.

Untuk tabel yang ada dengan kunci asing, Spanner harus mengisi ulang referensi dan indeks yang direferensikan sesuai kebutuhan. Selain itu, Spanner memvalidasi data yang ada dalam tabel untuk memastikannya mematuhi batasan integritas referensial kunci asing. Perubahan skema akan gagal jika ada data yang tidak valid.

Menambahkan tindakan {i>foreign key<i} pada batasan yang ada tidak didukung. Sebaiknya Anda melakukan tindakan berikut:

1. Tambahkan batasan baru dengan tindakan.
2. Hapus batasan lama tanpa tindakan.

Tindakan ini akan menghindari masalah Long-running Alter Constraint Operation. Setelah Anda membuat kunci asing baru dengan tindakan ON DELETE CASCADE, efek bersih dari kedua batasan tersebut adalah DELETE CASCADE. Melepaskan batasan dapat menyebabkan penurunan indeks pendukung kunci asing jika indeks tidak digunakan dalam batasan kunci asing lainnya. Kemudian, jika pengguna menambahkan batasan kunci asing yang sama dengan tindakan, tindakan tersebut mungkin memerlukan operasi yang berjalan lama, termasuk pengisian ulang indeks, memvalidasi batasan indeks unik, dan memvalidasi batasan referensi kunci asing.

Catatan: Jika Anda tidak menghapus batasan kunci asing yang lama, ada overhead karena memvalidasi batasan kunci asing tambahan.

Perubahan skema di atas dapat gagal jika indeks yang dirujuk tidak dapat dibuat karena pelanggaran batasan UNIQUE.

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

Validasi batasan untuk transaksi

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

Nilai yang dimasukkan ke kolom referensi dicocokkan dengan nilai tabel yang direferensikan dan kolom yang direferensikan. Baris dengan nilai referensi NULL tidak dicentang, sehingga Anda dapat menambahkannya ke tabel referensi.

Spanner memvalidasi semua batasan referensi kunci asing yang berlaku saat mencoba memperbarui data melalui 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 referensinya. Saat menggunakan API mutasi, mutasi akan di-buffer hingga transaksi di-commit. Validasi kunci asing ditunda hingga transaksi di-commit. Dalam hal ini, Anda dapat menyisipkan baris referensi terlebih dahulu.

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

Indeks pendukung

{i>Foreign key<i} tidak menggunakan indeks yang dibuat pengguna. Mereka membuat indeks pendukungnya sendiri.

Spanner dapat membuat hingga dua indeks pendukung sekunder untuk setiap kunci asing, satu untuk kolom referensi, dan satu detik untuk kolom yang direferensikan. Namun, kunci asing biasanya mereferensikan kunci utama dari tabel yang direferensikan sehingga indeks kedua pada tabel yang direferensikan biasanya tidak diperlukan.

Indeks pendukung untuk tabel yang direferensikan adalah indeks UNIQUE NULL_FILTERED; pembuatan kunci asing akan gagal jika ada data yang melanggar batasan keunikan indeks. Indeks pendukung untuk tabel referensi adalah NULL_FILTERED.

Jika dua kunci asing atau lebih memerlukan indeks pendukung yang sama, Spanner akan membuat satu indeks untuk semuanya. Backing index dihapus ketika kunci asing yang menggunakannya dihapus. Pengguna tidak dapat mengubah atau menghapus indeks pendukung.

Spanner menggunakan setiap skema informasi database untuk menyimpan metadata terkait indeks pendukung. Baris dalam INFORMATION_SCHEMA.INDEXES dengan nilai SPANNER_IS_MANAGED true menjelaskan indeks pendukung.

Selain kueri SQL yang langsung memanggil skema informasi, Google Cloud Console tidak menampilkan informasi apa pun tentang indeks pendukung database.

Tindakan bertingkat penghapusan yang berjalan lama

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

Batas mutasi terlampaui untuk jenjang penghapusan kunci asing

Menghapus sejumlah besar kumpulan data menggunakan urutan penghapusan {i>foreign key<i} dapat memengaruhi performa. Hal ini karena setiap data yang dihapus akan memicu penghapusan semua data terkait yang menggunakan kunci asing. Jika jumlah mutasi dalam transaksi melebihi 80.000, transaksi akan gagal.

Jika perlu menghapus sejumlah besar data menggunakan jenjang penghapusan kunci asing, Anda harus menghapus baris secara eksplisit dari tabel turunan sebelum menghapus baris dari tabel induk. Dengan begitu, transaksi tidak akan gagal karena batas mutasi.

Perbandingan kunci asing dan tabel yang bertautan

Penyertaan tabel Spanner adalah pilihan yang tepat untuk banyak hubungan induk-turunan tempat kunci utama tabel turunan menyertakan kolom kunci utama tabel induk. Lokasi 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. Hubungan tersebut tidak terbatas pada kolom kunci utama, dan tabel dapat memiliki beberapa hubungan kunci asing, baik sebagai induk dalam beberapa hubungan maupun turunan dalam hubungan yang lain. Namun, relasi kunci asing tidak berarti co-location tabel di lapisan penyimpanan.

Mari kita lihat contoh berikut menggunakan skema pemrosesan pesanan yang telah kita bahas sebelumnya dalam topik ini. Ingat kembali tabel Orders kita ditentukan sebagai berikut:

Gambar 3. Diagram skema database kami dengan kunci asing

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

Bayangkan pelanggan memberi tahu kita bahwa mereka ingin dapat memesan lebih dari satu produk per pesanan. Kita dapat meningkatkan kualitas desain dengan memperkenalkan tabel OrderItems yang berisi entri untuk setiap produk yang dipesan pelanggan. Kita dapat memperkenalkan kunci asing lain untuk mewakili hubungan one-to-many yang baru antara Orders dan OrderItems. Namun, kita juga tahu bahwa sering kali kita ingin menjalankan kueri di seluruh pesanan dan item urutannya masing-masing. Lokasi bersama data ini akan meningkatkan performa, jadi kita akan membuat hubungan induk-turunan menggunakan kemampuan antarmuka tabel Spanner.

Berikut ini cara kami menentukan tabel OrderItems, yang disisipkan dengan Orders.

GoogleSQL

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 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 memperkenalkan tabel baru ini, OrderItems, yang disisipkan dengan Orders. Di sini Anda juga dapat melihat hubungan one-to-many di antara kedua tabel tersebut.

Gambar 4. Penambahan tabel OrderItems yang disisipkan

Dalam konfigurasi ini, kita dapat memiliki beberapa entri OrderItems dalam setiap pesanan dan entri OrderItems untuk setiap pesanan akan disisipkan, sehingga ditempatkan bersama pesanan tersebut. Menggabungkan Orders dan OrderItems secara fisik dengan cara ini dapat meningkatkan performa, yang secara efektif menggabungkan tabel dan memungkinkan Anda mengakses baris terkait bersama-sama sambil 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 besar tersebut berfungsi baik untuk tabel yang memiliki hubungan "disisipkan dalam induk", tetapi tidak untuk tabel dengan hubungan kunci asing. Jika memiliki hubungan kunci asing dan perlu menghapus sejumlah besar baris, Anda harus menghapus baris secara eksplisit dari tabel turunan terlebih dahulu.

Jika Anda memiliki tabel pengguna dengan hubungan {i>foreign key<i} dengan tabel lain, dan menghapus satu baris dari tabel yang dirujuk akan memicu penghapusan jutaan baris, Anda harus mendesain skema dengan tindakan jenjang penghapusan dengan "disisipkan di induk".

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

Tabel perbandingan

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

Jenis hubungan induk-turunan	Penyambungan Tabel	{i>Foreign Key<i}
Dapat menggunakan kunci utama	Ya	Ya
Dapat menggunakan kolom kunci non-utama	Tidak	Ya
Jumlah orang tua yang didukung	0 .. 1	0 .. U
Menyimpan data induk dan turunan secara bersamaan	Ya	Tidak
Mendukung penghapusan bertingkat	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.	Lulus jika ada nilai referensi null. Lulus jika semua nilai referensi non-null, dan tabel yang dirujuk memiliki baris dengan nilai yang sama dengan nilai referensi. Gagal jika tidak ditemukan baris yang cocok.
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 dengan mudah	Tidak. Tabel yang bertautan tidak dapat dihapus setelah dibuat, kecuali jika Anda menghapus seluruh tabel turunan.	Ya

Langkah selanjutnya

• Pelajari Membuat dan mengelola hubungan kunci asing.

• Pelajari skema informasi lebih lanjut.