डेटाबेस में कुंजियाँ

5.1 परिचय

इंटरनेट हठधर्मिता के नियमों से भरा है कि कैसे कुंजियों को चुना जाना चाहिए और संबंधपरक डेटाबेस में उपयोग किया जाना चाहिए। कभी-कभी विवाद होलीवर में भी बदल जाते हैं: क्या प्राकृतिक या कृत्रिम चाबियों का उपयोग किया जाना चाहिए? स्वत: वृद्धि पूर्णांक या UUIDs?

चौंसठ लेखों को पढ़ने के बाद, पाँच पुस्तकों के खंडों को पलटते हुए, और IRC और StackOverflow पर ढेर सारे प्रश्न पूछने के बाद, मैंने (जो "बेग्रिफ़्स" नेल्सन, मूल लेख के लेखक) ने पहेली के टुकड़ों को एक साथ रखा है और अब विरोधियों से समझौता कर सकते हैं। कई महत्वपूर्ण विवाद वास्तव में किसी और के दृष्टिकोण की ग़लतफ़हमी से उत्पन्न होते हैं।

आइए समस्या को अलग करें और अंत में इसे वापस एक साथ रखें। सबसे पहले, आइए प्रश्न पूछें - "कुंजी" क्या है?

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

लेकिन पहले, कुछ सिद्धांत:

प्राथमिक कुंजी

प्राथमिक कुंजीतालिका में पंक्तियों की पहचान करने के लिए सीधे उपयोग किया जाता है। इसे निम्नलिखित प्रतिबंधों का पालन करना चाहिए:

• प्राथमिक कुंजी हर समय अद्वितीय होनी चाहिए।
• यह हमेशा तालिका में मौजूद होना चाहिए और इसका एक मूल्य होना चाहिए।
• इसका मूल्य बार-बार नहीं बदलना चाहिए। आदर्श रूप से, इसे मान बिल्कुल नहीं बदलना चाहिए ।

आमतौर पर, एक प्राथमिक कुंजी एक तालिका के एकल स्तंभ का प्रतिनिधित्व करती है, लेकिन यह कई स्तंभों वाली एक समग्र कुंजी भी हो सकती है।

समग्र कुंजी

कस्टम कुंजी- विशेषताओं (स्तंभों) का एक संयोजन जो विशिष्ट रूप से प्रत्येक तालिका पंक्ति की पहचान करता है। यह सभी कॉलम, और कई, और एक हो सकता है। इस स्थिति में, इन विशेषताओं के मान वाली पंक्तियों को दोहराया नहीं जाना चाहिए।

संभावित कुंजी

उम्मीदवार कुंजी- संबंध (तालिका) की न्यूनतम समग्र कुंजी का प्रतिनिधित्व करता है, जो कि कई शर्तों को पूरा करने वाली विशेषताओं का एक समूह है:

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

5.2 प्राथमिक कुंजियों का जिज्ञासु मामला

जिसे हमने पिछले अनुभाग में "कुंजी" कहा था, उसे आमतौर पर "उम्मीदवार कुंजी" कहा जाता है। "उम्मीदवार" शब्द का तात्पर्य है कि ऐसी सभी कुंजियाँ "प्राथमिक कुंजी" (प्राथमिक कुंजी) की मानद भूमिका के लिए प्रतिस्पर्धा करती हैं, और बाकी को "वैकल्पिक कुंजी" (वैकल्पिक कुंजी) सौंपी जाती हैं।

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

शब्द "कुंजी" का अर्थ फ़ाइल सॉर्ट कुंजी है, जिसे अनुक्रमिक फ़ाइल सिस्टम पर किसी प्रसंस्करण संचालन को करने के लिए आवश्यक था। पंच कार्ड का एक सेट एक और केवल एक क्रम में पढ़ा गया था; वापस जाना असंभव था। शुरुआती टेप ड्राइव ने समान व्यवहार की नकल की और द्विदिश पहुंच की अनुमति नहीं दी। यही है, मूल Sybase SQL सर्वर को पिछली पंक्ति को पढ़ने के लिए शुरुआत में तालिका को "रिवाइंड" करने की आवश्यकता होती है।

आधुनिक एसक्यूएल में, आपको सूचना के भौतिक प्रतिनिधित्व, टेबल मॉडल संबंधों पर ध्यान केंद्रित करने की आवश्यकता नहीं है, और पंक्तियों का आंतरिक क्रम बिल्कुल भी महत्वपूर्ण नहीं है। हालाँकि, अब भी SQL सर्वर डिफ़ॉल्ट रूप से प्राथमिक कुंजियों के लिए एक संकुल अनुक्रमणिका बनाता है और, पुरानी परंपरा के अनुसार, पंक्तियों के क्रम को भौतिक रूप से व्यवस्थित करता है।

अधिकांश डेटाबेस में, प्राथमिक कुंजियाँ अतीत की बात हैं, और प्रतिबिंब या भौतिक स्थान से थोड़ा अधिक प्रदान करती हैं। उदाहरण के लिए, एक PostgreSQL तालिका में, एक प्राथमिक कुंजी की घोषणा स्वचालित रूप से एक बाधा को लागू करती है NOT NULLऔर एक डिफ़ॉल्ट विदेशी कुंजी को परिभाषित करती है। इसके अलावा, प्राथमिक कुंजियाँ ऑपरेटर के लिए पसंदीदा कॉलम हैं JOIN।

प्राथमिक कुंजी अन्य कुंजियों को घोषित करने की संभावना को ओवरराइड नहीं करती है। उसी समय, यदि कोई कुंजी प्राथमिक के रूप में निर्दिष्ट नहीं की गई है, तो तालिका अभी भी ठीक काम करेगी। बिजली, किसी भी मामले में, आप पर वार नहीं करेगी।

5.3 प्राकृतिक कुंजी ढूँढना

ऊपर चर्चा की गई चाबियों को "प्राकृतिक" कहा जाता है क्योंकि वे प्रतिरूपित वस्तु के गुण हैं जो अपने आप में दिलचस्प हैं, भले ही कोई उनमें से कुंजी नहीं बनाना चाहता हो।

संभावित प्राकृतिक चाबियों के लिए तालिका की जांच करते समय याद रखने वाली पहली बात यह है कि बहुत स्मार्ट न होने का प्रयास करें। StackExchange पर उपयोगकर्ता sqlvogel निम्नलिखित सलाह देता है:

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

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

उदाहरण के लिए, हॉबी क्लब के सदस्यों के डेटाबेस में दो कॉलम में विशिष्टता हो सकती है - first_name, last_name. थोड़ी मात्रा में डेटा के साथ, डुप्लिकेट की संभावना नहीं है, और वास्तविक संघर्ष उत्पन्न होने से पहले, ऐसी कुंजी का उपयोग करना काफी उचित है।

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

यही कारण है कि मानकीकरण निकाय उत्पादों के लिए विशिष्ट चिह्न बनाते और लागू करते हैं। वाहनों पर वाहन पहचान संख्या (वीआईएन) की मुहर लगी होती है , किताबें आईएसबीएन के साथ मुद्रित होती हैं , और खाद्य पैकेजिंग में यूपीसी होते हैं । आप आपत्ति कर सकते हैं कि ये संख्याएँ प्राकृतिक नहीं लगतीं। तो मैं उन्हें प्राकृतिक चाबियां क्यों कहता हूं?

एक डेटाबेस में अद्वितीय गुणों की स्वाभाविकता या कृत्रिमता बाहरी दुनिया के सापेक्ष होती है। एक कुंजी जो कृत्रिम थी जब इसे एक मानक निकाय या सरकारी एजेंसी में बनाया गया था, हमारे लिए स्वाभाविक हो जाता है, क्योंकि यह पूरी दुनिया में एक मानक बन जाता है और / या वस्तुओं पर मुद्रित होता है।

मुद्राओं, भाषाओं, वित्तीय साधनों, रसायनों और चिकित्सा निदान सहित विभिन्न विषयों के लिए कई उद्योग, सार्वजनिक और अंतर्राष्ट्रीय मानक हैं। यहाँ कुछ मान हैं जो अक्सर प्राकृतिक कुंजियों के रूप में उपयोग किए जाते हैं:

• आईएसओ 3166 देश कोड
• आईएसओ 639 भाषा कोड
• ISO 4217 के अनुसार मुद्रा कोड
• स्टॉक प्रतीक आईएसआईएन
• यूपीसी/ईएएन, वीआईएन, जीटीआईएन, आईएसबीएन
• लॉगिन नाम
• ईमेल पते
• कमरे के नंबर
• नेटवर्क मैक पता
• अक्षांश, पृथ्वी की सतह पर बिंदुओं के लिए देशांतर

मैं अनुशंसा करता हूं कि जब भी संभव हो और उचित हो, कुंजी घोषित करें, शायद प्रति तालिका में कई कुंजियां भी। लेकिन याद रखें कि उपरोक्त सभी के अपवाद हो सकते हैं।

• सभी के पास ईमेल पता नहीं होता है, हालांकि यह कुछ डेटाबेस शर्तों के तहत स्वीकार्य हो सकता है। साथ ही, लोग समय-समय पर अपने ईमेल पते बदलते रहते हैं। (बाद में मुख्य स्थिरता पर अधिक।)
• ISIN स्टॉक प्रतीक समय-समय पर बदलते रहते हैं, उदाहरण के लिए GOOG और GOOGL प्रतीक Google से वर्णमाला में कंपनी के पुनर्गठन का सटीक वर्णन नहीं करते हैं। कभी-कभी भ्रम पैदा हो सकता है, जैसा कि TWTR और TWTRQ के मामले में, कुछ निवेशकों ने गलती से Twitter IPO के दौरान बाद वाले को खरीद लिया।
• सामाजिक सुरक्षा नंबर केवल अमेरिकी नागरिकों द्वारा उपयोग किए जाते हैं, गोपनीयता प्रतिबंध हैं, और मृत्यु के बाद पुन: उपयोग किए जाते हैं। इसके अलावा, दस्तावेजों की चोरी के बाद लोगों को नए नंबर मिल सकते हैं। अंत में, एक ही नंबर एक व्यक्ति और आयकर पहचानकर्ता दोनों की पहचान कर सकता है।
• ज़िप कोड शहरों के लिए एक खराब विकल्प हैं। कुछ शहरों में एक सामान्य सूचकांक होता है, या इसके विपरीत, एक शहर में कई सूचकांक होते हैं।

5.4 कृत्रिम चाबियां

यह देखते हुए कि कुंजी प्रत्येक पंक्ति में अद्वितीय मान वाला एक स्तंभ है, इसे बनाने का एक तरीका धोखा देना है - आप प्रत्येक पंक्ति में काल्पनिक अद्वितीय मान लिख सकते हैं। ये कृत्रिम कुंजियाँ हैं: आविष्कृत कोड का उपयोग डेटा या वस्तुओं को संदर्भित करने के लिए किया जाता है।

यह बहुत महत्वपूर्ण है कि कोड डेटाबेस से ही उत्पन्न होता है और डेटाबेस के उपयोगकर्ताओं को छोड़कर किसी के लिए अज्ञात है। यह वही है जो कृत्रिम कुंजियों को मानकीकृत प्राकृतिक कुंजियों से अलग करता है।

जबकि प्राकृतिक कुंजियों को तालिका में डुप्लिकेट या असंगत पंक्तियों से बचाने का लाभ होता है, कृत्रिम कुंजियाँ उपयोगी होती हैं क्योंकि वे मनुष्यों या अन्य प्रणालियों के लिए पंक्ति को संदर्भित करना आसान बनाती हैं, और वे लुकअप को गति देती हैं और जुड़ती हैं क्योंकि वे उपयोग नहीं करते हैं स्ट्रिंग (या बहु-स्तंभ) तुलना। कुंजियाँ।

सरोगेट्स

कृत्रिम चाबियों का उपयोग एंकर के रूप में किया जाता है - कोई फर्क नहीं पड़ता कि नियम और कॉलम कैसे बदलते हैं, एक पंक्ति को हमेशा उसी तरह पहचाना जा सकता है। इस उद्देश्य के लिए उपयोग की जाने वाली कृत्रिम कुंजी को "सरोगेट कुंजी" कहा जाता है और इस पर विशेष ध्यान देने की आवश्यकता होती है। हम नीचे सरोगेट्स पर विचार करेंगे।

डेटाबेस के बाहर से पंक्ति को संदर्भित करने के लिए गैर-सरोगेट कृत्रिम कुंजियाँ उपयोगी हैं। एक कृत्रिम कुंजी संक्षेप में एक डेटा या वस्तु की पहचान करती है: इसे एक यूआरएल के रूप में निर्दिष्ट किया जा सकता है, एक चालान से जुड़ा हुआ है, फोन पर लिखा जा सकता है, बैंक से प्राप्त किया जा सकता है, या लाइसेंस प्लेट पर मुद्रित किया जा सकता है। (कार की लाइसेंस प्लेट हमारे लिए एक प्राकृतिक कुंजी है, लेकिन सरकार द्वारा कृत्रिम कुंजी के रूप में डिज़ाइन की गई है।)

टाइपो और त्रुटियों को कम करने के लिए संचरण के संभावित साधनों को ध्यान में रखते हुए सिंथेटिक कुंजियों का चयन किया जाना चाहिए। यह ध्यान दिया जाना चाहिए कि कुंजी को बोला जा सकता है, मुद्रित पढ़ा जा सकता है, एसएमएस के माध्यम से भेजा जा सकता है, हस्तलिखित पढ़ा जा सकता है, कीबोर्ड से टाइप किया जा सकता है और URL में एम्बेड किया जा सकता है। इसके अतिरिक्त, कुछ कृत्रिम कुंजियाँ, जैसे क्रेडिट कार्ड नंबर, में एक चेकसम होता है ताकि यदि कुछ त्रुटियां होती हैं, तो उन्हें कम से कम पहचाना जा सके।

उदाहरण:

• यूएस लाइसेंस प्लेटों के लिए, ओ और 0 जैसे अस्पष्ट वर्णों के उपयोग के बारे में नियम हैं।
• डॉक्टरों की लिखावट को देखते हुए अस्पतालों और फार्मेसियों को विशेष रूप से सावधान रहना चाहिए।
• क्या आप पाठ संदेश द्वारा एक पुष्टिकरण कोड भेजते हैं? जीएसएम 03.38 कैरेक्टर सेट से आगे न जाएं।
• बेस 64 के विपरीत, जो मनमाना बाइट डेटा को एन्कोड करता है, बेस 32 सीमित वर्ण सेट का उपयोग करता है जो मनुष्यों के लिए पुराने कंप्यूटर सिस्टम पर उपयोग करने और संभालने के लिए सुविधाजनक है।
• Proquints पठनीय, लिखने योग्य और उच्चारण योग्य पहचानकर्ता हैं। ये स्पष्ट रूप से समझे जाने वाले व्यंजन और स्वरों के PRO-nounable QUINT-uplets हैं।

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

यहां तक ​​​​कि अगर हम खुद को संख्यात्मक कुंजियों तक सीमित रखते हैं, तो तेरहवीं मंजिल जैसी वर्जनाएं हैं। जबकि प्रोक्विंट्स में प्रति बोले गए शब्दांश की जानकारी का घनत्व अधिक होता है, नंबर कई मायनों में भी ठीक होते हैं: URL, पिन-कीबोर्ड और हस्तलिखित नोट्स में, जब तक प्राप्तकर्ता जानता है कि कुंजी केवल संख्याएँ हैं।

हालाँकि, कृपया ध्यान दें कि आपको सार्वजनिक संख्यात्मक कुंजियों में अनुक्रमिक क्रम का उपयोग नहीं करना चाहिए, क्योंकि यह आपको संसाधनों (/videos/1.mpeg, /videos/2.mpeg, और इसी तरह) के माध्यम से छानबीन करने की अनुमति देता है और नंबर के बारे में जानकारी भी लीक करता है। आंकड़े। संख्याओं के अनुक्रम पर एक Feistel जाल को अध्यारोपित करें और संख्याओं के क्रम को छिपाते हुए अद्वितीयता को बनाए रखें।

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

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

5.5 सरोगेट चाबियां

जैसा कि पहले ही उल्लेख किया गया है, एक महत्वपूर्ण प्रकार की कृत्रिम कुंजी को "सरोगेट कुंजी" कहा जाता है। इसे अन्य कृत्रिम चाबियों की तरह संक्षिप्त और पारगम्य होने की आवश्यकता नहीं है, लेकिन इसका उपयोग एक आंतरिक लेबल के रूप में किया जाता है जो हमेशा स्ट्रिंग की पहचान करता है। यह एसक्यूएल में प्रयोग किया जाता है, लेकिन एप्लिकेशन इसे स्पष्ट रूप से एक्सेस नहीं करता है।

यदि आप PostgreSQL के सिस्टम कॉलम से परिचित हैं, तो आप सरोगेट्स को लगभग एक डेटाबेस कार्यान्वयन पैरामीटर (जैसे ctid) के रूप में सोच सकते हैं, जो, हालांकि, कभी नहीं बदलता है। सरोगेट मान प्रति पंक्ति एक बार चुना जाता है और उसके बाद कभी नहीं बदला जाता है।

सरोगेट कुंजी विदेशी कुंजी के रूप में महान हैं, और ON UPDATE RESTRICTसरोगेट की अपरिवर्तनीयता से मेल खाने के लिए कैस्केडिंग बाधाओं को निर्दिष्ट किया जाना चाहिए।

दूसरी ओर, ON UPDATE CASCADEअधिकतम लचीलापन प्रदान करने के लिए, सार्वजनिक रूप से साझा की गई कुंजियों की विदेशी कुंजियों को चिह्नित किया जाना चाहिए। एक कैस्केडिंग अपडेट आसपास के लेन-देन के समान अलगाव स्तर पर चलता है, इसलिए समवर्ती मुद्दों के बारे में चिंता न करें - यदि आप सख्त अलगाव स्तर चुनते हैं तो डेटाबेस ठीक रहेगा।

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

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

ऑटो-इन्क्रीमेंट INT/BIGINT

सरोगेट कुंजी के लिए सबसे आम उपयोग ऑटो-इंक्रीमेंटिंग "बिगसीरियल" कॉलम है , जिसे पहचान के रूप में भी जाना जाता है । (वास्तव में, PostgreSQL 10 अब पहचान निर्माण का समर्थन करता है, जैसा कि Oracle करता है, CREATE TABLE देखें।)

हालाँकि, मेरा मानना ​​​​है कि सरोगेट कुंजियों के लिए एक ऑटो-इंक्रीमेंटिंग पूर्णांक एक खराब विकल्प है। यह राय अलोकप्रिय है, इसलिए मुझे समझाने दीजिए।

सीरियल कुंजियों के नुकसान:

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

यूयूआईडी

आइए एक और विकल्प देखें: एक यादृच्छिक पैटर्न के अनुसार उत्पन्न बड़े पूर्णांक (128-बिट) का उपयोग करना। इस तरह के सार्वभौमिक अद्वितीय पहचानकर्ता (यूयूआईडी) उत्पन्न करने के लिए एल्गोरिदम में एक ही समय में दो अलग-अलग प्रोसेसर पर चलने पर भी समान मूल्य को दो बार चुनने की बेहद कम संभावना होती है।

उस स्थिति में, यूयूआईडी सरोगेट कुंजी के रूप में उपयोग करने के लिए प्राकृतिक विकल्प की तरह प्रतीत होते हैं, है ना? यदि आप पंक्तियों को एक अनोखे तरीके से लेबल करना चाहते हैं, तो अद्वितीय लेबल से बेहतर कुछ नहीं है!

तो हर कोई PostgreSQL में उनका उपयोग क्यों नहीं कर रहा है? इसके कई कृत्रिम कारण हैं और एक तार्किक कारण जिसके आसपास काम किया जा सकता है, और मैं अपनी बात को स्पष्ट करने के लिए बेंचमार्क प्रस्तुत करूंगा।

सबसे पहले, मैं दूरगामी कारणों के बारे में बात करूँगा। कुछ लोग सोचते हैं कि यूयूआईडी तार हैं क्योंकि वे पारंपरिक हेक्साडेसिमल नोटेशन में डैश के साथ लिखे गए हैं 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f:। दरअसल, कुछ डेटाबेस में एक कॉम्पैक्ट (128-बिट) uuid प्रकार नहीं होता है, लेकिन PostgreSQL करता है और इसका आकार दो होता है bigint, यानी, डेटाबेस में अन्य जानकारी की मात्रा की तुलना में, ओवरहेड नगण्य है।

यूयूआईडी पर भी गलत तरीके से बोझिल होने का आरोप लगाया जाता है, लेकिन उनका उच्चारण कौन करेगा, उन्हें टाइप करेगा, या उन्हें पढ़ेगा? हमने कहा कि यह कृत्रिम कुंजियों को दिखाने के लिए समझ में आता है, लेकिन किसी को (परिभाषा के अनुसार) सरोगेट यूयूआईडी नहीं देखना चाहिए। यह संभव है कि सिस्टम को डीबग करने के लिए psql में SQL कमांड चलाने वाले डेवलपर द्वारा UUID को निपटाया जाएगा, लेकिन यह इसके बारे में है। और डेवलपर अधिक सुविधाजनक कुंजियों का उपयोग करके स्ट्रिंग्स को भी संदर्भित कर सकता है, यदि वे दिए गए हैं।

UUIDs के साथ वास्तविक समस्या यह है कि अत्यधिक यादृच्छिक मान राइट-फॉरवर्ड लॉग (WAL) पर पूर्ण पृष्ठ लिखने के कारण लेखन प्रवर्धन की ओर ले जाते हैं । हालाँकि, प्रदर्शन में गिरावट वास्तव में UUID जनरेशन एल्गोरिथम पर निर्भर करती है।

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

चूंकि PostgreSQL निरंतरता प्रदान करने के लिए अधिकांश ऑपरेटिंग सिस्टम/फाइल सिस्टम/डिस्क कॉन्फ़िगरेशन पर भरोसा नहीं कर सकता है, डेटाबेस बदले हुए डिस्क पेज की पूरी स्थिति को राइट-फॉरवर्ड लॉग में सहेजता है जिसका उपयोग संभावित क्रैश से पुनर्प्राप्त करने के लिए किया जा सकता है। UUIDs जैसे अत्यधिक यादृच्छिक मूल्यों को अनुक्रमित करने में आमतौर पर विभिन्न डिस्क पृष्ठों का एक गुच्छा शामिल होता है और प्रत्येक नई प्रविष्टि के लिए WAL को पूर्ण पृष्ठ आकार (आमतौर पर 4 या 8 KB) लिखा जाता है। यह तथाकथित पूर्ण-पृष्ठ लेखन (पूर्ण-पृष्ठ लेखन, FPW) है।

कुछ UUID पीढ़ी के एल्गोरिदम (जैसे कि Twitter का "स्नोफ्लेक" या पोस्टग्रेएसक्यूएल के uuid-ossp एक्सटेंशन में uuid_generate_v1 ()) प्रत्येक मशीन पर नीरस रूप से बढ़ते मूल्य उत्पन्न करते हैं। यह दृष्टिकोण कम डिस्क पृष्ठों में लिखता है और FPW को कम करता है।

5.6 निष्कर्ष और सिफारिशें

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

प्रत्येक तालिका के लिए:

• परिभाषित करें और सभी प्राकृतिक कुंजियों की घोषणा करें।
• के डिफ़ॉल्ट मान के साथ UUID<table_name>_id प्रकार की सरोगेट कुंजी बनाएँ । आप इसे प्राथमिक कुंजी के रूप में भी चिह्नित कर सकते हैं। यदि आप इस पहचानकर्ता में तालिका का नाम जोड़ते हैं, तो यह सरल हो जाएगा , अर्थात के बदले प्राप्त करें । इस कुंजी को ग्राहकों को न दें और इसे डेटाबेस के बाहर बिल्कुल भी प्रकट न करें।uuid_generate_v1()JOINJOIN foo USING (bar_id)JOIN foo ON (foo.bar_id = bar.id)
• के माध्यम से गुजरने वाली मध्यवर्ती तालिकाओं के लिए JOIN, सभी विदेशी कुंजी कॉलम को एक समग्र प्राथमिक कुंजी के रूप में घोषित करें।
• वैकल्पिक रूप से, एक कृत्रिम कुंजी जोड़ें जिसका उपयोग URL या अन्य स्ट्रिंग संदर्भ संकेतों में किया जा सकता है। स्वत: वृद्धिशील पूर्णांकों को छिपाने के लिए Feistel ग्रिड या pg_hashids का उपयोग करें ।
• ON UPDATE RESTRICTविदेशी कुंजी के रूप में और कृत्रिम विदेशी कुंजी के लिए सरोगेट UUIDs का उपयोग करके एक कैस्केडिंग बाधा निर्दिष्ट करें ON UPDATE CASCADE। अपने स्वयं के तर्क के आधार पर प्राकृतिक कुंजियाँ चुनें।

यह दृष्टिकोण प्राकृतिक चाबियों की अनुमति देने और यहां तक ​​कि सुरक्षा करते समय आंतरिक कुंजियों की स्थिरता सुनिश्चित करता है। इसके अलावा, दृश्यमान कृत्रिम कुंजियाँ किसी भी चीज़ से जुड़ी नहीं होती हैं। सब कुछ सही ढंग से समझने के बाद, आप केवल "प्राथमिक कुंजियों" पर नहीं लटक सकते हैं और कुंजियों का उपयोग करने की सभी संभावनाओं का उपयोग कर सकते हैं।