Bir makale kimliğinin iki başvuru sütunundan herhangi birinde birden fazla kez görünmesini önlemek için her iki rolü de tek bir benzersiz makale kimliği içeren bir başvuru tablosuna taşıyın. Her satırda tam olarak bir sütunun dolu olmasına izin veriliyorsa benzersiz bir ifade indeksi daha basit bir seçenektir.

Bu eğitim, PostgreSQL ve node-postgres ile macOS Terminal ortamında çalışan geliştiricilere yöneliktir. Amaç, makale başvuruları için ortak bir havuz oluşturmaktır: Bir makaleye hangi rolde olursa olsun bir kez başvurulduktan sonra, aynı makaleye yeniden başvurulması başarısız olmalıdır. Örnek satırların tümü sentetiktir.

Her iki sütunda tekillik ne anlama gelir?

Her iki sütunun da aynı makale kataloğuna başvurduğunu varsayın. İstenen kural, her boş olmayan makale kimliğinin bir satırdaki iki konum da dahil olmak üzere tüm başvuru konumlarında en fazla bir kez yer almasıdır. Bu, her sütunu ayrı ayrı benzersiz kılmaktan daha güçlü bir kuraldır.

Paket Birincil makale İkincil makale İstenen kurala göre sonuç
1 101 102 Kabul et
2 103 101 Reddet: 101, paket 1'de zaten yer alıyor
3 104 104 Reddet: aynı makale her iki konumda da yer alıyor

Bileşik bir UNIQUE (primary_article_id, secondary_article_id) eşleşimi benzersiz kılar. Ayrı benzersiz kısıtlamalar her sütunu kendi içinde benzersiz kılar. Bu tasarımların hiçbiri paket 1 ile 2 arasındaki sütunlar arası tekrarı reddetmez. PostgreSQL, çok sütunlu bir benzersiz indeksin dizine alınan tüm değerler eşleştiğinde yinelenenleri reddettiğini belgeler. Benzersiz İndeksler

Ekleme sorgusunu yazmadan önce izin verilen satır biçimine göre tasarımı seçin:

Gerekli satır biçimi Uygun tasarım
Her iki başvuru da dolu olabilir Başvuruları satırlar olarak saklayın; article_id değerini benzersiz kılın
Tam olarak bir başvuru dolu olmalı Satır denetimiyle tam olarak bir başvuruyu zorunlu kılın; COALESCE değerini benzersiz biçimde dizine alın
Sıfır veya bir başvuru dolu olabilir En fazla bir başvuru denetimini ve aynı ifade indeksini kullanın
Sütunlar farklı kataloglara başvuruyor Önce eşit sayısal kimliklerin aynı şeyi ifade edip etmediğini belirleyin; bu eğitim tek bir ortak katalog varsayar

Her iki başvuruya da izin verildiğinde kuralı nasıl uygularsınız?

Her başvuruyu paket, rol ve makale kimliği içeren bir satır olarak gösterin. Rol, özgün sütunun anlamını korur. Böylece article_id üzerindeki tek bir benzersiz kısıtlama her iki rolü de kapsar.

Aşağıdaki şema standart PostgreSQL kısıtlamalarını bir araya getirir: yabancı anahtarlar eşleşen katalog satırlarının bulunmasını zorunlu kılar, bileşik birincil anahtar paket ve yuva başına tek başvuruya izin verir, adlandırılmış benzersiz kısıtlama ise her makaleye yalnızca bir kez başvurulmasını sağlar. Bu kısıtlama mekanizmaları Kısıtlamalar sayfasında belgelenmiştir.

Tekrarlanabilir bir örnek oluşturun

PostgreSQL 14–18 ve kullanılabilir bir psql komutu bulunan boş bir yerel örnek veritabanı kullanın. Aşağıdaki eksiksiz kurulumu schema.sql olarak kaydedin. Kurulum standart, bölümlenmemiş tablolar kullanır ve uzantı gerektirmez.

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 içinde PGDATABASE değişkenini mevcut örnek veritabanının adı olarak ayarlayın, ardından dosyayı çalıştırın. Komut, yerel bağlantı ayarlarınızın zaten çalıştığını varsayar.

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

Bu tasarım, bir pakette sıfır, bir veya iki başvuru bulunmasına izin verir. Tekilliği ve en fazla iki rol olmasını zorunlu kılar; her pakette iki rolün de bulunmasını zorunlu kılmaz. Bu ayrı bir gereksinimdir. Eksik bir başvuru, satır bulunmamasıyla gösterilir.

Okuma sırasında iki sütunlu biçimi koruyun

Uygulamalar yine iki başvuru alanı içeren, paket başına tek bir satır alabilir. Kurulumdan sonra şu sorguyu çalıştırın:

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;

İlk sonuç, 101 ve 102 başvurularına sahip paket 1'dir. Şemanın birincil anahtarı, her birleştirmenin yuvası için en fazla bir başvuru bulmasını sağlar. Başvuru değerlerinin ikinci bir kopyasını tutmak yerine bu sorguyu uygulamanın okuma biçimi olarak kullanın.

node-postgres ile her iki başvuruyu nasıl eklersiniz?

Paketi ve başvurularını tek bir işlemde oluşturun. Herhangi bir başvuru çakışırsa paketi de geri alın. node-postgres, işlemdeki her ifadenin aynı havuzdan alınmış istemciyi kullanmasını gerektirir; bu işlem sırası için ayrı pool.query() çağrıları uygun değildir. İşlemler

Yerel proje dizininizde belgelenen pg paketini yükleyin. Güncel bir Node.js LTS sürümünü kullanın ve aşağıdaki programı demo.mjs olarak kaydedin. SQL kurulumundan sonra, aynı bağlantı ortamıyla çalıştırın. node-postgres hem kurulumu hem de ortam değişkenleriyle bağlantıları belgeler.

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();
}

Program, node-postgres Sorgular API'sini izleyerek değerleri numaralı parametreler ve ayrı bir değerler dizisi üzerinden iletir. İlk çalıştırmada beklenen çıktı şöyledir:

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

İkinci işlem 101 numaralı makaleyi yeniden kullanmayı dener. İşlemin geri alınması paket 3'ü kaldırır ve 105 numaralı makaleye başvurulmamış olarak bırakır. Yinelenen başvuru mesajı yalnızca adı belirtilen tekillik çakışması için gösterilir; diğer hatalar üst katmana iletilir. PostgreSQL tekillik ihlalini SQLSTATE 23505 ile tanımlar ve kısıtlama adlarını ileti metninden ayrı olarak sağlar. PostgreSQL Hata Kodları

Sonucu incelemek için önceki okuma sorgusunu yeniden çalıştırın: 101–104 başvurularıyla birlikte paket 1 ve 2 kalmalıdır. Bu örneği yalnızca yeni kurulmuş bir ortamda bir kez çalıştırın: Programı yeniden çalıştırmak, paket 2'nin mevcut birincil anahtarıyla karşılaşır.

COALESCE üzerinde benzersiz indeks ne zaman yeterlidir?

Bu daha küçük tasarımı yalnızca bir satırda en fazla bir gerçek başvuru bulunduğunda kullanın. COALESCE boş olmayan ilk bağımsız değişkenini döndürür; bu nedenle ilk sütun doluysa ikinci sütun dizine alınan ifadede görünmez. PostgreSQL ayrıca bağımsız değişkenlerin ortak bir türde çözümlenmesini gerektirir. Koşullu İfadeler

Örneğin, (101, 102) ve (103, 101) satırları 101 ve 103 ifade değerlerini üretir. Bu ifade değerleri üzerinde oluşturulan benzersiz bir indeks, 101 numaralı makaleye iki kez başvurulmasına rağmen her iki satırı da kabul eder. Bu karşı örnek, ifade için neden satır biçimi kısıtlaması gerektiğini açıklar.

Tam olarak bir başvuru kuralı için daha önce oluşturulan articles kataloğundan sonra aşağıdaki alternatif örneği çalıştırın. Ayrı tablosu, bundle_refs tablosuna başvuru eklemek yerine alternatif bir şema gösterir.

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;

Denetim, iki boş olmayan Boolean sonucu karşılaştırır ve yalnızca bir sütun boş, diğeri dolu olduğunda kabul eder. PostgreSQL aynı satırdaki birden fazla sütunu içeren denetimleri destekler. PostgreSQL 14 Kısıtlamaları

Benzersiz ifade indeksi, hangi sütun doluysa onun değerini tek bir karşılaştırma alanına taşır. İfade indeksleri hem güncel PostgreSQL hem de PostgreSQL 14 belgelerinde açıklanmıştır; bu yaklaşım PostgreSQL 15'in ek null işleme söz dizimini gerektirmez.

101 numaralı makaleye zaten başvurulduğu için ikinci ekleme hiçbir satır döndürmez. Bir ifade ON CONFLICT hedefi olabilir ve RETURNING gerçekten eklenen veya güncellenen satırları bildirir. Boş sonucu atlanan ekleme olarak değerlendirin. İfade indeksinin adı, ON CONFLICT ON CONSTRAINT için bir tablo kısıtlaması adı değildir. INSERT

Her iki sütunun da boş olmasına izin veriliyorsa tabloyu tanımlarken tam olarak bir koşulu yerine aşağıdaki satır düzeyi koşuluyla değiştirin. İfade indeksini koruyun. Böylece PostgreSQL'in varsayılan benzersiz indeks null davranışında birden fazla tamamen boş satıra izin verilir. Benzersiz İndeksler

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

Mevcut bir tabloyu değiştirmeden önce yinelenenleri nasıl bulursunuz?

İki sütunu UNION ALL ile tek bir akışta birleştirin, ardından makale kimliğine göre gruplayın. Yinelenenleri korumak önemlidir: Sıradan UNION, bulmanız gereken tekrara ilişkin kanıtı ortadan kaldırabilir. Bu tam tanılama, sentetik girdi kullanır ve yukarıdaki şemalardan bağımsız olarak çalışır.

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;

Beklenen yinelenen kimlikler 101 ve 104'tür; her biri iki kez geçer. Mevcut bir tablo için ilk CTE'yi tablonun gerçek sütunlarıyla değiştirin. Geçişten önce bildirilen tüm tekrarları inceleyin; satırları otomatik olarak kaldırmak amaçlanan başvuruları silebilir. Tüm başvuruları seçilen gösterime taşıyın ve sonraki yazma işlemlerini buraya yönlendirin; örnekteki ayrı alternatif tablolar tek bir tekillik kuralını paylaşmaz.

Son kararı neden veritabanı uygulamalıdır?

Ön sorgu mevcut bir çakışmayı açıklayabilir, ancak tek başına tekillik garantisi sağlayamaz. İki eşzamanlı işlem, herhangi biri ekleme yapmadan önce aynı başvurunun kullanılmadığını görebilir. PostgreSQL'in tekillik uygulaması, indeks eklemesinin bir parçası olarak çakışmaları denetler, gerektiğinde çakışan ve henüz kesinleştirilmemiş işlemleri bekler, ardından sonucu yeniden denetler. İndeks Tekillik Denetimleri

Bu mekanizmanın yerine diğer satırları sorgulayan bir CHECK koymayın. PostgreSQL, sürekli sürdürülen bir garanti olarak satırlar arası denetimleri açıkça desteklemez. İzin verilen sütun biçimi için satır denetimlerini, yinelenen başvurular içinse benzersiz kısıtlamaları veya indeksleri kullanın. Kısıtlamalar

JavaScript programı söz dizimi denetiminden geçti; karşı örnek ve tam olarak bir koşulunun doğruluk tablosu belirleyici biçimde denetlendi. PostgreSQL örnekleri belgelere göre incelendi, ancak bu araştırma ortamında bir veritabanında çalıştırılmadı.

İki dolu başvuru konumu için başvuru tablosu tasarımı, kuralı tek bir benzersiz makale kimliğiyle doğrudan uygulanabilir kılar. Sütunlar arasında yalnızca birinin dolu olmasına izin veriliyorsa denetimli COALESCE tasarımı daha küçük şemayı korur. İzin verilen satır biçimine göre seçim yapın, ardından son tekillik kararını veritabanına bırakın.

Kaynaklar