Soru koşullu benzersiz kısıtlama


Bir sütun kümesinde benzersiz bir kısıtlama uygulamak zorunda olduğum, ancak bir sütunun yalnızca bir değeri için gereken bir durum var.

Yani örneğin Tablo (ID, Name, RecordStatus) gibi bir tablo var.

RecordStatus yalnızca 1 veya 2 (etkin veya silinmiş) bir değere sahip olabilir ve ben sadece RecordStatus = 1 olduğunda benzersiz bir kısıtlama oluşturmak istiyorum (ID, RecordStatus), çünkü aynı anda birden fazla silinmiş kayıt olup olmadığından umurumda değil İD.

Tetikleyicileri yazmanın dışında bunu yapabilir miyim?

SQL Server 2005 kullanıyorum.


76
2018-05-14 21:57


Menşei


Bu tasarım yaygın bir acıdır. Tasarımı değiştirmeyi düşündüğünüzde, "silinmiş" kayıtlar fiziksel olarak tablodan silinir ve belki de bir 'arşiv' tablosuna taşınır mı? - onedaywhen
... çünkü basit bir anahtarı zorlamak için bir UNIQUE kısıtlaması yazamaması bir 'kod kokusu', IMO olarak kabul edilmelidir. Başka bir tablo bu tabloya başvuruda bulunduğundan, tasarımı değiştiremiyorsanız (SQL DDL), SQL DML’nizin de bir sorun olarak karşılaşacağından bahsetmeliyiz, yani eklemek istediğinizde ... AND Table.RecordStatus = 1 ' Çoğu arama koşuluna ve bu tabloyu içeren koşullara katılmaya ve kaçınılmaz olarak zaman zaman göz ardı edildiğinde ince hatalara maruz kalmaya. - onedaywhen


Cevaplar:


Böyle bir çek sınırlaması ekleyin. Fark, Status = 1 ve Count> 0 ise false döndürürsünüz.

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



Tablo seviyesinde kontrol kısıtlamalarına baktım ama görünmüyor veya işleve güncellenen değerleri iletmenin herhangi bir yolu yok, nasıl yapılacağını biliyor musun? - np-hard
Tamam, neden bahsettiğimi kanıtlamanıza yardımcı olacak bir örnek senaryo yayınladım. Test ettim ve işe yarıyor. İki yorumlanan çizgiye bakarsanız, aldığım mesajı görürsünüz. Nota bene, benim uygulamamda, sadece bir aktif olan varsa aktif olan aynı ID ile ikinci bir öğe ekleyemezsiniz. Mantığı, aktif olan varsa, aynı kimliğe sahip herhangi bir öğe ekleyemezsiniz. Bu örüntüyle, olasılıklar neredeyse sonsuzdur. - D. Patrick
Bir tetikleyicide aynı mantığı tercih ederim. "scalar işlevindeki bir sorgu ... CHECK kısıtlamanız bir sorguya dayanıyorsa ve birden fazla satır herhangi bir güncelleştirmeden etkileniyorsa büyük sorunlar yaratabilir. Ne olur, kısıtlama, ifade tamamlanmadan önce her satır için bir kez kontrol edilir Bu, atomizasyonun kırık olduğunu ve işlevin tutarsız bir durumda veritabanına yansıtılacağı anlamına gelir. Sonuçlar tahmin edilemez ve yanlıştır. " Görmek: blogs.conchango.com/davidportas/archive/2007/02/19/... - onedaywhen
Bu sadece bir gün doğrudur. Veritabanı tutarlı ve öngörülebilir şekilde davranır. Satır tabloya eklendikten sonra ve işlem dbms tarafından işlenmeden önce kontrol kısıtlaması yürütülür ve buna güvenebilirsiniz. Bu blog, her seferinde yalnızca bir ekleme yerine bir dizi eke karşı kısıtlamayı yürütmeniz gereken oldukça benzersiz bir sorun hakkında konuşuyordu. Ashish, bir seferde bir eklenti için bir kısıtlama ister ve bu kısıtlama, doğru, öngörülebilir ve tutarlı bir şekilde çalışacaktır. Bu kulağa hoş geliyorsa özür dilerim; Karakterlerin tükeniyordu. - D. Patrick
Bu, ekler için harika çalışıyor ancak güncellemeler için işe yaramıyor. ÖRNEĞİN. Bunu ekledikten sonra diğer ekler eklendikten sonra bunu ekler. CheckConstraint DEĞERLERİNE (1, 'No ProblemsA', 2) INSERT INSERT; CheckConstraint set güncelleme Recordstatus = 1 isim = 'No ProblemsA' - dwidel


Bakın, filtrelenmiş dizin. Belgelerden (vurgu benim):

Filtrelenmiş bir dizin, özellikle iyi tanımlanmış bir veri alt kümesinden seçilen sorguları kapsayacak şekilde uyarlanmış optimize edilmiş kümelenmemiş bir dizindir. Tablodaki satırların bir bölümünü dizine eklemek için bir filtre yüklemi kullanır. İyi tasarlanmış bir filtrelenmiş dizin, sorgu performansını iyileştirmenin yanı sıra, tam tablo dizinleriyle karşılaştırıldığında dizin bakımı ve depolama maliyetlerini azaltabilir.

Ve burada benzersiz bir endeksi bir filtre yüklemiyle birleştiren bir örnek:

create benzersiz index [MyIndex]
on [MyTable]([ID])
[RecordStatus] = 1 nerede

Bu esas olarak benzersizliği zorlar. ID ne zaman RecordStatus olduğu 1.

Not: filtrelenmiş dizin SQL Server 2008'de tanıtıldı. SQL Server'ın önceki sürümleri için lütfen bkz. bu cevap.


112
2018-03-01 00:37



SQL Server gerektirir ansi_padding Filtre uygulanmış dizinler için, bu seçeneğin çalıştırılarak açık olduğundan emin olun. SET ANSI_PADDING ON filtrelenmiş bir dizin oluşturmadan önce. - naXa


Silinen kayıtları kısıtlamadan yoksun bir tabloya taşıyabilir ve belki de tek bir tablonun görünümünü korumak için iki tablodan UNION ile bir görünüm kullanabilirsiniz.


9
2018-05-14 22:01



Aslında oldukça zeki Carl. Bu sorunun kendisi için bir cevap değil, ama bu iyi bir çözüm. Tabloda çok sayıda satır varsa, bu da aktif kayıt tablosuna bakabildiğiniz için etkin bir kayıt aramayı hızlandırabilir. Aynı zamanda kısıtlamayı da hızlandıracaktır çünkü benzersiz kısıtlama, aşağıda bir yazım yürütmesi gereken kontrol sınırının tersine bir indeks kullanır. Bunu sevdim. - D. Patrick


Bunu gerçekten hacky bir şekilde yapabilirsin ...

Masanızda bir schemabound görünümü oluşturun.

VIEW CREATE Ne olursa olsun SELECT * Tablodan WERE RecordStatus = 1

Şimdi, istediğiniz alanlarla görünümde benzersiz bir kısıtlama oluşturun.

Şemabound görünümleriyle ilgili bir not, altta yatan tabloları değiştirirseniz, görünümü yeniden oluşturmanız gerekir. Bunun yüzünden bol bol var.


3
2018-05-14 22:43



Bu oldukça iyi bir öneri ve "hacky" değil. İşte bu konuda daha fazla bilgi filtrelenmiş dizin alternatifi. - Scott Whitlock
Bu kötü bir fikir. Soru bu değil. - FabianoLothor
Bir kez şemabound görünümü kullandım ve hatayı tekrarlamadım. Çalışmak için bir kraliyet acısı olabilirler. Alttaki tabloyu değiştirirseniz görünümü yeniden oluşturmanız gerekmez. En azından SQL Server'da tüm görünümler için bunu yapmak zorundasınız. Tabloyu ilk önce görünümü düşürmeden değiştiremezsiniz; bu, ilk önce referansları düşürmeden yapamayacağınız olabilir. Ayrıca, depolama alanı sorunlu olabilir - ya alan yüzünden ya da ekleme ve güncelleme için harcadığı maliyet nedeniyle. - MattW


Çünkü, kopyalara izin vereceksin, benzersiz bir kısıtlama işe yaramayacak. RecordStatus sütunu için bir denetim kısıtlaması ve yinelenen kimlikler eklemeden önce varolan etkin kayıtları denetleyen INSERT için saklı yordam oluşturabilirsiniz.


1
2018-05-14 21:59





NULL'ı Bill'in önerdiği gibi bir RecordStatus olarak kullanamazsanız, onun fikrini işlev tabanlı bir dizinle birleştirebilirsiniz. RecordStatus, kısıtlamanız (ve aksi takdirde RecordStatus) üzerinde düşünmek istediğiniz değerlerden biri değilse ve bunun üzerine bir dizin oluşturuyorsa, NULL döndüren bir işlev oluşturun.

Bu, kısıtlamalarınızda tablodaki diğer satırları açıkça incelemeniz gerekmediğinden, performans sorunlarına neden olabilmeniz avantajına sahip olacaktır.

SQL sunucusunu hiç bilmediğimi söylemeliyim, ancak bu yaklaşımı Oracle'da başarıyla kullandım.


1
2018-05-14 22:48



iyi bir fikir, ancak sql sunucuda endeksli hiçbir fonksiyon yok ama cevabınız için teşekkürler - np-hard