Pertanyaan kendala unik bersyarat


Saya memiliki situasi di mana saya perlu menerapkan batasan unik pada satu set kolom, tetapi hanya untuk satu nilai kolom.

Jadi misalnya saya memiliki tabel seperti Tabel (ID, Nama, RecordStatus).

RecordStatus hanya dapat memiliki nilai 1 atau 2 (aktif atau dihapus), dan saya ingin membuat batasan unik pada (ID, RecordStatus) hanya ketika RecordStatus = 1, karena saya tidak peduli jika ada beberapa catatan yang dihapus dengan yang sama ID.

Terlepas dari menulis pemicu, bisakah saya melakukannya?

Saya menggunakan SQL Server 2005.


76
2018-05-14 21:57


asal


Jawaban:


Tambahkan kendala cek seperti ini. Bedanya, Anda akan kembali salah jika Status = 1 dan Hitung> 0.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

CREATE TABLE CheckConstraint
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN

  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

ALTER TABLE CheckConstraint
  ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));

INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);

INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);

SELECT * FROM CheckConstraint;
-- Id   Name         RecordStatus
-- ---- ------------ ------------
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  1
-- 2    Oh no!       1
-- 2    Oh no!       2

ALTER TABLE CheckConstraint
  DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;

34
2018-05-14 22:06



Melihat, indeks yang difilter. Dari dokumentasi (penekanan tambang):

Indeks yang difilter adalah indeks nonkluster yang dioptimalkan terutama yang sesuai untuk menutupi kueri yang memilih dari subkumpulan data yang terdefinisi dengan baik. Ini menggunakan predikat filter untuk mengindeks sebagian baris dalam tabel. Indeks filter yang dirancang dengan baik dapat meningkatkan kinerja kueri serta mengurangi biaya pemeliharaan dan penyimpanan indeks dibandingkan dengan indeks tabel penuh.

Dan inilah contoh yang menggabungkan indeks unik dengan predikat filter:

create unik index [MyIndex]
on [MyTable]([ID])
di mana [RecordStatus] = 1

Ini pada dasarnya memaksakan keunikan ID kapan RecordStatus aku s 1.

Catatan: indeks yang difilter diperkenalkan di SQL Server 2008. Untuk versi SQL Server yang lebih lama, silakan lihat jawaban ini.


112
2018-03-01 00:37



Anda bisa memindahkan catatan yang terhapus ke tabel yang tidak memiliki kendala, dan mungkin menggunakan tampilan dengan UNION dari dua tabel untuk mempertahankan tampilan satu tabel.


9
2018-05-14 22:01



Anda dapat melakukan ini dengan cara yang benar-benar berbahaya ...

Buat tampilan skematis di meja Anda.

MENCIPTAKAN VIEW Apapun PILIH * DARI Tabel DI MANA RecordStatus = 1

Sekarang buat batasan unik pada tampilan dengan bidang yang Anda inginkan.

Satu catatan tentang pandangan schemabound, jika Anda mengubah tabel yang mendasari Anda harus membuat ulang tampilan. Banyak gotcha karena itu.


3
2018-05-14 22:43



Karena, Anda akan memperbolehkan duplikat, batasan unik tidak akan berfungsi. Anda dapat membuat kendala cek untuk kolom RecordStatus dan prosedur yang tersimpan untuk INSERT yang memeriksa catatan aktif yang ada sebelum memasukkan ID duplikat.


1
2018-05-14 21:59



Jika Anda tidak dapat menggunakan NULL sebagai RecordStatus sebagaimana disarankan oleh Bill, Anda dapat menggabungkan idenya dengan indeks berbasis fungsi. Buat fungsi yang mengembalikan NULL jika RecordStatus bukan salah satu dari nilai yang ingin Anda pertimbangkan dalam batasan Anda (dan sebaliknya RecordStatus) dan membuat indeks di atasnya.

Itu akan memiliki keuntungan bahwa Anda tidak harus secara eksplisit memeriksa baris lain di dalam tabel dalam batasan Anda, yang dapat menyebabkan masalah kinerja Anda.

Saya harus mengatakan saya tidak tahu SQL server sama sekali, tetapi saya telah berhasil menggunakan pendekatan ini di Oracle.


1
2018-05-14 22:48