Delete mit null constraint

Moin,

folgende Situation:
ich habe 2 Tabellen einen mit Standard Werten und eine mit Werten die ein User mit persönlichen Werten füllen kann. Wenn ein neuer Eintrag in eine andere Tabelle gemacht wird muss aus einer der beiden Tabellen ein Wert gewählt werden. Der andere Foreignkey ist dann null. Das klappt auch soweit. Ich habe eine entsprechende Constraint gebaut die eben erfordert das einer der beiden ForeignKeys nicht null sein darf und der andere es sein muss. Jetzt zu meinem Problem, wenn aus der User Tabelle nun ein Eintrag gelöscht wird, sollen alle bisherigen Datensätze unberührt bleiben also keine Cascade ausgelöst werden. Das bekomme ich aber leider nicht hin. Keine Ahnung ob man aus meiner Ausführung schlau werden kann als Fachmann? Ich stelle aber natürlich auch gerne die die SQL Statements zu Verfügung die ich so gebastelt habe wenn das hilft. Ich bräuchte also sowas wie ein softdelete. Alles alte bleibt wie es ist, aber für neue Datensätze steht der Eintrag nicht mehr zur Verfügung

Dann will ich das mal machen

CREATE TABLE IF NOT EXISTS public.user_fish_species (
   id SERIAL PRIMARY KEY,
   user_fish_species_name VARCHAR(30) UNIQUE NOT NULL,
   user_id int,
   created_at TIMESTAMP,
   updated_at TIMESTAMP,
   CONSTRAINT fish_species_users_fk FOREIGN KEY(user_id) REFERENCES users(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS public.user_fishing_baits (
   id SERIAL PRIMARY KEY,
   user_fishing_bait_name VARCHAR(30) NOT NULL,
   user_id int,
   created_at TIMESTAMP,
   updated_at TIMESTAMP,
CONSTRAINT user_fishing_baits_users_fk FOREIGN KEY(user_id) REFERENCES users(id)
	ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS public.catch_book (
   id SERIAL PRIMARY KEY,
   weight NUMERIC(7,2) NOT NULL,
   length NUMERIC(7,2) NOT NULL,
   temperature NUMERIC(7,2),
   air_pressure NUMERIC(7,2),
   catched_at TIMESTAMP without time zone NOT NULL,
   user_id INT, 
   fish_species_id INT,
   user_fish_species_id INT,
   fishing_bait_id INT NULL,
   user_fishing_bait_id INT NULL,
   water_id INT, 
   user_water_id int,
   weather_type_id INT,
   created_at TIMESTAMP,
   updated_at TIMESTAMP,
	CONSTRAINT catch_book_waters_fk FOREIGN KEY(water_id) REFERENCES waters(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT catch_book_user_waters_fk FOREIGN KEY(user_water_id) REFERENCES user_waters(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT catch_book_weather_types_fk FOREIGN KEY(weather_type_id) REFERENCES weather_types(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT catch_book_fish_species_fk FOREIGN KEY(fish_species_id) REFERENCES fish_species(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT catch_book_user_fish_species_fk FOREIGN KEY(user_fish_species_id) REFERENCES user_fish_species(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT catch_book_fishing_baits_fk FOREIGN KEY(fishing_bait_id) REFERENCES fishing_baits(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT catch_book_user_fishing_baits_fk FOREIGN KEY(user_fishing_bait_id) REFERENCES user_fishing_baits(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT catch_book_users_fk FOREIGN KEY(user_id) REFERENCES users(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT catch_book_fishing_bait_all_user_ck CHECK (
		CASE WHEN fishing_bait_id IS NULL THEN 0 ELSE 1 END +
		CASE WHEN user_fishing_bait_id IS NULL THEN 0 ELSE 1 END = 1
	),
	CONSTRAINT catch_book_waters_all_user_ck CHECK (
		CASE WHEN water_id IS NULL THEN 0 ELSE 1 END +
		CASE WHEN user_water_id IS NULL THEN 0 ELSE 1 END = 1
	),
	CONSTRAINT catch_book_fish_species_all_user_ck CHECK (
		CASE WHEN fish_species_id IS NULL THEN 0 ELSE 1 END +
		CASE WHEN user_fish_species_id IS NULL THEN 0 ELSE 1 END = 1
	),
	CONSTRAINT weight_positiv_ck CHECK (weight > 0),
	CONSTRAINT length_positiv_ck CHECK (length > 0)
);

vielleicht noch als Ergänzung. So läuft es bisher, da hatte ich das einfach noch nicht bedacht. Jetzt war mein Ansatz ein Trigger zu setzen, “wenn gelöscht wird, dann setze den anderen ForeignKey auf not null” und alle constraints sind erfüllt. Aber hier verließen Sie mich :slight_smile:

Ich stelle aber natürlich auch gerne die die SQL Statements zu Verfügung die ich so gebastelt habe wenn das hilft.

Da bisher noch keiner geantwortet hat, ist dies wohl das beste. Damit noch eine Chance besteht, das dir geholfen werden kann.

Für mich ist das kein Constraint Problem und es ist nicht untypisch. Nach ein paar Jahren Lebenszeit eines Systems, ändern sich eben einige Dinge. Wer will deswegen seine Altdaten zerlegen.
Wieso setzt Du nicht dort an, wo es problematisch ist und vergibst für die “zu löschenden” DS ein Gültikeitsflag? Entweder plain boolean un-/gültig oder ein Ablaufdatum.
Gültige Datensätze werden in einen View gefasst, den jede Anwendung bequem nutzen kann. Im Extremfall könnte man beim Insert auch noch die Gültigkeit prüfen und mit Fehler abbrechen.

super danke für diesen Ansatz. Ich wusste nicht, dass sowas unkompliziert geht. Das schau ich mir jetzt mal an und kann so hoffentlich eine Lösung für mich bauen.

Ich habe gerade mal gesucht nach Postgres und Flags aber da nicht wirklich was passendes gefunden. Hast Du da eine gute Seite oder im Zweifel ein gutes buzzword mit dem ich die Suche auf den Weg bringen kann?

Mit “Flag” war eine Spalte gemeint, die nach Deinem Geschmack den Status eines Datensatzes markiert, so wie Du es brauchst.
Kann noch vergeben werden versus dient nur noch der Anzeige

Hier ein Beispiel für gültige Anreden:

create or replace view anrede_insert as 
select id, anrede_kurz, anrede_lang from anrede
 where anrede_ungueltig is null or anrede_gueltig > now();

Alle Deine Anwendungen müssen diesen View als Quelle gültiger Anwendungen verwenden.
Ein insert trigger könnte auf Basis dieses Views alle Inserts in personen auf gültige Anreden zusätzlich prüfen.

Korrektur des Beispiels, auch wenn es nur Fantasie ist, die Spalte zur Prüfung existiert nicht in 2 Varianten, sondern eher nur so:

 where anrede_ungueltig is null or anrede_ungueltig > now();

super vielen Dank! Habe das gerade tatsächlich genau so umgesetzt und es funktioniert, wie erwartet, perfekt. Eine vermeintlich einfache Idee, aber um ehrlich zu sein da wäre ich wahrscheinlich nie mehr drauf gekommen. Meine Ansätze wurden immer aufwendiger. Ich wollte wirklich von hinten durchs Auge. Egal was ich sagen will, bin dir wirklich dankbar für Deine Hilfe!

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.