Membuat dan mengelola hubungan kunci asing

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

Kunci asing adalah kolom yang dibagikan antar-tabel untuk membuat link antara data terkait. Saat Anda menggunakan kunci asing, Spanner memastikan bahwa hubungan ini tetap terjaga.

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 dilakukan.
• Tabel Products menyimpan informasi produk untuk setiap produk.

Ada dua hubungan kunci asing 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 Products memastikan bahwa pesanan tidak dapat dibuat untuk produk yang tidak ada.

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

Secara default, semua kunci asing di Spanner adalah kunci asing yang diterapkan, yang menerapkan integritas referensial. Di Spanner, Anda juga dapat memilih untuk menggunakan kunci asing informasional, yang tidak memvalidasi atau menerapkan integritas referensial. Untuk mengetahui 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 bahwa Anda telah membuat tabel Customers di database pemesanan produk dasar Anda. Sekarang Anda memerlukan tabel Orders untuk menyimpan informasi tentang pesanan yang dilakukan pelanggan. Untuk memastikan semua pesanan valid, Anda tidak ingin sistem memasukkan baris ke dalam tabel Orders kecuali jika ada 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 disebutkan 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 penghapusan tabel menggunakan nama yang telah Anda pilih.

• Batasan memiliki nama FK_CustomerOrder. Nama batasan dicakup ke skema dan harus unik dalam skema.

• Tabel Orders, tempat Anda menentukan batasan, adalah tabel yang mereferensikan. 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 dirujuk dari tabel yang dirujuk sesuai dengan kolom kunci utama, tetapi hal ini tidak diperlukan. Tabel yang mereferensikan juga dapat mereferensikan kolom non-primer dari tabel yang direferensikan.

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 mengetahui 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 memiliki batasan yang ada, 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 kolom ini mereferensikan kolom ProductID di Products. Jika Anda tidak keberatan Spanner menamai batasan ini untuk Anda, gunakan sintaksis berikut:

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

Menambahkan kunci asing dengan tindakan penghapusan ke tabel baru

Ingat contoh sebelumnya saat Anda memiliki tabel Customers dalam database pemesanan produk yang memerlukan tabel Orders. Anda ingin menambahkan batasan kunci asing yang merujuk ke tabel Customers. Namun, Anda ingin memastikan bahwa saat Anda menghapus catatan pelanggan di 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 mencakup 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. 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 di tabel Orders yang mereferensikan nilai CustomerID yang dihapus, operasi penghapusan akan gagal dengan error pelanggaran batasan kunci asing.

Menambahkan kunci asing dengan tindakan penghapusan 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 informasional (khusus GoogleSQL)

Kunci asing informasional memungkinkan pengoptimal kueri memanfaatkan hubungan kunci asing tanpa overhead yang ditimbulkan dari pemeriksaan integritas referensial yang dilakukan oleh kunci asing yang diterapkan. Kunci asing informasional berguna saat menerapkan integritas referensial yang ketat tidak praktis atau menimbulkan overhead performa yang signifikan.

Melanjutkan contoh sebelumnya, bayangkan Anda ingin memodelkan hubungan antara tabel Customers, Orders, dan Products. Namun, menerapkan integritas referensial yang ketat dalam data tabel dapat menyebabkan bottleneck performa, terutama selama periode belanja puncak dengan volume pesanan yang tinggi. Selain itu, pelanggan dapat melakukan pemesanan 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 informasional dengan NOT ENFORCED, Anda memungkinkan kemungkinan pesanan mereferensikan pelanggan atau produk yang tidak ada. Menggunakan kunci asing informasional, 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 referensial. Hal ini mengurangi overhead penulisan, 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 informasional untuk pengoptimalan kueri.

Perhatian: Jika logika aplikasi Anda memerlukan integritas referensial 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 referensial data Anda. Jika Anda mengubah data dengan cara yang melanggar batasan kunci asing, pembaruan akan gagal dengan error.

Pertimbangkan data dalam Gambar 2. Beberapa pelanggan telah memesan produk, seperti yang ditunjukkan dalam tabel Orders. Karena batasan kunci asing yang diterapkan yang berlaku, data yang dimasukkan ke dalam tabel Orders memiliki integritas referensial.

Gambar 2. Data sampel di database pemesanan kami.

Contoh berikut menunjukkan apa yang terjadi saat Anda mencoba mengubah 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, mengingat 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 melakukannya, Anda akan memiliki pesanan yang tidak valid di sistem Anda. Namun, dengan batasan kunci asing yang diterapkan yang Anda tambahkan ke tabel Orders, tabel Anda terlindungi. INSERT gagal dengan pesan berikut, dengan asumsi batasannya 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 informasional tidak menerapkan integritas referensial. Jika FK_CustomerOrder adalah kunci asing informasi, maka pernyataan penyisipan berhasil karena Spanner tidak memvalidasi bahwa CustomerID yang sesuai ada di tabel Customers. Oleh karena itu, data mungkin tidak sesuai dengan integritas referensial yang ditentukan oleh FK_CustomerOrder.

• Mencoba menghapus baris dari tabel Customers saat pelanggan dirujuk 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 coba Anda 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 yang dirujuk di Orders terlebih dahulu. Anda juga dapat menentukan kunci asing dengan tindakan ON DELETE CASCADE agar Spanner menangani penghapusan entri yang dirujuk.

  Demikian pula, jika FK_CustomerOrder adalah kunci asing informasional, maka tindakan penghapusan berhasil karena Spanner tidak menjamin integritas referensial kunci asing informasional.

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

Untuk mengetahui informasi selengkapnya tentang indeks pendukung, lihat Kunci asing yang mendukung indeks.

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

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 akan dihapus secara otomatis saat batasan itu sendiri dihapus.

Dukungan untuk hubungan kunci asing yang lebih kompleks

Topik berikut menunjukkan cara menggunakan kunci asing untuk menerapkan hubungan yang lebih kompleks antar-tabel.

Beberapa kolom

Kunci asing dapat mereferensikan beberapa kolom. Daftar kolom membentuk kunci yang sesuai dengan kunci utama tabel atau indeks pendukung. Tabel yang merujuk 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 merujuknya, salah satu kunci asing harus ditambahkan dengan pernyataan ALTER TABLE. Berikut contohnya

1. Buat TableA, tanpa kunci asing.
2. Buat TableB dengan batasan kunci asing pada TableA.
3. Gunakan ALTER TABLE pada TableA untuk membuat referensi kunci asing ke TableB.

Tabel yang merujuk ke dirinya sendiri

Salah satu jenis khusus referensi melingkar adalah tabel yang menentukan kunci asing yang mereferensikan tabel yang sama. Misalnya, cuplikan berikut menunjukkan kunci asing untuk memastikan 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 lebih lanjut dukungan kunci asing di Spanner.

• Pelajari lebih lanjut SKEMA INFORMASI Spanner.