Bagaimana untuk Menguatkuasakan Kekangan Unik dalam PostgreSQL dengan Lajur NULL?

Kekangan Unik PostgreSQL dan Lajur NULL: Panduan Praktikal

Mengendalikan kekangan unik dalam PostgreSQL apabila berurusan dengan lajur boleh batal memerlukan pertimbangan yang teliti. Bayangkan jadual dengan lajur UserId, MenuId dan RecipeId, dengan UserId dan RecipeId bukan nol. Kekangan unik standard pada lajur ini akan membenarkan entri pendua jika MenuId berbeza, termasuk nilai NULL.

Berikut ialah strategi yang berkesan untuk menangani perkara ini:

PostgreSQL 15 dan Versi Kemudian

PostgreSQL 15 memperkenalkan klausa NULLS NOT DISTINCT, menyediakan penyelesaian yang mudah. Klausa ini menganggap nilai NULL sebagai sama apabila menguatkuasakan keunikan dalam kekangan dan indeks:

<code class="language-sql">ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);</code>

PostgreSQL 14 dan Versi Terdahulu

Untuk versi PostgreSQL yang lebih lama, pendekatan yang disyorkan melibatkan penciptaan indeks separa:

<code class="language-sql">CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id)
WHERE menu_id IS NOT NULL;

CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id)
WHERE menu_id IS NULL;</code>

Ini secara berkesan menguatkuasakan keunikan dengan mencipta indeks berasingan untuk baris di mana menu_id adalah NULL dan di mana ia BUKAN NULL.

Pertimbangan Penting untuk Indeks Separa

Menggunakan indeks separa memperkenalkan beberapa had:

• Kekangan kunci asing tidak boleh digunakan terus pada gabungan tiga lajur (user_id, menu_id, recipe_id).
• Pengelompokkan berdasarkan indeks separa tidak disokong.
• Pertanyaan kekurangan klausa WHERE padanan tidak akan menggunakan indeks separa.

Amalan Terbaik:

Adalah dinasihatkan untuk menggunakan pengecam huruf kecil (cth., favorites) dalam PostgreSQL untuk konsistensi dan untuk mengelakkan kemungkinan isu. Memilih kaedah yang sesuai bergantung pada versi PostgreSQL anda dan keperluan khusus. Klausa NULLS NOT DISTINCT menawarkan penyelesaian yang lebih bersih dalam versi yang lebih baharu, manakala indeks separa menyediakan alternatif berfungsi untuk versi yang lebih lama.

Atas ialah kandungan terperinci Bagaimana untuk Menguatkuasakan Kekangan Unik dalam PostgreSQL dengan Lajur NULL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!