रिलेशनल डेटाबेस डिज़ाइन गाइड। कैस्केडिंग डेटा विलोपन

अनूदित लेखों के चक्र में जोड़।
लेख: 1-3 , 4-6 , 7-9 , 10-13 , 14-15

लेख की जानकारी मैनुअल के 5 वें भाग को संदर्भित करती है।

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

परिचय।

यदि आप डेटाबेस विकसित करने वाले व्यक्ति की दार्शनिक स्थिति से शुरू करते हैं, तो विदेशी चाबियाँ सुविधाजनक हैं और जीवन को सरल बनाती हैं (ज्यादातर मामलों में, हमेशा अपवाद होते हैं।)। यहां तक ​​कि डेटाबेस के संबंधपरक सिद्धांत में अनभिज्ञ होने के बावजूद, इसके विकास के एक निश्चित चरण में विदेशी कुंजी का उपयोग करने की वास्तविक आवश्यकता, लगभग किसी भी व्यवसायी के लिए आता है (कथन शुरुआती को अधिक संदर्भित करता है) जो अभी भी इसके विकास में खड़ा नहीं है और यह सोचना जारी है। यहां तक ​​कि अगर वह अभी भी नहीं जानता है कि उसे क्या चाहिए एक विदेशी कुंजी संबंध कहा जाता है, तो वह स्वतंत्र रूप से डेटा को एक निश्चित तरीके से व्यवस्थित करना शुरू कर देता है, इसे अलग-अलग तालिकाओं में विभाजित करता है और उन्हें एक साथ जोड़ता है। तो यह स्पष्ट हो जाता है।
लेकिन विदेशी कुंजियों का उपयोग करते समय, भले ही आप ऐसी परिभाषा नहीं जानते हों, लेकिन डेटा कनेक्ट होने की निगरानी करना आवश्यक हो जाता है। इस लेख का विषय है, अगर मैं ऐसा कह सकता हूं, तो एक प्रकार का उपग्रह जो इस तरह के डेटा संगठन का अनुसरण करता है। और इस मामले में यह सिद्धांत जानने के लिए बहुत अधिक उपयोगी है, क्योंकि यह डेटाबेस के साथ काम करने की प्रक्रिया में जीवन को सरल बना सकता है।

बिंदु के करीब।

विदेशी कुंजी के बारे में इसे अनुवादों में बताया गया था, मैं इस पर ध्यान नहीं दूंगा। मैं आपको "उपग्रह" के बारे में बताऊंगा।

यदि आप सिद्धांत को नहीं जानते हैं, तो आपको स्वयं डेटा संबंधों का पालन करना होगा। एक विकल्प इस कार्य को डेटाबेस पर रखना है। डेटा लिंक की तथाकथित ट्रैकिंग क्या है? समझने के लिए, आपको एक उदाहरण की आवश्यकता है।

हमारे पास कुछ चीजें हैं। वे बिखरे हुए हैं, उनमें से कई हैं। हम चीजों को क्रम में रखना चाहते हैं। आदेश अक्सर एक वर्गीकरण (वर्गीकरण) और सूची है। हम ऑर्डर चाहते हैं, जबकि हम डेटाबेस के साथ काम करने में सक्षम हैं और कागज पर कुछ भी लिखना नहीं चाहते हैं। हम सभी चीजों को एक कॉलम में लिखते हैं। अगला, हम सूची को देखते हैं और श्रेणियों को निर्धारित करते हैं कि कौन सी चीजें हैं।

इसे हमारी चीज़ों का हिस्सा बनने दें, बाकी पर विचार नहीं किया जाएगा:

• पुस्तक 1
• पुस्तक २
• पुस्तक ३
• कंप्यूटर माउस
• कीबोर्ड
• लेखनी
• ऊन बेचनेवाला

हम अपने लिए निर्धारित करते हैं कि:

पुस्तक 1, पुस्तक 2, पुस्तक 3 पुस्तकें हैं, विचित्र रूप से पर्याप्त हैं।
एक कंप्यूटर माउस, कीबोर्ड कंप्यूटर बाह्य उपकरणों है।
एक कलम, एक स्टेपलर स्टेशनरी है।

हम डेटाबेस में दो टेबल बनाते हैं: श्रेणियां (श्रेणियां) और सामान (चीजें) ।

श्रेणियाँ (श्रेणी)

category_id | नाम

1 | किताबें
2 | कंप्यूटर बाह्य उपकरणों
3 | स्टेशनरी

सामान (सामान)

stuff_id | category_id | नाम

1 | 1 | पुस्तक 1
2 | 1 | पुस्तक २
3 | 1 | पुस्तक ३
4 | 2 | कंप्यूटर माउस
5 | 2 | कीबोर्ड
6 | 3 | लेखनी
7 | 3 | ऊन बेचनेवाला

PS चित्र habrastorage.org से प्रदर्शित नहीं होते हैं।

कुल: हमारे पास किताबें, कंप्यूटर परिधीय, स्टेशनरी हैं।

हम अपनी सभी पुस्तकों को फेंकना या दान करना चाहते थे, हम इन चीजों को घर में एक श्रेणी के रूप में नहीं देखना चाहते, हमें इलेक्ट्रॉनिक पुस्तकें पसंद हैं। हम श्रेणी तालिका से "पुस्तकें" श्रेणी निकालते हैं। इसी समय, हमारे पास अभी भी इस श्रेणी की चीजें एक अन्य तालिका में हैं, हम इन श्रेणियों को चीजों की तालिका में संदर्भित करते हैं। इसे संदर्भात्मक अखंडता उल्लंघन कहा जाता है। ऐसा लगता है कि हमारे पास कोई श्रेणी नहीं है, और इसलिए कोई किताबें नहीं हैं, लेकिन चीजों की तालिका में प्रविष्टियां बनी हुई हैं और बहुत सारी चीजें हैं और भविष्य में स्थिति फिर से हो सकती है और फिर हमारे बीच गड़बड़ होगी, बहुत सारी अनावश्यक जानकारी और आने वाले सभी परिणाम होंगे। हमारी जानकारी के साथ काम करने की सुविधा में, और डेटाबेस के साथ काम करते समय तकनीकी भाग में (उदाहरण के लिए, जानकारी खोज)। और यहां यह समझ आती है कि हमें दो तालिकाओं के साथ काम करने की आवश्यकता है, यह निगरानी करने के लिए कि किन मामलों में कनेक्शन को तोड़ा जा सकता है, तोड़ा जा सकता है और किसी प्रकार की शारीरिक गतिविधियां की जा सकती हैं और दो विकल्प हैं: इसे स्वयं करें या, यहां ज्ञान शक्ति है, हम इसे स्थानांतरित कर सकते हैं डेटाबेस पर सिरदर्द।

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

निम्नलिखित मामलों में रिश्ते टूट सकते हैं (यदि आप "सही" भाषा कहते हैं - संदर्भात्मक अखंडता टूट सकती है):

• विदेशी कुंजी को अद्यतन किया गया है (श्रेणी तालिका में पहचानकर्ता से लिंक)। हम किसी चीज़ के लिए एक श्रेणी (इस श्रेणी के पहचानकर्ता) को अपडेट कर रहे हैं, और हमसे गलती की जाती है, ऐसी कोई श्रेणी नहीं है। और ... हमारे पास एक चीज हवा में लटकी हुई है।
• एक नई वंशावली जोड़ी जाती है। एक नई चीज़ जोड़ें, और यह किसी भी श्रेणी से संबंधित नहीं है। वैसे, हम चीजों के बिना एक श्रेणी जोड़ सकते हैं। हमारे डेटाबेस में इतनी व्यवस्था है कि कोई चीज श्रेणी के बिना नहीं हो सकती है, और एक श्रेणी के बिना, क्योंकि यह किसी चीज का संदर्भ नहीं देता है।
• पूर्वज स्ट्रिंग हटाएं। हमारे मामले में ठीक यही हुआ है। हमने श्रेणी हटा दी, लेकिन चीजें बनी रहीं।
• पूर्वज स्ट्रिंग में प्राथमिक कुंजी को अद्यतन करना। हमने श्रेणी पहचानकर्ता को बदल दिया, और कुछ चीजें पिछली पहचानकर्ता को संदर्भित करती हैं। निचला रेखा: कुछ चीजें फिर से सीमित हैं।

एसक्यूएल की संदर्भित अखंडता को बनाए रखने का मतलब है ( जब आप सही कहेंगे, जब आपको इसकी आवश्यकता हो, तो मैं पूरी तरह से बात करूंगा; यदि हम MySQL RDBMS के बारे में बात करते हैं, तो इन उपकरणों का उपयोग विदेशी कुंजियों के साथ मिलकर करना केवल InnoDB तालिकाओं के लिए संभव है; विदेशी कुंजियों का उपयोग MyISAM में किया जा सकता है, एक निश्चित डेटा संरचना का निर्माण करता है, लेकिन तब ट्रैकिंग संचार के लिए सभी सिरदर्द उपयोगकर्ता पर पड़ते हैं ) आपको इन मामलों को संभालने की अनुमति देते हैं।

और यहां बताया गया है कि इन समस्याओं को कैसे हल किया जाता है (लिस्टिंग के क्रम में):

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

अब आखिरी दो। यहां स्थिति अधिक रोचक है।

• पूर्वज पंक्ति हटाएं। हमारे मामले में, अगर हम किसी श्रेणी को हटाते हैं, और चीजें चीजों की तालिका में इसका उल्लेख करेंगी। समस्या के कई समाधान हो सकते हैं। उनमें से कौन सा डेटाबेस डेवलपर द्वारा तय किया गया है (आप निर्दिष्ट करें)।
  
  1) किसी श्रेणी को हटाते समय, आप उससे संबंधित सभी चीजों को हटा सकते हैं।
  2) श्रेणियों को हटाने पर रोक लगाने के लिए जबकि कुछ चीजें उन्हें संदर्भित करती हैं।
  3) उन चीजों के लिए जो श्रेणी को हटाने के लिए संदर्भित करते हैं, श्रेणी के संदर्भ के रूप में एक NULL मान निर्दिष्ट करें।
  4) उन चीजों के लिए जो श्रेणी को हटाने के लिए संदर्भित करते हैं, NULL के अलावा एक डिफ़ॉल्ट मान निर्दिष्ट करें।
  
• पूर्वज स्ट्रिंग में प्राथमिक कुंजी को अपडेट करने के मामले में, विकल्प पिछले वाले के समान हैं।

अब कैस्केडिंग डेटा हटाने के बारे में, जिसका उल्लेख शुरुआत में किया गया था।
SQL में अद्यतन हटाने और (या) के नियमों को परिभाषित करते समय, कुछ वाक्य रचना का उपयोग किया जाता है:

FOREIGN KEY [key_name] (col1, …) REFERENCES table (table_col, …)
[ON DELETE {CASCADE|SET NULL|NO ACTION|RESTRICT|SET DEFAULT}]
[ON UPDATE {CASCADE|SET NULL|NO ACTION|RESTRICT|SET DEFAULT}]

जहां DELETE और OND UPATE के वैकल्पिक निर्माण आपको ऊपर बताई गई समस्या के बहुत समाधानों को निर्दिष्ट करने की अनुमति देते हैं। और ये कीवर्ड उन्हें नाम देते हैं:

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

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

कोई कार्रवाई नहीं - जब प्राथमिक कुंजी में पूर्वजों की तालिका में किसी रिकॉर्ड को हटाना या अपडेट करना, वंशज तालिका में कोई कार्रवाई नहीं की जाएगी।
हमारे मामले में, यदि हम श्रेणी तालिका में श्रेणी पहचानकर्ता को हटाते हैं या अपडेट करते हैं, तो इससे चीजों की तालिका प्रभावित नहीं होगी।

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

SET DEFAULT - यह नाम से स्पष्ट है कि जब प्राथमिक कुंजी वाली पूर्वजों की तालिका में प्रविष्टि को हटाना या अद्यतन करना होगा, तो डिफ़ॉल्ट प्रविष्टि को संबंधित प्रविष्टियों के लिए वंश तालिका में सेट किया जाएगा। वहाँ एक है "लेकिन"। MySQL RDBMS इस कीवर्ड का उपयोग नहीं करता है।

और अब फिर से - डेटा विलोपन का कैस्केडिंग करने के लिए। यह वास्तव में सुनवाई पर क्यों है? पहली बार में उसके बारे में क्यों पूछा गया, इस तथ्य के बावजूद कि यह केवल एक है। शायद इसलिए क्योंकि डेटा डिलीट करना कैस्केडिंग समस्या का सबसे आम समाधान है।