किसी लेख ID को दो संदर्भ कॉलम में से किसी में एक से अधिक बार आने से रोकने के लिए, दोनों भूमिकाओं को एक संदर्भ तालिका में रखें, जिसमें लेख ID विशिष्ट हो। यदि हर पंक्ति में ठीक एक कॉलम भरा हो सकता है, तो विशिष्ट एक्सप्रेशन इंडेक्स अधिक सरल विकल्प है।

यह ट्यूटोरियल macOS Terminal में PostgreSQL और node-postgres के साथ काम करने वाले डेवलपर के लिए है। लक्ष्य लेख संदर्भों का एक साझा पूल लागू करना है: किसी लेख का एक भूमिका में संदर्भ दिए जाने के बाद, उस लेख का कोई अन्य संदर्भ विफल होना चाहिए। सभी नमूना पंक्तियाँ कृत्रिम हैं।

किसी भी कॉलम में विशिष्टता का क्या अर्थ है?

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

बंडल प्राथमिक लेख द्वितीयक लेख अपेक्षित नियम के तहत परिणाम
1 101 102 स्वीकार करें
2 103 101 अस्वीकार करें: 101 पहले से बंडल 1 में है
3 104 104 अस्वीकार करें: एक ही लेख दोनों स्थानों पर है

एक संयुक्त UNIQUE (primary_article_id, secondary_article_id) संयोजन को विशिष्ट बनाता है। अलग-अलग विशिष्ट बाधाएँ हर कॉलम को स्वतंत्र रूप से विशिष्ट बनाती हैं। इनमें से कोई भी डिज़ाइन बंडल 1 और 2 के बीच अलग-अलग कॉलमों में हुई दोहराव को अस्वीकार नहीं करता। PostgreSQL के दस्तावेज़ों के अनुसार, बहु-कॉलम विशिष्ट इंडेक्स तब डुप्लिकेट अस्वीकार करता है जब सभी इंडेक्स किए गए मान मेल खाते हों। विशिष्ट इंडेक्स

इंसर्ट क्वेरी लिखने से पहले अनुमत पंक्ति संरचना के आधार पर डिज़ाइन चुनें:

आवश्यक पंक्ति संरचना उपयुक्त डिज़ाइन
दोनों संदर्भ भरे हो सकते हैं संदर्भों को पंक्तियों के रूप में रखें; article_id को विशिष्ट बनाएँ
ठीक एक संदर्भ भरा होना चाहिए पंक्ति जाँच से ठीक एक की शर्त लागू करें; COALESCE को विशिष्ट रूप से इंडेक्स करें
शून्य या एक संदर्भ भरा हो सकता है अधिकतम एक की जाँच और वही एक्सप्रेशन इंडेक्स इस्तेमाल करें
कॉलम अलग-अलग कैटलॉग का संदर्भ देते हैं पहले तय करें कि समान संख्यात्मक ID एक ही चीज़ दर्शाती हैं या नहीं; यह ट्यूटोरियल एक साझा कैटलॉग मानता है

दोनों संदर्भों की अनुमति होने पर नियम कैसे लागू करें?

हर संदर्भ को बंडल, भूमिका और लेख ID वाली पंक्ति के रूप में दर्शाएँ। भूमिका मूल कॉलम का अर्थ बनाए रखती है। फिर article_id पर एक विशिष्ट बाधा दोनों भूमिकाओं को कवर करती है।

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

दोहराया जा सकने वाला नमूना बनाएँ

PostgreSQL 14–18 और उपलब्ध psql कमांड वाले किसी खाली स्थानीय उदाहरण डेटाबेस का उपयोग करें। नीचे का पूरा सेटअप schema.sql के रूप में सहेजें। इसमें सामान्य, अनपार्टिशन्ड तालिकाएँ हैं और किसी एक्सटेंशन की आवश्यकता नहीं है।

BEGIN;

CREATE TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TABLE article_bundles (
    bundle_id integer PRIMARY KEY,
    label text NOT NULL
);

CREATE TABLE bundle_refs (
    bundle_id integer NOT NULL
        REFERENCES article_bundles (bundle_id),
    slot text NOT NULL
        CHECK (slot IN ('primary', 'secondary')),
    article_id integer NOT NULL
        REFERENCES articles (article_id),
    PRIMARY KEY (bundle_id, slot),
    CONSTRAINT bundle_refs_article_once UNIQUE (article_id)
);

INSERT INTO articles (article_id, title) VALUES
    (101, 'Grid spacing'),
    (102, 'Type scale'),
    (103, 'Page margins'),
    (104, 'Caption layout'),
    (105, 'Colour palette');

INSERT INTO article_bundles (bundle_id, label)
VALUES (1, 'Opening notes');

INSERT INTO bundle_refs (bundle_id, slot, article_id) VALUES
    (1, 'primary', 101),
    (1, 'secondary', 102);

COMMIT;

macOS Terminal में PGDATABASE को उस मौजूदा उदाहरण डेटाबेस के नाम पर सेट करें, फिर फ़ाइल चलाएँ। यह कमांड मानता है कि आपकी स्थानीय कनेक्शन सेटिंग पहले से काम कर रही हैं।

export PGDATABASE=article_demo
psql -v ON_ERROR_STOP=1 -f schema.sql

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

पढ़ने के लिए दो-कॉलम संरचना बनाए रखें

एप्लिकेशन अब भी हर बंडल के लिए दो संदर्भ फ़ील्ड वाली एक पंक्ति प्राप्त कर सकते हैं। सेटअप के बाद यह क्वेरी चलाएँ:

SELECT b.bundle_id,
       b.label,
       p.article_id AS primary_article_id,
       s.article_id AS secondary_article_id
FROM article_bundles AS b
LEFT JOIN bundle_refs AS p
  ON p.bundle_id = b.bundle_id AND p.slot = 'primary'
LEFT JOIN bundle_refs AS s
  ON s.bundle_id = b.bundle_id AND s.slot = 'secondary'
ORDER BY b.bundle_id;

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

node-postgres दोनों संदर्भ कैसे इंसर्ट करे?

बंडल और उसके संदर्भों को एक ही ट्रांज़ैक्शन में बनाएँ। यदि कोई संदर्भ टकराता है, तो बंडल को भी रोल बैक करें। node-postgres में ट्रांज़ैक्शन के हर कथन के लिए उसी चेक-आउट किए गए क्लाइंट का उपयोग आवश्यक है; pool.query() के अलग-अलग कॉल इस क्रम के लिए उपयुक्त नहीं हैं। ट्रांज़ैक्शन

किसी स्थानीय प्रोजेक्ट डायरेक्टरी में दस्तावेज़ों में दिया गया pg पैकेज इंस्टॉल करें। Node.js की मौजूदा LTS रिलीज़ इस्तेमाल करें और नीचे दिए गए प्रोग्राम को demo.mjs के रूप में सहेजें। इसे SQL सेटअप के बाद, उसी कनेक्शन वातावरण के साथ चलाएँ। node-postgres के दस्तावेज़ों में इंस्टॉलेशन और पर्यावरण-आधारित कनेक्शन, दोनों शामिल हैं।

npm install pg
node demo.mjs

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool();

async function addBundle(bundleId, label, primaryId, secondaryId) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      'INSERT INTO article_bundles (bundle_id, label) VALUES ($1, $2)',
      [bundleId, label]
    );

    await client.query(
      `INSERT INTO bundle_refs (bundle_id, slot, article_id)
       VALUES ($1, 'primary', $2), ($1, 'secondary', $3)`,
      [bundleId, primaryId, secondaryId]
    );

    await client.query('COMMIT');
    console.log(`Added bundle ${bundleId}`);
  } catch (error) {
    await client.query('ROLLBACK');

    if (
      error.code === '23505' &&
      error.constraint === 'bundle_refs_article_once'
    ) {
      console.log(
        `Bundle ${bundleId} rejected: an article is already referenced`
      );
      return;
    }

    throw error;
  } finally {
    client.release();
  }
}

try {
  await addBundle(2, 'Layout notes', 103, 104);
  await addBundle(3, 'Colour notes', 105, 101);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

यह प्रोग्राम मानों को क्रमांकित पैरामीटर और अलग मान-सरणी के ज़रिए भेजता है, जैसा node-postgres के क्वेरीज़ API में बताया गया है। पहली बार चलाने पर अपेक्षित आउटपुट यह है:

Added bundle 2
Bundle 3 rejected: an article is already referenced

दूसरा ट्रांज़ैक्शन लेख 101 को फिर से इस्तेमाल करने की कोशिश करता है। उसका रोलबैक बंडल 3 को हटा देता है और लेख 105 को बिना संदर्भ वाला छोड़ देता है। डुप्लिकेट संदेश केवल विशेष रूप से नामित विशिष्टता टकराव के लिए दिखता है; अन्य विफलताएँ आगे प्रेषित होती हैं। PostgreSQL विशिष्टता उल्लंघन को SQLSTATE 23505 से पहचानता है और बाधा के नाम संदेश के पाठ से अलग देता है। PostgreSQL त्रुटि कोड

परिणाम देखने के लिए पहले वाली रीड क्वेरी फिर चलाएँ: बंडल 1 और 2 बने रहने चाहिए, जिनमें संदर्भ 101–104 हों। इसे हर नए सेटअप पर एक बार चलाए जाने वाला प्रदर्शन मानें: प्रोग्राम दोबारा चलाने पर बंडल 2 की मौजूदा प्राथमिक कुंजी से टकराव होगा।

COALESCE पर विशिष्ट इंडेक्स कब पर्याप्त है?

इस छोटे डिज़ाइन का उपयोग केवल तब करें जब किसी पंक्ति में अधिकतम एक वास्तविक संदर्भ हो। COALESCE अपना पहला गैर-शून्य तर्क लौटाता है, इसलिए यदि पहला कॉलम भरा हो, तो वह दूसरे कॉलम को इंडेक्स किए गए एक्सप्रेशन से छिपा देता है। PostgreSQL यह भी माँगता है कि उसके तर्कों का प्रकार एक समान हो। सशर्त एक्सप्रेशन

उदाहरण के लिए, पंक्तियाँ (101, 102) और (103, 101) एक्सप्रेशन मान 101 और 103 देती हैं। इन एक्सप्रेशन मानों पर बना विशिष्ट इंडेक्स दोनों को स्वीकार कर लेता है, भले ही लेख 101 दो बार आया हो। यह प्रतिउदाहरण बताता है कि एक्सप्रेशन के साथ पंक्ति-संरचना की बाधा क्यों चाहिए।

ठीक एक वाली शर्त के लिए, पहले वाला articles कैटलॉग बनाने के बाद नीचे का वैकल्पिक उदाहरण चलाएँ। इसकी अलग तालिका वैकल्पिक स्कीमा दिखाती है; यह bundle_refs में संदर्भ नहीं जोड़ती।

CREATE TABLE single_article_refs (
    reference_id integer PRIMARY KEY,
    primary_article_id integer REFERENCES articles (article_id),
    secondary_article_id integer REFERENCES articles (article_id),
    CONSTRAINT exactly_one_article CHECK (
        (primary_article_id IS NULL)
        <> (secondary_article_id IS NULL)
    )
);

CREATE UNIQUE INDEX single_article_refs_article_once
ON single_article_refs (
    (COALESCE(primary_article_id, secondary_article_id))
);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (1, 101, NULL);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (2, NULL, 101)
ON CONFLICT (
    (COALESCE(primary_article_id, secondary_article_id))
)
DO NOTHING
RETURNING reference_id;

यह जाँच दो गैर-शून्य बूलियन परिणामों की तुलना करती है और तभी स्वीकार करती है जब एक कॉलम शून्य हो और दूसरा भरा हो। PostgreSQL एक ही पंक्ति के कई कॉलमों से जुड़ी जाँचों का समर्थन करता है। PostgreSQL 14 की बाधाएँ

विशिष्ट एक्सप्रेशन इंडेक्स किसी भी कॉलम के भरे हुए मान को एक ही तुलना क्षेत्र में रखता है। एक्सप्रेशन इंडेक्स का वर्णन मौजूदा PostgreSQL और PostgreSQL 14, दोनों के दस्तावेज़ों में है; इस तरीके के लिए PostgreSQL 15 का अतिरिक्त null-treatment सिंटैक्स आवश्यक नहीं है।

दूसरा इंसर्ट कोई पंक्ति नहीं लौटाता, क्योंकि लेख 101 का संदर्भ पहले से मौजूद है। किसी एक्सप्रेशन को ON CONFLICT लक्ष्य बनाया जा सकता है, और RETURNING वास्तव में इंसर्ट या अपडेट की गई पंक्तियाँ बताता है। खाली परिणाम को छोड़ा गया इंसर्ट मानें। ON CONFLICT ON CONSTRAINT के लिए एक्सप्रेशन इंडेक्स का नाम तालिका बाधा का नाम नहीं होता। INSERT

यदि दोनों कॉलम खाली हो सकते हैं, तो तालिका बनाते समय ठीक एक वाली जाँच को नीचे दी गई पंक्ति-स्तरीय शर्त से बदलें। एक्सप्रेशन इंडेक्स बनाए रखें। PostgreSQL के डिफ़ॉल्ट विशिष्ट-इंडेक्स null व्यवहार के तहत कई पूरी तरह null पंक्तियों की अनुमति होगी। विशिष्ट इंडेक्स

-- Alternative CHECK fragment for the CREATE TABLE definition:
CHECK (
    primary_article_id IS NULL
    OR secondary_article_id IS NULL
)

मौजूदा तालिका बदलने से पहले डुप्लिकेट का पता कैसे लगाएँ?

दोनों कॉलमों को UNION ALL से एक धारा में फैलाएँ, फिर लेख ID के आधार पर समूहित करें। डुप्लिकेट बनाए रखना ज़रूरी है: सामान्य UNION उस दोहराव के सबूत हटा सकता है जिसे आप ढूँढ़ना चाहते हैं। यह पूरा निदान कृत्रिम इनपुट का उपयोग करता है और ऊपर दिए गए स्कीमा से स्वतंत्र रूप से चलता है।

WITH legacy_refs (bundle_id, primary_article_id, secondary_article_id) AS (
    VALUES
        (1, 101, 102),
        (2, 103, 101),
        (3, 104, 104)
),
expanded AS (
    SELECT bundle_id, 'primary' AS slot,
           primary_article_id AS article_id
    FROM legacy_refs
    WHERE primary_article_id IS NOT NULL

    UNION ALL

    SELECT bundle_id, 'secondary' AS slot,
           secondary_article_id AS article_id
    FROM legacy_refs
    WHERE secondary_article_id IS NOT NULL
)
SELECT article_id,
       count(*) AS occurrences,
       string_agg(
           bundle_id::text || ':' || slot,
           ', ' ORDER BY bundle_id, slot
       ) AS locations
FROM expanded
GROUP BY article_id
HAVING count(*) > 1
ORDER BY article_id;

अपेक्षित डुप्लिकेट ID 101 और 104 हैं, जिनमें से हर एक दो बार आता है। किसी मौजूदा तालिका के लिए, पहले CTE को तालिका के वास्तविक कॉलमों से बदलें। माइग्रेशन से पहले बताई गई हर घटना की समीक्षा करें; पंक्तियाँ अपने आप हटाने से इच्छित संदर्भ मिट सकते हैं। सभी संदर्भ चुने गए निरूपण में ले जाएँ और आगे के लेखन को वहीं निर्देशित करें, क्योंकि नमूने की अलग वैकल्पिक तालिकाओं में विशिष्टता लागू करने की व्यवस्था साझा नहीं होती।

डेटाबेस को अंतिम निर्णय क्यों लागू करना चाहिए?

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

इस तंत्र की जगह ऐसा CHECK न लें जो दूसरी पंक्तियों को क्वेरी करता हो। PostgreSQL स्पष्ट रूप से लगातार लागू रहने वाली गारंटी के रूप में पंक्तियों के पार जाँच का समर्थन नहीं करता। अनुमत कॉलम संरचना के लिए पंक्ति जाँचें और दोहराए गए संदर्भों के लिए विशिष्ट बाधाएँ या इंडेक्स इस्तेमाल करें। बाधाएँ

JavaScript प्रोग्राम सिंटैक्स जाँच में सफल रहा, और प्रतिउदाहरण तथा ठीक-एक सत्यता तालिका की नियतात्मक जाँच की गई। PostgreSQL के उदाहरणों की दस्तावेज़ों के आधार पर समीक्षा की गई, लेकिन इस शोध वातावरण में उन्हें डेटाबेस पर चलाया नहीं गया।

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

स्रोत