PostgreSQL: ON DELETE RESTRICT, CASCADE या SET NULL का चयन करते समय संबंधित रिकॉर्ड खोने से बचें

विलोपन नीति को डेटा प्रतिधारण निर्णय के रूप में परिभाषित करें

हर विदेशी कुंजी एक व्यावसायिक प्रश्न का अप्रत्यक्ष उत्तर रखती है: जब संदर्भित पंक्ति गायब हो जाती है, तो उन पंक्तियों का क्या होना चाहिए जो उसकी ओर इशारा करती हैं? ON DELETE RESTRICT यह उत्तर देता है कि आश्रित पंक्तियों को जीवित रहना चाहिए और जनक को तब तक नहीं हटाया जा सकता जब तक उन आश्रित पंक्तियों को स्पष्ट रूप से संभाला नहीं जाता। ON DELETE CASCADE यह उत्तर देता है कि आश्रित पंक्तियाँ जनक का अभिन्न अंग हैं और उन्हें एक साथ गायब हो जाना चाहिए। ON DELETE SET NULL यह उत्तर देता है कि संदर्भ वैकल्पिक है और आश्रित पंक्ति उसके बिना भी अस्तित्व में रह सकती है। ये परस्पर विनिमेय सिंटैक्स विकल्प नहीं हैं; ये प्रतिधारण नियमों को एन्कोड करते हैं जो विलोपन के बाद कौन सा डेटा क्वेरी करने योग्य रहेगा, इसे प्रभावित करते हैं। PostgreSQL दस्तावेज़ बताता है कि ये क्रियाएं सहज विकल्पों को दर्शाती हैं: संदर्भित उत्पाद को हटाने की अनुमति न दें, ऑर्डर को भी हटा दें, या कुछ और करें। गलत विकल्प चुनने से वे पंक्तियाँ चुपचाप हटाई जा सकती हैं जिनकी दूसरे संबंध द्वारा आवश्यकता होती है, या यह वैध सफाई को अवरुद्ध कर सकता है क्योंकि आश्रित पंक्तियाँ जीवित रहने के लिए अभिप्रेत नहीं थीं। यह निर्णय संबंध के अर्थ का पालन करना चाहिए, न कि स्कीमा लेखक की सुविधा का। ऐतिहासिक ऑर्डर द्वारा संदर्भित उत्पाद कैटलॉग प्रविष्टि उसी प्रकार की नहीं है जैसे एक ऑर्डर-आइटम लाइन जो केवल इसलिए मौजूद है क्योंकि ऑर्डर मौजूद है।

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric);
CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text);
CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
);

इसे बदलने से पहले डिफ़ॉल्ट NO ACTION व्यवहार को समझें

यदि आप बिना ON DELETE क्लॉज़ के विदेशी कुंजी लिखते हैं, तो PostgreSQL ON DELETE NO ACTION लागू करता है। दस्तावेज़ स्पष्ट करता है कि इसका अर्थ है कि संदर्भित तालिका में विलोपन आगे बढ़ने की अनुमति है, लेकिन विदेशी कुंजी बाधा को अभी भी संतुष्ट किया जाना आवश्यक है, इसलिए ऑपरेशन आमतौर पर एक त्रुटि का कारण बनेगा। RESTRICT से मुख्य अंतर समय और टालने की क्षमता (deferrability) है। NO ACTION स्टेटमेंट के अंत में या यदि बाधा टालने योग्य है तो लेनदेन के अंत में बाधा की जाँच करता है, जिससे अन्य कमांड को जाँच चलने से पहले स्थिति ठीक करने का अवसर मिलता है। RESTRICT तुरंत जाँच करता है और इसे नहीं टाला जा सकता। अप्रत्यक्ष डिफ़ॉल्ट पर निर्भर रहना भंगुर है क्योंकि स्कीमा के पाठक यह नहीं बता सकते कि क्या लेखक ने एक नीति का इरादा किया था या बस एक निर्दिष्ट करना भूल गए थे। क्रिया को स्पष्ट बनाना, भले ही वह NO ACTION हो, निर्णय को दस्तावेज़ करता है और कोड समीक्षा या माइग्रेशन के दौरान अस्पष्टता को हटाता है।

-- These two are NOT equivalent:
-- product_no integer REFERENCES products  -- defaults to NO ACTION, deferrable
-- product_no integer REFERENCES products ON DELETE RESTRICT  -- immediate, non-deferrable

जब संदर्भित पंक्तियों को जीवित रहना चाहिए तो RESTRICT चुनें

ON DELETE RESTRICT का उपयोग करें जब आश्रित पंक्तियों के मौजूद होने पर जनक पंक्ति को हटाना पूरी तरह असफल होना चाहिए। यह एप्लिकेशन या ऑपरेटर को जनक को हटाने से पहले आश्रित पंक्तियों के बारे में एक स्पष्ट निर्णय लेने के लिए मजबूर करता है। PostgreSQL दस्तावेज़ इसे उत्पाद और ऑर्डर आइटम उदाहरण के साथ दर्शाता है: उत्पाद और ऑर्डर अलग चीजें हैं, इसलिए किसी उत्पाद के विलोपन से स्वतः ही कुछ ऑर्डर आइटम हट जाने को समस्याग्रस्त माना जा सकता है। RESTRICT मास्टर डेटा, संदर्भ तालिकाओं और किसी भी इकाई के लिए सही डिफ़ॉल्ट है जिसके हटाने से मानवीय समीक्षा की आवश्यकता वाले निम्नलिखित परिणाम होते हैं। RESTRICT से मिलने वाली त्रुटि संदेश निश्चित और तत्काल होता है, जिससे इसे एप्लिकेशन कोड में पकड़ना और उपयोगकर्ता को एक सार्थक संदेश प्रस्तुत करना आसान हो जाता है।

-- Attempting this with RESTRICT on product_no will raise an error:
DELETE FROM products WHERE product_no = 7;
-- ERROR: update or delete on table "products" violates
--        foreign key constraint on table "order_items"

केवल आश्रित जीवन-चक्र पंक्तियों के लिए CASCADE चुनें

ON DELETE CASCADE तब उपयुक्त है जब बाल पंक्तियाँ केवल जनक के जीवन-चक्र का हिस्सा हों और उनका कोई स्वतंत्र अर्थ न हो। दस्तावेज़ नोट करता है कि ऑर्डर आइटम ऑर्डर का हिस्सा हैं, और यदि ऑर्डर हटा दिया जाए तो उन्हें स्वतः हटा देना सुविधाजनक है। कैस्केड एक ही ऑपरेशन में संदर्भित पंक्ति से हर संदर्भित करने वाली पंक्ति तक यात्रा करता है। खतरा यह है कि CASCADE उस डेटा को हटा सकता है जिसे एक अलग संबंध द्वारा सुरक्षित रखने की आवश्यकता होती है। यदि एक order_item पंक्ति शिपिंग मैनिफेस्ट या ऑडिट लॉग द्वारा भी संदर्भित है, तो ऑर्डर से विलोपन को कैस्केड करने से order_item हट जाएगा और फिर उन अन्य विदेशी कुंजियों के साथ आगे बढ़ते हुए बाधा का उल्लंघन होगा या आगे कैस्केड होगा। CASCADE का उपयोग केवल तभी करें जब आपने हर डाउनस्ट्रीम संदर्भ को ट्रैस कर लिया हो और पुष्टि कर दी हो कि सभी के लिए स्वतः हटाना अभिप्रेत व्यवहार है।

-- Deleting an order cascades to its line items:
DELETE FROM orders WHERE order_id = 42;
-- All rows in order_items with order_id = 42 are removed automatically.

वैकल्पिक संदर्भों के लिए केवल SET NULL का चयन करें

ON DELETE SET NULL संदर्भित पंक्ति को बनाए रखता है लेकिन विदेशी कुंजी कॉलम को NULL पर सेट कर देता है। यह तब उपयुक्त होता है जब संबंध वैकल्पिक जानकारी का प्रतिनिधित्व करता है। दस्तावेज़ एक उत्पाद प्रबंधक संदर्भ का उदाहरण देता है: यदि उत्पाद प्रबंधक प्रविष्टि हटा दी जाती है, तो उत्पाद के उत्पाद प्रबंधक को null पर सेट करना उपयोगी हो सकता है।

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

-- Column-list form for composite foreign keys:
CREATE TABLE posts (
  tenant_id integer REFERENCES tenants ON DELETE CASCADE,
  post_id integer NOT NULL,
  author_id integer,
  PRIMARY KEY (tenant_id, post_id),
  FOREIGN KEY (tenant_id, author_id) REFERENCES users
    ON DELETE SET NULL (author_id)
);
-- Without the column list, tenant_id would also be set to NULL,
-- violating the primary key.

एक छोटी स्कीमा पर तीन कार्यों की तुलना करें

दस्तावेज़ से products, orders और order_items स्कीमा पर विचार करें। order_items टेबल में दो विदेशी कुंजियाँ हैं जिनके अलग-अलग कार्य हैं: product_no RESTRICT का उपयोग करता है और order_id CASCADE का उपयोग करता है। यदि आप DELETE FROM products WHERE product_no = 7 का प्रयास करते हैं और वह उत्पाद किसी भी order_items पंक्ति में दिखाई देता है, तो कथन एक विदेशी कुंजी उल्लंघन त्रुटि के साथ तुरंत विफल हो जाता है। दोनों टेबलों से कोई भी पंक्ति हटाई नहीं जाती है।

यदि आप DELETE FROM orders WHERE order_id = 42 का प्रयास करते हैं, तो CASCADE कार्य उस ऑर्डर को संदर्भित करने वाली हर order_items पंक्ति को हटा देता है, और डिलीट सफल हो जाता है। यही स्कीमा दर्शाता है कि किस पैरेंट को लक्ष्य बनाने के आधार पर सुरक्षात्मक और स्वचालित व्यवहार दोनों होते हैं।

SET NULL लागू होगा यदि, उदाहरण के लिए, orders में एक वैकल्पिक assigned_shipper कॉलम होता जो shipper टेबल को संदर्भित करता है। शिपर को हटाने से ऑर्डर अक्षुण्ण रहेगा जिसमें assigned_shipper NULL पर सेट होगा, ऑर्डर इतिहास को संरक्षित करते हुए अब अमान्य संदर्भ को हटा देगा।

-- Outcome table for the example schema:
-- DELETE FROM products WHERE product_no = 7
--   -> ERROR (RESTRICT blocks it, dependents survive)
-- DELETE FROM orders WHERE order_id = 42
--   -> SUCCESS (CASCADE removes matching order_items)
-- DELETE FROM shippers WHERE shipper_id = 3
--   -> SUCCESS (SET NULL clears orders.assigned_shipper)

वास्तविक डेटा पर नीति लागू करने से पहले इसकी पुष्टि करें

उत्पादन बाधा को बदलने से पहले, pg_constraint के खिलाफ एक क्वेरी द्वारा या psql में \d के साथ स्कीमा पढ़कर वर्तमान कार्य का निरीक्षण करें। उन निर्भर पंक्तियों की पहचान करें जो आपके हटाने की योजना बनाए गए पैरेंट पंक्तियों के लिए मौजूद हैं। दस्तावेज़ चेतावनी देता है कि WHERE क्लॉज के बिना DELETE FROM products लिखने से सभी पंक्तियाँ हट जाती हैं, इसलिए हमेशा अपने परीक्षण डिलीट्स को सीमित करें।

इच्छित DELETE को एक लेनदेन के भीतर चलाएं जिसे आप रोल बैक करते हैं। यह आपको देखने की अनुमति देता है कि क्या RESTRICT एक त्रुटि उत्पन्न करता है, क्या CASCADE अपेक्षित संख्या में बच्चों को हटाता है, या क्या SET NULL पंक्तियों को NULL संदर्भों के साथ छोड़ देता है, बिना परिवर्तनों को कमिट किए। व्यवहार की पुष्टि करने के बाद ही आपको उत्पादन में बाधा कार्य बदलने के लिए ALTER TABLE जारी करना चाहिए।

BEGIN;
DELETE FROM orders WHERE order_id = 42;
SELECT count(*) FROM order_items WHERE order_id = 42;
-- Expect 0 if CASCADE is correct.
ROLLBACK;
-- No data was permanently removed.

जानें कि नीति अपर्याप्त कब होती है

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

दस्तावेज़ यह भी नोट करता है कि ON UPDATE में संबंधित लेकिन समान व्यवहार नहीं होता है, और कि ON UPDATE के तहत SET NULL और SET DEFAULT के लिए कॉलम सूचियाँ निर्दिष्ट नहीं की जा सकतीं। डिलीट्स के लिए काम करने वाली नीति अपडेट्स पर सीधे स्थानांतरित नहीं हो सकती। अंत में, CASCADE के साथ एक अनबाउंडेड DELETE कथन एक ही लेनदेन में बड़ी मात्रा में डेटा हटा सकता है, इसलिए एप्लिकेशन-स्तर की सुरक्षा और स्पष्ट WHERE क्लॉज बाधा कार्य की परवाह किए बिना आवश्यक बने रहते हैं।

-- This single statement could cascade through many tables:
DELETE FROM products;  -- no WHERE clause, caveat programmer
-- If CASCADE is set on multiple levels, this can be destructive.