Buat dan kelola hubungan kunci asing

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

{i>Foreign key<i} adalah kolom yang digunakan di antara tabel-tabel untuk membuat tautan di antara data yang terkait. Saat Anda menggunakan kunci asing, Spanner akan memastikan bahwa hubungan ini akan dipertahankan.

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

Gambar 1. Diagram skema database pemrosesan pesanan

Ada tiga tabel dalam skema yang ditunjukkan 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 di antara tabel-tabel ini:

• Hubungan kunci asing ditentukan antara tabel Orders dan tabel Customers untuk memastikan bahwa pesanan tidak dapat dibuat kecuali 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 antar-tabel dalam database.

Menambahkan {i>foreign key<i} saat membuat tabel baru

Asumsikan bahwa Anda telah membuat tabel Customers dalam database pengurutan produk sederhana. Anda sekarang memerlukan tabel Orders untuk menyimpan informasi tentang pesanan yang dibuat pelanggan. Untuk memastikan semua pesanan valid, Anda tidak ingin sistem menyisipkan baris ke dalam tabel Orders kecuali ada juga entri yang cocok dalam tabel Customers. Oleh karena itu, Anda memerlukan {i>foreign key<i} untuk membuat hubungan antara kedua tabel. Salah satu pilihannya adalah menambahkan kolom CustomerID ke tabel baru dan menggunakannya sebagai kunci asing untuk membuat hubungan dengan kolom CustomerID dalam tabel Customers.

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

Contoh berikut menunjukkan cara menggunakan pernyataan DDL CREATE TABLE untuk membuat tabel Orders dengan batasan kunci asing yang merujuk 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 mempermudah pelepasan tabel menggunakan nama yang Anda pilih.

• Batasan tersebut memiliki nama FK_CustomerOrder. Nama batasan tercakup dalam skema dan harus unik di dalam skema.

• Tabel Orders, yang Anda gunakan untuk menentukan batasan, adalah tabel referensi. Tabel Customers adalah tabel yang direferensikan.

• Kolom referensi dalam tabel referensi adalah CustomerID. Fungsi ini mereferensikan kolom CustomerID dalam tabel Customers. Jika seseorang mencoba menyisipkan baris ke Orders dengan CustomerID yang tidak ada di Customers, penyisipan akan gagal.

Catatan: Kolom yang direferensikan pada tabel yang direferensikan sering kali sesuai dengan kolom kunci utama, tetapi hal ini tidak wajib. Tabel referensi juga dapat mereferensikan kolom non-primer dari tabel yang dirujuk.

Contoh berikut menunjukkan pernyataan pembuatan tabel alternatif. Di sini, batasan kunci asing ditentukan tanpa nama. Saat 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 {i>foreign key<i} 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 lainnya 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 dalam Products. Sekali lagi, jika Anda tidak keberatan jika Spanner memberi nama batasan ini, 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 jika Anda memiliki tabel Customers dalam database pengurutan 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 di masa mendatang, Spanner juga akan 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 dalam tabel Customers. Ini berarti setiap nilai CustomerID dalam tabel Orders juga harus ada di tabel Customers. Jika seseorang mencoba menghapus baris dari tabel Customers, semua baris dalam tabel Orders yang merujuk ke nilai CustomerID yang dihapus juga akan dihapus dalam transaksi yang sama.

Catatan: Jika Anda tidak menentukan tindakan tombol asing, tindakan defaultnya adalah NO ACTION. Artinya, jika seseorang mencoba menghapus baris dari tabel Customers, dan ada baris dalam tabel Orders yang merujuk ke nilai CustomerID yang dihapus, operasi penghapusan akan gagal dengan error pelanggaran batasan kunci asing.

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 lainnya 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 merujuk ke nilai ProductID yang dihapus.

Catatan: Menambahkan atau melepas tindakan kunci asing pada batasan kunci asing yang ada tidak didukung. Anda perlu menambahkan batasan kunci asing baru dengan tindakan. Untuk informasi selengkapnya, lihat Perubahan skema yang berjalan lama.

Mengkueri 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 untuk menambahkan hubungan kunci asing adalah agar Spanner dapat mempertahankan integritas referensial data Anda. Jika Anda memodifikasi data dengan cara yang merusak batasan kunci asing, update akan gagal dan menampilkan error.

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

Gambar 2. Contoh data untuk di {i>database<i} pemesanan.

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

• Tambahkan 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 memasukkan baris ke Orders dengan CustomerID (447) yang tidak ada dalam tabel Customers. Jika sistem melakukannya, Anda akan memiliki pesanan yang tidak valid di sistem Anda. Namun, dengan batasan kunci asing yang Anda tambahkan ke tabel Orders, tabel Anda akan dilindungi. INSERT gagal dengan pesan berikut, dengan asumsi batasan dipanggil 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 ketika pelanggan berhenti berlangganan dari toko online Anda. Anda ingin menghapus pelanggan dari backend, jadi coba lakukan 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 merujuk ke baris pelanggan yang Anda coba hapus. Error berikut ditampilkan dalam kasus ini.

  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 untuk memungkinkan Spanner 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 SCHEMA INFORMASI.

Untuk mengetahui informasi lebih lanjut 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';

Apa kunci asing yang didefinisikan dalam database saya?

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

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

Indeks pendukung kunci asing dikelola oleh Spanner , sehingga 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 referensial 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 sendiri dihapus.

Dukungan untuk hubungan kunci asing yang lebih kompleks

Beberapa kolom

{i>Foreign key <i}dapat merujuk pada beberapa kolom. Daftar kolom membentuk kunci yang sesuai dengan kunci utama tabel atau indeks pendukung. Tabel referensi berisi kunci asing dari kunci tabel yang direferensikan.

Pada contoh berikut, definisi kunci asing memberi tahu Spanner bahwa setiap nilai SongName dalam tabel TopHits harus memiliki nilai yang cocok di tabel Lagu; 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 direferensikan harus ada sebelum kunci asing dapat mereferensikannya, salah satu kunci asing harus ditambahkan dengan pernyataan ALTER TABLE. Berikut adalah contohnya

1. Membuat TableA, tanpa {i>foreign key<i}
2. Membuat TableB dengan batasan {i>foreign key<i} di TableA.
3. Gunakan ALTER TABLE pada TableA untuk membuat referensi kunci asing ke TableB.

Tabel yang merujuk ke dirinya sendiri

Salah satu jenis referensi melingkar khusus adalah tabel yang menentukan {i>foreign key<i} yang mereferensikan tabel yang sama. Misalnya, cuplikan berikut menampilkan kunci asing untuk menyatakan 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 dukungan kunci asing di Spanner lebih lanjut.

• Pelajari SCHEMA INFORMASI Spanner lebih lanjut.