Kunci asing

Halaman ini menjelaskan kunci asing di Spanner, dan cara menggunakannya untuk menerapkan integritas referensi di database Anda.

Kunci asing menentukan hubungan antartabel. Spanner memastikan bahwa integritas data hubungan ini 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 antara tabel 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. Spanner menerapkan integritas referensi melalui kunci asing.

Menerapkan 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 4.

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 memastikan bahwa semua baris di Orders memiliki CustomerID yang valid. Kunci asing FK_ProductOrder memastikan bahwa 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	Memastikan bahwa semua baris di Orders memiliki CustomerID yang valid	Pelanggan yang valid melakukan pemesanan
FK_ProductOrder	Memastikan bahwa semua baris di Orders memiliki ProductID yang valid	Pesanan dibuat untuk produk yang valid

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. Hal ini juga akan membuat transaksi yang mencoba memperbarui atau menghapus baris di tabel Customers dan Products gagal, sehingga ID di tabel Orders akan menjadi tidak valid. Untuk mengetahui detail selengkapnya tentang cara Spanner memvalidasi batasan, lihat bagian Validasi batasan transaksi.

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. Kolom referensi pertama mengacu pada kolom referensi pertama, kolom kedua ke kolom kedua, dll.

• 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 salah 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 kunci asing cocok dengan kolom kunci utama tabel yang dirujuk. Jika Spanner tidak dapat menggunakan kunci utama tabel yang direferensikan, Spanner akan membuat UNIQUE NULL_FILTERED INDEX di atas kolom yang direferensikan.

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

• 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 untuk nama indeks pendukung dari INFORMATION_SCHEMA Spanner. Untuk informasi selengkapnya, lihat Mendukung indeks.

Menentukan kunci asing

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.

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

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. Untuk tabel baru, tabel tidak dapat ditulis hingga operasi yang berjalan lama selesai.

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

Untuk tabel yang ada dengan kunci asing, Spanner perlu mengisi ulang indeks yang merujuk dan dirujuk sesuai kebutuhan. Selain itu, Spanner 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.

Menambahkan tindakan kunci asing pada batasan yang ada tidak didukung. Anda harus:

1. Tambahkan batasan baru dengan tindakan yang diperlukan.
2. Hapus batasan lama yang tidak memiliki 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. Menghapus batasan dapat menyebabkan penghapusan 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, seperti mengisi ulang indeks, memvalidasi batasan indeks unik, dan memvalidasi batasan referensi kunci asing.

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

Salah satu perubahan skema sebelumnya dapat gagal jika indeks yang direferensikan 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

Spanner memvalidasi batasan kunci asing 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 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 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 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. 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.

Indeks pendukung

Kunci asing tidak menggunakan indeks yang dibuat pengguna. Mereka membuat indeks pendukung mereka sendiri.

Spanner dapat membuat hingga dua indeks pendukung sekunder untuk setiap kunci asing, satu untuk kolom referensi, dan satu lagi untuk kolom yang dirujuk. Namun, kunci asing biasanya mereferensikan kunci utama 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 data yang ada melanggar batasan keunikan indeks. 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 semuanya. Indeks pendukung dihapus saat kunci asing yang menggunakannya dihapus. Pengguna 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 langsung memanggil skema informasi, konsol Google Cloud tidak menampilkan informasi apa pun tentang indeks pendukung database.

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 cascade penghapusan ke tabel atau membuat tabel dengan batasan kunci asing dengan tindakan cascade penghapusan 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 perlu menghapus sejumlah besar data menggunakan cascade penghapusan kunci asing, Anda harus menghapus baris dari tabel turunan secara eksplisit sebelum menghapus baris dari tabel induk. Hal ini mencegah transaksi gagal karena batas mutasi.

Perbandingan kunci asing dan interleaving 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 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

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 untuk mewakili 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 pesanannya masing-masing. Karena co-location data ini meningkatkan performa, Anda sebaiknya membuat hubungan induk-turunan menggunakan kemampuan interleaving tabel Spanner.

Berikut cara menentukan tabel OrderItems, yang diselingi 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 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. Secara fisik, menyisipkan Orders dan OrderItems dengan cara ini dapat meningkatkan performa, yang secara efektif menggabungkan 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 direferensikan 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 komputasi dan penyimpanan tambahan untuk memverifikasi batasan dan mempertahankan indeks pendukung.

Tabel perbandingan

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

Jenis hubungan induk-turunan	Interleaving Tabel	Kunci Asing
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

Langkah selanjutnya

• Pelajari cara Membuat dan mengelola hubungan kunci asing.

• Pelajari skema informasi lebih lanjut.