Wie verhindert man, dass Spalte b den gleichen Wert wie jede Spalte a in Oracle enthält?

Question

Was ist eine gute Methode, um zu verhindern, dass eine Tabelle mit 2 Spalten, a (eindeutig) und b, einen Datensatz hat, bei dem die Spalte b einen Wert hat, der gleich einem Wert in der Spalte a ist? Dies würde für eine Tabelle von Korrekturen wie dieser verwendet werden,

MR   -> Mr
Prf. -> Prof.
MRs  -> Mrs

Ich kann sehen, wie es mit einem Trigger und einer Unterabfrage gemacht werden könnte, vorausgesetzt, es gibt keine gleichzeitige Aktivität, aber ein deklarativerer Ansatz wäre vorzuziehen.

Dies ist ein Beispiel dafür, was verhindert werden sollte,

Wing Commdr. -> Wing Cdr.
Wing Cdr.    -> Wing Commander

Im Idealfall würde die Lösung mit gleichzeitigen Einfügungen und Aktualisierungen funktionieren.

Answer 1

Sie könnten eine materialisierte Ansicht verwenden, um Ihre Anforderungen durchzusetzen (getestet mit 10.2.0.1).

SQL> CREATE TABLE t (a VARCHAR2(20) NOT NULL PRIMARY KEY,
  2                  b VARCHAR2(20) NOT NULL);
Tabelle erstellt

SQL> CREATE MATERIALIZED VIEW LOG ON t WITH (b), ROWID INCLUDING NEW VALUES;     
Materialisierte Ansichtsprotokoll erstellt

SQL> CREATE MATERIALIZED VIEW mv
  2     REFRESH FAST ON COMMIT
  3  AS
  4  SELECT 1 umarker, COUNT(*) c, count(a) cc, a val_col
  5    FROM t
  6   GROUP BY a
  7  UNION ALL
  8  SELECT 2 umarker, COUNT(*), COUNT(b), b
  9    FROM t
 10    GROUP BY b;     
Materialisierte Ansicht erstellt

SQL> CREATE UNIQUE INDEX idx ON mv (val_col);     
Index erstellt 

Der eindeutige Index stellt sicher, dass Sie nicht den gleichen Wert in beiden Spalten (auf zwei Zeilen) haben können.

SQL> INSERT INTO t VALUES ('Wing Commdr.', 'Wing Cdr.');     
1 Zeile eingefügt

SQL> COMMIT;     
Commit abgeschlossen

SQL> INSERT INTO t VALUES ('Wing Cdr.', 'Wing Commander');     
1 Zeile eingefügt

SQL> COMMIT;     

ORA-12008: Fehler im Pfad zur Regeneration der materialisierten Ansicht
ORA-00001: Eindeutigkeitsbeschränkungsverletzung (VNZ.IDX)

SQL> INSERT INTO t VALUES ('X', 'Wing Commdr.');     
1 Zeile eingefügt

SQL> COMMIT;

ORA-12008: Fehler im Pfad zur Regeneration der materialisierten Ansicht
ORA-00001: Eindeutigkeitsbeschränkungsverletzung (VNZ.IDX)

Es wird während des Commit-Vorgangs serialisiert, aber nur auf die Werte der Spalten A und B (d.h. im Allgemeinen sollte dies keine gleichzeitige getrennte Aktivität verhindern).

Die Einzigartigkeit wird nur beim COMMIT-Vorgang überprüft, und einige Tools erwarten nicht, dass der Commit fehlschlägt, und können sich möglicherweise unangemessen verhalten. Außerdem, wenn der COMMIT fehlschlägt, wird die gesamte Transaktion zurückgerollt und Sie verlieren alle nicht bestätigten Änderungen (Sie können nicht "erneut versuchen").

Answer 2

Betrachten Sie die Sitzung A, die ('A', 'B') einfügt, aber nicht bestätigt, dann fügt die Sitzung B ('B','A') ein, ohne zu bestätigen. Keine der Sitzungen kann den von der anderen eingefügten Datensatz sehen. Dann bestätigen die Sitzungen.

Sie können serialisieren, indem Sie die gesamte Tabelle sperren, wenn eine Sitzung einfügt (BEFORE INSERT Trigger) und den Check in einem AFTER INSERT Trigger durchführen. Wenn die Tabelle den von Ihnen angegebenen Inhalt hat, sollte sie nicht viele Aktivitäten sehen, sodass die Serialisierung kein Problem darstellen sollte.