Bagaimana untuk Mencipta Kekangan Unik dengan Lajur Nullable dalam PostgreSQL?

Menguruskan Kekangan Unik dan Lajur Boleh Nullable dalam PostgreSQL

PostgreSQL membentangkan cabaran unik apabila menguatkuasakan kekangan keunikan merentas lajur yang membenarkan nilai NULL. Berbilang baris mungkin berkongsi nilai bukan NULL yang sama manakala hanya berbeza dalam entri NULL mereka. Ini memerlukan pengendalian NULL yang teliti dalam definisi kekangan.

PostgreSQL 15 dan Kemudian: NULLS NOT DISTINCT Penyelesaian

PostgreSQL 15 dan versi seterusnya memudahkan proses ini dengan klausa NULLS NOT DISTINCT. Klausa ini, apabila ditambah pada kekangan UNIK, menganggap nilai NULL sebagai setara. Oleh itu, hanya satu baris boleh wujud dengan gabungan nilai bukan NULL yang diberikan, tidak kira sama ada lajur boleh null mengandungi NULL.

ALTER TABLE favorites ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);

PostgreSQL 14 dan Terdahulu: Indeks Separa sebagai Penyelesaian

Untuk versi PostgreSQL yang lebih lama, pendekatan yang disyorkan melibatkan mencipta indeks separa. Indeks ini menguatkuasakan keunikan pada subset data, dengan berkesan mengendalikan NULL secara berasingan. Satu indeks meliputi baris di mana lajur boleh batal BUKAN NULL dan satu lagi mengendalikan baris di mana ia NULL.

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;

Pertimbangan Penting

Indeks separa, walaupun berkesan, mungkin mengehadkan kefungsian. Mereka boleh menyekat pilihan rujukan dan pengelompokan kunci asing. Jika indeks yang lengkap adalah penting, pertimbangkan kekangan UNIK yang merangkumi set lajur yang lebih luas, termasuk lajur boleh batal.

Pilihan lain, walaupun berkemungkinan tidak sesuai untuk semua situasi, adalah untuk menetapkan nilai lalai bukan NULL kepada lajur boleh batal. Ini memudahkan penciptaan kekangan tetapi boleh memberi kesan kepada integriti data bergantung pada keperluan aplikasi.

Akhir sekali, konvensyen penamaan yang konsisten adalah penting. Menggunakan pengecam huruf kecil dalam PostgreSQL meningkatkan kebolehbacaan dan mengelakkan potensi isu.

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