Halaman ini diterjemahkan oleh Cloud Translation API.

Membuat dan mengelola hubungan kunci asing

Halaman ini menjelaskan cara mengelola hubungan kunci asing di database Anda.

Kunci asing adalah kolom yang dibagikan di antara tabel untuk membuat link antara data terkait. Saat Anda menggunakan kunci asing, Spanner memastikan hubungan ini dipertahankan.

Diagram berikut menunjukkan skema database sederhana dengan data dalam tabel yang memiliki hubungan dengan data dalam tabel lain.

Contoh skema database yang menunjukkan hubungan kunci asing antar-tabel.

Gambar 1. Diagram skema database pemrosesan pesanan

Ada tiga tabel dalam skema yang ditampilkan pada Gambar 1:

Tabel Customers mencatat nama setiap pelanggan.
Tabel Orders melacak semua pesanan yang dibuat.
Tabel Products menyimpan informasi produk untuk setiap produk.

Ada dua hubungan kunci asing antara tabel ini:

Hubungan kunci asing ditentukan antara tabel Orders dan tabel Customers untuk memastikan bahwa pesanan tidak dapat dibuat kecuali jika ada pelanggan yang sesuai.
Hubungan kunci asing antara tabel Orders dan tabel Products memastikan bahwa pesanan tidak dapat dibuat untuk produk yang tidak ada.

Dengan menggunakan skema sebelumnya sebagai contoh, topik ini membahas pernyataan CONSTRAINT Bahasa Definisi Data (DDL) yang dapat Anda gunakan untuk mengelola hubungan antara tabel dalam database.

Menambahkan kunci asing saat membuat tabel baru

Asumsikan Anda telah membuat tabel Customers dalam database pemesanan produk sederhana. Sekarang Anda memerlukan tabel Orders untuk menyimpan informasi tentang pesanan yang dibuat pelanggan. Untuk memastikan semua urutan valid, Anda tidak ingin membiarkan sistem menyisipkan baris ke dalam tabel Orders kecuali jika ada entri yang cocok di tabel Customers. Oleh karena itu, Anda memerlukan kunci asing untuk membangun hubungan antara kedua tabel tersebut. Salah satu pilihannya adalah menambahkan kolom CustomerID ke tabel baru dan menggunakannya sebagai kunci asing untuk membuat hubungan dengan kolom CustomerID di tabel Customers.

Saat membuat tabel baru dengan kunci asing, Anda menggunakan REFERENCE untuk membuat hubungan ke tabel lain. Tabel yang berisi pernyataan REFERENCE disebut tabel referensi. Tabel yang dinamai dalam pernyataan REFERENCE adalah tabel yang direferensikan. Kolom yang diberi nama dalam pernyataan REFERENCE disebut kolom referensi.

Contoh berikut menunjukkan cara menggunakan pernyataan DDL CREATE TABLE untuk membuat tabel Orders dengan batasan kunci asing yang mereferensikan CustomerID dalam tabel Customers.

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)
);

Pernyataan sebelumnya berisi klausa CONSTRAINT yang memiliki karakteristik berikut:

Penggunaan sintaksis CONSTRAINT untuk memberi nama batasan, sehingga memudahkan untuk menghapus tabel menggunakan nama yang telah Anda pilih.
Batasan ini memiliki nama FK_CustomerOrder. Nama batasan dicakup dalam skema dan harus unik dalam skema.
Tabel Orders, tempat Anda menentukan batasan, adalah tabel referensi. Tabel Customers adalah tabel yang dirujuk.
Kolom referensi dalam tabel referensi adalah CustomerID. Kolom ini mereferensikan kolom CustomerID dalam tabel Customers. Jika seseorang mencoba menyisipkan baris ke Orders dengan CustomerID yang tidak ada di Customers, penyisipan akan gagal.

Contoh berikut menunjukkan pernyataan pembuatan tabel alternatif. Di sini, batasan kunci asing ditentukan tanpa nama. Saat Anda menggunakan sintaksis ini, Spanner akan membuat nama untuk Anda. Untuk menemukan nama semua kunci asing, lihat Melihat properti hubungan kunci asing.

GoogleSQL

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  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,
  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
  PRIMARY KEY (OrderID)
);

Menambahkan kunci asing ke tabel yang ada

Anda juga ingin memastikan bahwa pelanggan hanya dapat memesan produk yang ada. Pertama, Anda harus menghapus batasan yang ada. Kemudian, Anda dapat menggunakan ALTER TABLE untuk menambahkan batasan kunci asing lain ke tabel Orders, seperti yang ditunjukkan pada contoh berikut:

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Kolom referensi di Orders adalah ProductID, dan kolom ini mereferensikan kolom ProductID di Produk. Sekali lagi, jika Anda tidak keberatan jika Spanner memberi nama batasan ini untuk Anda, gunakan sintaksis berikut:

ALTER TABLE Orders
  ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Menambahkan kunci asing dengan tindakan hapus saat membuat tabel baru

Ingat kembali contoh sebelumnya saat Anda memiliki tabel Customers dalam database pesanan produk yang memerlukan tabel Orders. Anda ingin menambahkan batasan kunci asing yang mereferensikan tabel Customers. Namun, Anda ingin memastikan bahwa saat Anda menghapus data pelanggan pada masa mendatang, Spanner juga menghapus semua pesanan untuk pelanggan tersebut. Dalam hal ini, Anda ingin menggunakan tindakan ON DELETE CASCADE dengan batasan kunci asing.

Pernyataan DDL CREATE TABLE berikut untuk tabel Orders menyertakan batasan kunci asing yang mereferensikan tabel Customers dengan tindakan ON DELETE CASCADE.

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

PostgreSQL

CREATE TABLE Orders (
  OrderID BIGINT NOT NULL,
  CustomerID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  FOREIGN KEY (CustomerID)
    REFERENCES Customers (CustomerID) ON DELETE CASCADE,
  PRIMARY KEY (OrderID)
);

Pernyataan sebelumnya berisi batasan kunci asing dengan klausa ON DELETE CASCADE. Kolom CustomerID adalah kunci asing yang mereferensikan kolom CustomerID di tabel Customers. Artinya, setiap nilai CustomerID dalam tabel Orders juga harus ada dalam tabel Customers. Jika seseorang mencoba menghapus baris dari tabel Customers, semua baris dalam tabel Orders yang mereferensikan nilai CustomerID yang dihapus juga akan dihapus dalam transaksi yang sama.

Menambahkan kunci asing dengan tindakan hapus ke tabel yang ada

Anda juga ingin memastikan bahwa pesanan hanya dibuat untuk produk yang ada. Anda dapat menggunakan ALTER TABLE untuk menambahkan batasan kunci asing lain dengan tindakan ON DELETE CASCADE ke tabel pesanan sebagai berikut:

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
    REFERENCES Products (ProductID) ON DELETE CASCADE;

Menghapus baris dari tabel Products akan menghapus semua baris dalam tabel Orders yang mereferensikan nilai ProductID yang dihapus.

Membuat kueri data di seluruh hubungan kunci asing

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Products ON Orders.ProductsID = Products.ProductID;

Contoh cara Spanner mempertahankan integritas referensial

Alasan utama menambahkan hubungan kunci asing adalah agar Spanner dapat mempertahankan integritas referensi data Anda. Jika Anda mengubah data dengan cara yang melanggar batasan kunci asing, update akan gagal dengan error.

Pertimbangkan data dalam Gambar 2. Beberapa pelanggan telah memesan produk, seperti yang ditampilkan dalam tabel pesanan. Karena kunci asing yang ada, Anda dapat menjamin bahwa data yang disisipkan ke dalam tabel Orders memiliki integritas referensi.

Contoh data untuk tabel Pelanggan, Produk, dan Pesanan.

Gambar 2. Contoh data untuk database pengurutan kami.

Contoh berikut menunjukkan hal yang terjadi saat Anda mencoba mengubah data dengan cara yang akan merusak integritas referensial.

Menambahkan baris ke tabel Orders dengan nilai CustomerID yang tidak ada di Customers

Apa yang terjadi jika Anda mencoba modifikasi berikut, dengan mempertimbangkan data sampel dari diagram sebelumnya?
```
INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);
```
Dalam hal ini, sistem akan mencoba menyisipkan baris ke Orders dengan CustomerID (447) yang tidak ada dalam tabel Customers. Jika sistem melakukan hal ini, Anda akan memiliki urutan yang tidak valid di sistem. Namun, dengan batasan kunci asing yang Anda tambahkan ke tabel Orders, tabel Anda akan dilindungi. INSERT gagal dengan pesan berikut, dengan asumsi batasan disebut FK_CustomerOrder.
```
Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`.
Cannot find referenced values in Customers(CustomerID).
```
Mencoba menghapus baris dari tabel Customers saat pelanggan direferensikan dalam batasan kunci asing.

Bayangkan situasi saat pelanggan berhenti berlangganan dari toko online Anda. Anda ingin menghapus pelanggan dari backend, jadi Anda mencoba operasi berikut.
```
DELETE FROM Customers WHERE CustomerID = 721;
```
Dalam contoh ini, Spanner mendeteksi melalui batasan kunci asing bahwa masih ada data dalam tabel Orders yang mereferensikan baris pelanggan yang Anda coba hapus. Dalam kasus ini, error berikut akan ditampilkan.

Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.

Untuk memperbaiki masalah ini, hapus semua entri referensi di Orders terlebih dahulu. Anda juga dapat menentukan kunci asing dengan tindakan ON DELETE CASCADE agar Spanner dapat menangani penghapusan entri referensi.

Melihat properti hubungan kunci asing

INFORMATION_SCHEMA Spanner berisi informasi tentang kunci asing dan indeks pendukungnya. Berikut adalah beberapa contoh pertanyaan yang dapat Anda jawab dengan membuat kueri INFORMATION SCHEMA.

Untuk informasi selengkapnya tentang indeks pendukung, lihat Indeks pendukung kunci asing.

Batasan apa yang ditentukan dalam database saya?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

Kunci asing apa yang ditentukan dalam database saya?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Indeks mana yang merupakan indeks sekunder untuk kunci asing, yang juga dikenal sebagai indeks pendukung?

Indeks pendukung kunci asing dikelola oleh Spanner , sehingga membuat kueri untuk SPANNER_IS_MANAGED pada tampilan INDEXES akan menampilkan semua indeks pendukung.

SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
  i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';

Apa tindakan referensi yang ditentukan dengan batasan kunci asing?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
  rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Untuk mengetahui informasi selengkapnya, lihat Skema Informasi.

Menghapus hubungan kunci asing

DDL berikut menghapus batasan kunci asing dari tabel Orders.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

Indeks pendukung kunci asing dihapus secara otomatis saat batasan itu dihapus.

Dukungan untuk hubungan kunci asing yang lebih kompleks

Beberapa kolom

Kunci asing dapat mereferensikan beberapa kolom. Daftar kolom membentuk kunci yang sesuai dengan kunci utama tabel atau indeks pendukung. Tabel yang mereferensikan berisi kunci asing dari kunci tabel yang dirujuk.

Dalam contoh berikut, definisi kunci asing memberi tahu Spanner bahwa setiap nilai SongName dalam tabel TopHits harus memiliki nilai yang cocok dalam tabel Songs; dan setiap pasangan nilai SingerFirstName dan SingerLastName harus memiliki pasangan nilai FirstName dan LastName yang cocok dalam tabel Singers.

GoogleSQL

CREATE TABLE TopHits (
  Rank INT64 NOT NULL,
  SongName STRING(MAX),
  SingerFirstName STRING(MAX),
  SingerLastName STRING(MAX),

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName)

) PRIMARY KEY (Rank);

PostgreSQL

CREATE TABLE TopHits (
  Rank BIGINT NOT NULL,
  SongName VARCHAR,
  SingerFirstName VARCHAR,
  SingerLastName VARCHAR,

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName),

  PRIMARY KEY (Rank)
);

Referensi melingkar

Terkadang tabel memiliki dependensi melingkar, mungkin karena alasan lama atau karena denormalisasi. Kunci asing Spanner mengizinkan referensi melingkar. Karena tabel yang dirujuk harus ada sebelum kunci asing dapat mereferensikannya, salah satu kunci asing harus ditambahkan dengan pernyataan ALTER TABLE. Berikut adalah contohnya

Membuat TableA, tanpa kunci asing
Buat TableB dengan batasan kunci asing di TableA.
Gunakan ALTER TABLE di TableA untuk membuat referensi kunci asing ke TableB.

Tabel yang merujuk ke dirinya sendiri

Salah satu jenis referensi melingkar khusus adalah tabel yang menentukan kunci asing yang mereferensikan tabel yang sama. Misalnya, cuplikan berikut menunjukkan kunci asing untuk menerapkan bahwa ManagerId karyawan juga merupakan karyawan.

GoogleSQL

CREATE TABLE Employees (
  EmployeeId INT64 NOT NULL,
  EmployeeName STRING(MAX) NOT NULL,
  ManagerId INT64,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);

PostgreSQL

CREATE TABLE Employees (
  EmployeeId BIGINT NOT NULL,
  EmployeeName VARCHAR NOT NULL,
  ManagerId BIGINT,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
  PRIMARY KEY (EmployeeId)
);

Langkah selanjutnya

Pelajari lebih lanjut dukungan kunci asing di Spanner.
Pelajari INFORMATION SCHEMA Spanner lebih lanjut.