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 bahwa hubungan ini dipertahankan.

Diagram berikut menunjukkan skema database dasar tempat data dalam tabel memiliki hubungan dengan data dalam tabel lain.

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 antartabel dalam database.

Secara default, semua kunci asing di Spanner adalah kunci asing yang diterapkan, yang menerapkan integritas referensi. Di Spanner, Anda juga dapat memilih untuk menggunakan kunci asing informatif, yang tidak memvalidasi atau menerapkan integritas referensi. Untuk informasi selengkapnya, lihat Perbandingan kunci asing dan Memilih jenis kunci asing yang akan digunakan. Jika tidak ditentukan, kunci asing dalam contoh di halaman ini adalah kunci asing yang diterapkan.

Menambahkan kunci asing ke tabel baru

Asumsikan Anda telah membuat tabel Customers di database pemesanan produk dasar. Sekarang Anda memerlukan tabel Orders untuk menyimpan informasi tentang pesanan yang dibuat pelanggan. Untuk memastikan semua urutan valid, Anda tidak ingin sistem menyisipkan baris ke dalam tabel Orders kecuali jika ada juga entri yang cocok di tabel Customers. Oleh karena itu, Anda memerlukan kunci asing yang diterapkan 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.

Catatan: Sering kali kolom yang direferensikan dari tabel yang direferensikan sesuai dengan kolom kunci utama, tetapi hal ini tidak diperlukan. Tabel referensi juga dapat mereferensikan kolom non-utama dari tabel yang dirujuk.

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. Jika tabel Anda sudah memiliki batasan, Anda harus menghapus semua batasan. Di Spanner, semua batasan yang diterapkan dalam tabel harus diterapkan secara bersamaan dalam satu pernyataan DDL batch.

Jika tabel Anda tidak memiliki batasan yang ada, Anda dapat menggunakan pernyataan DDL ALTER TABLE untuk menambahkan batasan kunci asing yang diterapkan ke tabel Orders yang ada seperti yang ditunjukkan dalam contoh berikut:

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

Kolom referensi di Orders adalah ProductID, dan mereferensikan kolom ProductID di Products. 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 ke 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.

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

Menambahkan kunci asing dengan tindakan hapus ke tabel

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.

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

Menggunakan kunci asing informasi (khusus GoogleSQL)

Kunci asing informasi memungkinkan pengoptimal kueri menggunakan hubungan kunci asing tanpa overhead yang timbul dari pemeriksaan integritas referensi yang dilakukan oleh kunci asing yang diterapkan. Kunci asing informasi berguna jika menerapkan integritas referensi yang ketat tidak praktis atau menimbulkan overhead performa yang signifikan.

Melanjutkan contoh sebelumnya, bayangkan Anda ingin membuat model hubungan antara tabel Customers, Orders, dan Products. Namun, menerapkan integritas referensi yang ketat dalam data tabel dapat menyebabkan bottleneck performa, terutama selama periode belanja puncak dengan volume pesanan yang tinggi. Selain itu, pelanggan mungkin melakukan pemesanan untuk produk yang dihentikan dan dihapus dari tabel Products.

Anda dapat membuat tabel Orders menggunakan kunci asing informasi:

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,
    CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID) NOT ENFORCED
) PRIMARY KEY (OrderID);

Dengan membuat kunci asing informasi dengan NOT ENFORCED, Anda memungkinkan kemungkinan pesanan mereferensikan pelanggan atau produk yang tidak ada. Menggunakan kunci asing informasi, bukan batasan kunci asing yang diterapkan, adalah pilihan yang baik jika akun pelanggan mungkin dihapus atau produk mungkin dihentikan. Dengan kunci asing informasi, Spanner tidak melakukan validasi integritas referensi. Hal ini mengurangi overhead operasi tulis, yang berpotensi meningkatkan performa selama waktu pemrosesan pesanan puncak.

Anda dapat mengizinkan pengoptimal kueri menggunakan hubungan untuk menghasilkan rencana kueri yang efisien. Hal ini dapat meningkatkan performa kueri yang menggabungkan tabel pada kolom kunci asing. Untuk mengetahui informasi selengkapnya, lihat kunci asing informatif untuk pengoptimalan kueri.

Perhatian: Jika logika aplikasi Anda memerlukan integritas referensi yang ketat (misalnya, mencegah pesanan untuk produk yang tidak ada), Anda perlu menerapkan pemeriksaan tambahan di tingkat aplikasi. Hasil kueri mungkin terpengaruh jika ada inkonsistensi dalam data. Misalnya, kueri yang menggabungkan Orders dengan Products dapat menampilkan pesanan dengan ProductID yang dihentikan jika pengoptimal menggunakan kunci asing informasi.

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;

Integritas referensial dengan kunci asing yang diterapkan

Alasan utama menambahkan hubungan kunci asing yang diterapkan 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 ditunjukkan dalam tabel Orders. Karena adanya batasan kunci asing yang diterapkan yang berlaku, data yang disisipkan ke dalam tabel Orders memiliki integritas referensi.

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

  Tidak seperti kunci asing yang diterapkan, kunci asing informasi tidak menerapkan integritas referensi. Jika FK_CustomerOrder adalah kunci asing informasi, pernyataan penyisipan akan berhasil karena Spanner tidak memvalidasi bahwa CustomerID yang sesuai ada di tabel Customers. Oleh karena itu, data mungkin tidak sesuai dengan integritas referensi yang ditentukan oleh FK_CustomerOrder.

• Mencoba menghapus baris dari tabel Customers saat pelanggan direferensikan dalam batasan kunci asing yang diterapkan.

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

  Demikian pula, jika FK_CustomerOrder adalah kunci asing informasi, tindakan penghapusan akan berhasil karena Spanner tidak menjamin integritas referensi kunci asing informasi.

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;

Apakah kunci asing diterapkan atau tidak diterapkan?

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

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

Topik berikut menunjukkan cara menggunakan kunci asing untuk menerapkan hubungan yang lebih kompleks antartabel.

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 yang diterapkan menunjukkan bahwa:

• Setiap nilai SongName dalam tabel TopHits harus memiliki nilai yang cocok dalam tabel Songs.

• 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

1. Buat TableA, tanpa kunci asing.
2. Buat TableB dengan batasan kunci asing pada TableA.
3. 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 berikutnya

• Pelajari dukungan kunci asing di Spanner lebih lanjut.

• Pelajari INFORMATION SCHEMA Spanner lebih lanjut.