Ich bin heute auf folgendes Problem gestoßen:
In einer ID-Spalte sollte ein Foreign Key Constraint angelegt werden. Dabei wurde festgestellt, dass die IDs aus zwei verschiedenen Tabellen kommen, z.B. Tabellen author und administrator.
Einige werden bestimmt gleich aufschreien: „Wozu zwei Tabellen, wenn Sie sich nur in Ihrer Rolle unterscheiden?“ Die kurze Antwort darauf: „Sie ist bereits seit einigen Jahren da und die ganze Software arbeitet damit schon immer.“ Abgesehen von der Ursache gilt es das Problem zu lösen.
CREATE TABLE author(id BIGINT, name TEXT);
CREATE TABLE administrator(id BIGINT, description TEXT);
INSERT INTO author VALUES (100, 'Rainer');
INSERT INTO adminstrator VALUES (1, '...');
CREATE TABLE tableA (id INT, user_id, description TEXT);
1. Möglichkeit: Inheritance
Es wird eine Tabelle user erstellt. Diese Tabelle dient als Elterntabelle für administrator und author.
CREATE TABLE user(id BIGINT PRIMARY KEY);
ALTER TABLE author INHERIT user;
ALTER TABLE administrator INHERIT user;
-- liefert alle user IDs
SELECT * FROM user;
ALTER TABLE tableA
ADD CONSTRAINT "fk_tableA#user_id2user#id"
FOREIGN KEY (user_id) REFERENCES user(id);
Schön gedacht – aber funktioniert nicht. Der Grund ist in der PostgreSQL Dokumentation beschrieben.
ERROR: insert or update on table „tableA“ violates foreign key constraint „fk_tableA#user_id2user#id“
DETAIL: Key (user_id)=(100) is not present in table „user“.
2. Möglichkeit: Selbstdefinierte Trigger
Die Idee ist einfach: In einem Trigger wird geprüft, ob die user_id vorhanden ist oder nicht. Beim Fehler der user_id mit einer RAISE EXCEPTION abgebrochen.
CREATE OR REPLACE FUNCTION tr_user_check()
RETURNS TRIGGER AS
$BODY$
BEGIN
IF ((TG_OP = 'INSERT') OR (TG_OP = 'UPDATE')) THEN
IF NOT EXISTS (SELECT id FROM user WHERE id = NEW.user_id) THEN
RAISE EXCEPTION 'user_id=% NOT FOUND in user (administrator, author)', NEW.user_id;
END IF;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER trIU_tableA_user_id_check
BEFORE INSERT OR UPDATE
ON tableA
FOR EACH ROW
EXECUTE PROCEDURE tr_user_check();
INSERT INTO tableA (user_id) VALUES (113); -- error user not exists
INSERT INTO tableA (user_id) VALUES (114); -- yes user exists
Auf ähnliche Weise noch ein DELETE/UPDATE-Trigger auf die Tabellen author, administrator sowie user definiert, der das Ändern der IDs verhindert (Constraint attribute ON UPDATE NO ACTION) oder beim Löschen aufräumt (Constraint ON DELETE CASCADE) und schon ist der Constraint-Check selbst implementiert.
3. Möglichkeit: Table Refactoring
Natürlich bleibt auch immer die Möglichkeit. Eine Tabelle user anlegen, die eine Spalte role (mit den Werten: author, adminstrator) besitzt und die Daten aus den Tabellen author und administrator migrieren. Natürlich stellt sich hier die Frage, wie man mit dem Problem umgeht, wenn die Attribute beide Tabellen zu unterschiedlich sind. Aber auch dafür gibt es mehrere Lösungen.
Skript zum Säubern
Wer die Kommandos mal ausgeführt hat, um selber mal rumzuspielen hier die Befehle zum Säubern.
DELETE FROM tableA WHERE user_id IN (111,113);
DROP TRIGGER trIU_tableA_user_id_check ON tableA;
DROP FUNCTION tr_user_check();
ALTER TABLE author NO INHERIT user;
ALTER TABLE administrator NO INHERIT user;
DROP TABLE user;
DROP TABLE author;
DROP TABLE adminstrator;