सवाल सशर्त अद्वितीय बाधा


मेरे पास एक ऐसी स्थिति है जहां मुझे कॉलम के सेट पर एक अद्वितीय बाधा लागू करने की आवश्यकता है, लेकिन केवल कॉलम के एक मान के लिए।

तो उदाहरण के लिए मेरे पास टेबल (आईडी, नाम, रिकॉर्डस्टैटस) जैसी तालिका है।

रिकॉर्डस्टैटस में केवल एक मान 1 या 2 (सक्रिय या हटाया जा सकता है) हो सकता है, और मैं रिकॉर्डस्टैटस = 1 पर केवल एक आईडी (रिकॉर्डस्टैटस) पर एक अनन्य बाधा बनाना चाहता हूं, क्योंकि मुझे कोई परवाह नहीं है कि इसके साथ कई हटाए गए रिकॉर्ड हैं आईडी।

ट्रिगर्स लिखने के अलावा, क्या मैं ऐसा कर सकता हूं?

मैं एसक्यूएल सर्वर 2005 का उपयोग कर रहा हूँ।


76
2018-05-14 21:57


मूल


यह डिजाइन एक आम दर्द है। क्या आपने डिज़ाइन को बदलने पर विचार किया है ताकि धारणात्मक रूप से 'हटाए गए' रिकॉर्ड्स तालिका से भौतिक रूप से हटा दिए जाएं और शायद 'संग्रह' तालिका में स्थानांतरित हो जाएं? - onedaywhen
... क्योंकि एक साधारण कुंजी को लागू करने के लिए एक विशिष्ट बाधा लिखने में असमर्थता को 'कोड गंध', आईएमओ माना जाना चाहिए। यदि आप डिज़ाइन (एसक्यूएल डीडीएल) नहीं बदल सकते हैं क्योंकि कई अन्य टेबल इस तालिका का संदर्भ देते हैं तो मैं डर दूंगा कि आपका एसक्यूएल डीएमएल भी परिणामस्वरूप पीड़ित है यानी आपको जोड़ना याद रखना होगा ... और Table.RecordStatus = 1 ' अधिकांश खोज स्थितियों में और इस तालिका से जुड़े स्थितियों में शामिल होने और सूक्ष्म बग का अनुभव करते समय जब यह अनिवार्य रूप से अवसर पर छोड़ा जाता है। - onedaywhen


जवाब:


इस तरह एक चेक बाधा जोड़ें। अंतर यह है कि यदि स्थिति = 1 और गणना> 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



मैंने टेबल लेवल चेक बाधाओं को देखा लेकिन यह नहीं देखा कि फ़ंक्शन में डाले गए मानों को अपडेट करने या अपडेट करने का कोई तरीका है, क्या आप जानते हैं कि कैसे करें? - np-hard
ठीक है, मैंने एक नमूना स्क्रिप्ट पोस्ट की है जो आपको यह साबित करने में मदद करेगी कि मैं किस बारे में बात कर रहा हूं। मैंने इसका परीक्षण किया और यह काम करता है। यदि आप दो टिप्पणी वाली लाइनों को देखते हैं, तो आपको वह संदेश दिखाई देगा जो मुझे मिलता है। नोटा फायदे, मेरे कार्यान्वयन में, मैं केवल यह सुनिश्चित करता हूं कि आप एक ही आईडी के साथ एक दूसरा आइटम नहीं जोड़ सकते जो सक्रिय है यदि पहले से ही एक सक्रिय है। आप तर्क को संशोधित कर सकते हैं कि यदि कोई सक्रिय है, तो आप एक ही आईडी के साथ कोई आइटम नहीं जोड़ सकते हैं। इस पैटर्न के साथ, संभावनाएं बहुत अंतहीन हैं। - D. Patrick
मैं एक ट्रिगर में एक ही तर्क पसंद करेंगे। "स्केलर फ़ंक्शन में एक क्वेरी ... यदि आपकी जांच की बाधा किसी क्वेरी पर निर्भर करती है तो बड़ी समस्याएं पैदा हो सकती हैं और यदि किसी भी अद्यतन से एक से अधिक पंक्ति प्रभावित होती हैं। क्या होता है कि कथन पूर्ण होने से पहले प्रत्येक पंक्ति के लिए बाधा जांच जाती है इसका मतलब है कि स्टेटमेंट परमाणु टूट गया है और फ़ंक्शन को असंगत स्थिति में डेटाबेस के संपर्क में लाया जाएगा। परिणाम अप्रत्याशित और गलत हैं। " देख: blogs.conchango.com/davidportas/archive/2007/02/19/... - onedaywhen
यह केवल आंशिक रूप से सच है। डेटाबेस लगातार और अनुमानित व्यवहार करता है। तालिका में पंक्ति जोड़ने के बाद और डीबीएम द्वारा लेनदेन करने से पहले चेक बाधा निष्पादित की जाएगी और आप उस पर भरोसा कर सकते हैं। वह ब्लॉग एक सुंदर अनूठी समस्या के बारे में बात कर रहा था जहां आपको एक समय में केवल एक डालने की बजाए आवेषण के सेट के खिलाफ बाधा को निष्पादित करने की आवश्यकता है। आशीष एक समय में एक डालने पर बाधा मांग रही है और यह बाधा सटीक, अनुमानित और लगातार काम करेगी। मुझे खेद है अगर यह झुका हुआ आवाज; मैं पात्रों से बाहर चला रहा था। - D. Patrick
यह आवेषण के लिए बहुत अच्छा काम करता है लेकिन अपडेट के लिए काम नहीं करता है। E.G. अन्य आवेषणों के बाद इसे जोड़ना यहां काम करता है जब मैंने इसकी अपेक्षा नहीं की थी। चेककोनस्टेंट वैल्यूज में प्रवेश करें (1, 'कोई समस्या नहीं', 2); चेककॉन्स्ट्रेन सेट रिकॉर्डस्टैटस = 1 अपडेट करें जहां नाम = 'कोई समस्या नहीं' - dwidel


देखो, फ़िल्टर सूचकांक। दस्तावेज से (जोर मेरा):

एक फ़िल्टर किए गए इंडेक्स एक अनुकूलित गैर-क्लस्टर इंडेक्स है जो विशेष रूप से डेटा के एक अच्छी तरह से परिभाषित सबसेट से चुनने वाले प्रश्नों को कवर करने के लिए उपयुक्त है। यह तालिका में पंक्तियों के एक हिस्से को इंडेक्स करने के लिए फ़िल्टर अनुमान का उपयोग करता है। एक अच्छी तरह से डिज़ाइन किए गए फ़िल्टर किए गए इंडेक्स में क्वेरी प्रदर्शन में सुधार हो सकता है साथ ही पूर्ण-तालिका इंडेक्स की तुलना में इंडेक्स रखरखाव और संग्रहण लागत को भी कम किया जा सकता है।

और यहां फ़िल्टर उदाहरण के साथ एक अद्वितीय इंडेक्स का संयोजन करने वाला एक उदाहरण है:

create अद्वितीय index [MyIndex]
on [MyTable]([ID])
जहां [रिकॉर्डस्टैटस] = 1

यह अनिवार्य रूप से विशिष्टता को लागू करता है ID कब RecordStatus है 1

नोट: फ़िल्टर किए गए इंडेक्स को SQL Server 2008 में पेश किया गया था। SQL सर्वर के पुराने संस्करणों के लिए, कृपया देखें यह जवाब


112
2018-03-01 00:37



ध्यान दें कि SQL सर्वर की आवश्यकता है ansi_padding फ़िल्टर किए गए इंडेक्स के लिए, इसलिए सुनिश्चित करें कि यह विकल्प निष्पादित करके चालू है SET ANSI_PADDING ON एक फ़िल्टर सूचकांक बनाने से पहले। - naXa


आप हटाए गए रिकॉर्ड्स को उस तालिका में ले जा सकते हैं जिसमें बाधा नहीं है, और शायद एक तालिका की उपस्थिति को संरक्षित रखने के लिए दो तालिकाओं के यूनियन के साथ एक दृश्य का उपयोग करें।


9
2018-05-14 22:01



यह वास्तव में सुंदर चालाक कार्ल है। यह प्रति प्रश्न का उत्तर नहीं है, लेकिन यह एक अच्छा समाधान है। यदि तालिका में बहुत सारी पंक्तियां हैं, जो सक्रिय रिकॉर्ड की तलाश में तेजी ला सकती हैं क्योंकि आप सक्रिय रिकॉर्ड तालिका देख सकते हैं। यह बाधा को भी तेज करेगा क्योंकि अनन्य बाधा एक सूचकांक का उपयोग करती है जो कि नीचे लिखी गई जांच बाधा के विपरीत है जिसे गिनती निष्पादित करना है। मुझें यह पसंद है। - D. Patrick


आप इसे वास्तव में हैकी तरीके से कर सकते हैं ...

अपनी तालिका पर एक योजनाबद्ध दृश्य बनाएँ।

जो कुछ भी बनाएँ उसे बनाएं तालिका से चुनें * जहां रिकॉर्डस्टैटस = 1

अब अपने इच्छित फ़ील्ड के साथ दृश्य पर एक अनूठी बाधा बनाएं।

स्कीमबाउंड विचारों के बारे में एक नोट हालांकि, यदि आप अंतर्निहित तालिकाओं को बदलते हैं तो आपको दृश्य को फिर से बनाना होगा। इसके कारण गॉथस के बहुत सारे।


3
2018-05-14 22:43



यह एक बहुत अच्छा सुझाव है, और यह नहीं कि "हैकी"। इसके बारे में यहां अधिक जानकारी दी गई है फ़िल्टर सूचकांक विकल्प। - Scott Whitlock
यह एक बुरा विचार है। सवाल यह नहीं है। - FabianoLothor
मैंने एक बार एक योजनाबद्ध दृश्य का उपयोग किया, और कभी गलती दोहराई नहीं है। वे काम करने के लिए शाही दर्द हो सकते हैं। ऐसा नहीं है कि यदि आप अंतर्निहित तालिका को बदलते हैं तो आपको दृश्य को फिर से बनाना होगा - कम से कम SQL सर्वर में, आपको संभावित रूप से सभी विचारों के लिए ऐसा करना होगा। यह है कि आप पहली बार दृश्य को छोड़ दिए बिना तालिका को नहीं बदल सकते हैं, जो आप इसके पहले संदर्भों को छोड़ दिए बिना करने में सक्षम नहीं हो सकते हैं। ओह, प्लस स्टोरेज समस्याग्रस्त हो सकता है - या तो अंतरिक्ष की वजह से, या लागत के कारण यह सम्मिलित करने और अपडेट करने के लिए जोड़ता है। - MattW


क्योंकि, आप डुप्लिकेट की अनुमति देने जा रहे हैं, एक अनूठी बाधा काम नहीं करेगी। आप रिकॉर्डस्टैटस कॉलम के लिए चेक बाधा बना सकते हैं और आईएनएसईआरटी के लिए एक संग्रहीत प्रक्रिया जो डुप्लिकेट आईडी डालने से पहले मौजूदा सक्रिय रिकॉर्ड की जांच कर सकते हैं।


1
2018-05-14 21:59





यदि आप बिल के सुझाव के रूप में रिकॉर्डस्टैटस के रूप में न्यूल का उपयोग नहीं कर सकते हैं, तो आप अपने विचार को फ़ंक्शन-आधारित इंडेक्स के साथ जोड़ सकते हैं। एक ऐसा फ़ंक्शन बनाएं जो NULL लौटाता है यदि रिकॉर्डस्टैटस उन मानों में से एक नहीं है जिन्हें आप अपनी बाधा (और रिकॉर्डस्टैटस अन्यथा) पर विचार करना चाहते हैं और उस पर एक अनुक्रमणिका बनाते हैं।

इसका लाभ यह होगा कि आपको अपनी बाधा में तालिका में अन्य पंक्तियों की स्पष्ट रूप से जांच करने की आवश्यकता नहीं है, जो आपको प्रदर्शन के मुद्दों का कारण बन सकता है।

मुझे कहना चाहिए कि मुझे एसक्यूएल सर्वर बिल्कुल नहीं पता है, लेकिन मैंने ओरेकल में इस दृष्टिकोण का सफलतापूर्वक उपयोग किया है।


1
2018-05-14 22:48



अच्छा विचार है, लेकिन उत्तर के लिए एसक्यूएल सर्वर धन्यवाद में अनुक्रमित कोई फ़ंक्शन आधारित नहीं है - np-hard