Bei Tables mit Serial Id eigenmächtigen ID-Eintrag verhindern möglich?

Hallo zusammen,

ich nutze im Moment recht intensiv den Datentyp Serial um Primary Keys für verschiedene Tabellen erstellen zu lassen. Die Tabellen werden jetzt allerdings auch anderen Anwendungen (die anderswo entwickelt werden) zugänglich gemacht. Das Problem dabei ist, dass man bei einem Table mit Serial trotzdem eigenmächtig einen Primary Key vergeben kann bei einem Insert und die Sequenz wird dabei nicht verändert. Sprich, wenn die Sequenz der Serial gerade auf 5 steht, kann ich per Insert-Befehl, bei dem ich die ID selbst angebe, z.B. mit 6, dafür sorgen, dass beim nächsten Insert das automatische vergeben der ID auf die Schnauze fällt, da hier ja auch 6 als nächstes käme und das eine Duplicate-Key-Exception gibt.

Es ist natürlich gut, dass man auch in einen Table mit Serial eigene IDs eingeben kann (z.B. für den Altdatenimport) - allerdings würde ich dieses Verhalten auch gerne abschalten können. Da nun fremde Entwickler per SQL auf die Datenbank zugreifen, kann ich sonst nicht verhindern, dass diese fälschlicherweise eigenmächtig IDs in die Tables reinpressen, da es ja keinen Fehler gibt! Gibt es also irgend eine Einstellung, mit der man das manuelle Vergeben von IDs bei einem Table mit Serial ID verhindern kann?

Grüße,
Bierstuebl

Du kannst das mit einem Trigger erschlagen:

test=# create table test (id serial primary key, name text);
NOTICE: CREATE TABLE will create implicit sequence “test_id_seq” for serial column “test.id”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “test_pkey” for table “test”
CREATE TABLE
test=# create or replace function no_id () returns trigger as $$begin NEW.id = nextval(‘test_id_seq’); return NEW; end;$$language plpgsql;
CREATE FUNCTION
test=# create trigger noid before insert on test for each row execute procedure no_id();
CREATE TRIGGER
test=# insert into test values (5, ‘test’);
INSERT 0 1
test=# select * from test;
id | name
----±-----
1 | test
(1 row)

akretschmer: deine Lösung ist formal richtig hat aber einen Schönheitsfehler: wenn man die ID weglässt, wird von PG eine ID aus der Serial generiert und danach greift deine Funktion noch eine ID ab. Das bildet dann also Lücken in der Sequenz.

Eleganter wäre es, gleich die Spalte auf Integer zu setzen und immer die ID von der Funktion generieren zu lassen. Dafür müsste man sich allerdings manuell eine Serial Sequenz erzeugen.

Ja, da kann ich nicht widersprechen. Wobei Lücken in Sequencen nix problematisches sind, aber ich will Deinen Einwand keinesfalls wegdiskutieren. DANKE.

Das ging ja wieder schnell :slight_smile:

Mmh, ja, so gehts natürlich schon. Ich dachte nur, da gebe vielleicht auch direkt ne Lösung von Postgresql intern. Extra mit Funktionen und Triggern zu arbeiten ist ja schon ein bissle umständlich :wink:

Trotzdem danke für die Lösung. Das mit den Lücken kann ich verschmerzen - übrigens entstehen auch beim normalen Einfügen Lücken. So wie ich das bisher sehe wird die Sequenz bei einem erfolglosen Insert nicht wieder zurückgesetzt (macht ja auch Sinn). Das heißt es entsteht eine Lücke, da diese Sequenznummer verloren ist.

Grüße,
Bierstuebl

Stimmt, dafür sind Sequenzen gedacht.

Im Grunde genommen funktionieren beide Lösungen, ich wollte nur verhindern, das IDs grundlos wegeworfen werden.

Trigger und Funktionen SIND PostgreSQL-Lösungen :wink: