Konzept von referentieller Integrität / Foreign Keys.

[Kampfgummibaerlie]

Mir ist klar, dass ein "Foreign Key" ein Fremdschlüssel ist, wobei ich mich nie damit beschäftigt habe, in wiefern dieser in "andere" Tabellen "zur Verfügung" gestellt wird.

LW = Löschweitergabe
ÄW = Änderungsweitergabe
RI = referentielle Integrität

Ich glaube, ich sollte eine referentielle Integrität zwischen Tabellen Kunden und Vermietungen aufbauen, wobei das Ganze eine 1:n Beziehung ist, die darstellt, dass 1 Kunde mehrmalig in die Tabelle Vermietungen eingetragen werden kann.

Sprich der Kunden_Name ist der Foreign-Key? (Ich möchte in der Vermietungstabelle den Namen eintragen), und Kunden_ID is der Primary Key (logisch), und der Foreign Key wird mittels referentieller Integrität mit der Tabelle Vermietungen verbunden?

Ich glaube, man muss Foreign Keys bereits beim erstellen der Tabelle mtgeben? Oder ist das nachträglich noch änderbar?

Regergen aus dem Internet:

CREATE TABLE Artikel(
ArtikelNr INTEGER NOT NULL,
ISBN VARCHAR(17),
KategorieNr INTEGER NOT NULL,
Verlagscode VARCHAR(10),
Artikelpreis NUMERIC NOT NULL,
. . . ,
PRIMARY KEY (ArtikelNr),
FOREIGN KEY (KategorieNr) REFERENCES Kategorie(KategorieNr)
);

Aber wie oben gesagt, würde ich gerne einen solchen Foreign Key nachträglich (Tabelle existiert) mit einer anderen Tabelle verbinden.

(Gibt es sowas wie den Befehl "alter"?)

Schreibe mir alle Codes mit, die funktionieren, in der richtigen Reihenfolge, damit ich jederzeit eine komplett neu erstellen kann, falls irgendein Zwischenfall mir das Ganze verdirbt ^^

Könnte also gerne den Code ein wenig erweitern/umschreiben, die Tabelle löschen, und mal eben neu erstellen, aber ja, Zwecks allgemeiner Information interessiert es mich

PostgreSQL: Documentation: 9.1: ALTER TABLE

Ja, es gibt den Begriff "alter" auch in PostgreSQL, aber ich lasse die Frage oben stehen, zwecks finden der Antwort.

 

[Kampfgummibaerlie]

habe das Ganze jetzt (Lehrzweck) schnell gemacht:

Code 1:
alter Table kunden
add unique (kunden_id)

Code 2:
INSERT INTO public.kunden(
kunden_id)
VALUES ('1');

Code 3:
Create Table Vermietungen (Vermietungs_ID bigserial, Kunden_id integer, Maschinen_Name text, Mietzeit timestamprange, Vermietungs_Kosten money, Primary Key(Vermietungs_ID), Foreign Key(Kunden_id) REFERENCES kunden(kunden_id))

Code 4:
INSERT INTO public.vermietungen(
kunden_id)
VALUES ('2');

Fehlermeldung (gewollte):
FEHLER: Einfügen oder Aktualisieren in Tabelle »vermietungen« verletzt Fremdschlüssel-Constraint »vermietungen_kunden_id_fkey« DETAIL: Schlüssel (kunden_id)=(2) ist nicht in Tabelle »kunden« vorhanden.

 

[akretschmer]

Ja, ALTER TABLE existiert - und funktioniert.

test=*# create table kunden (id int, name text);
CREATE TABLE
test=*# create table vermietung (kunde int, geraet text);
CREATE TABLE
test=*# insert into kunden values (1, 'Kunde1');
INSERT 0 1
test=*# insert into vermietung values (1, 'geraet 1');
INSERT 0 1
test=*# alter table kunden add primary key (id);
ALTER TABLE
test=*# alter table vermietung add foreign key (kunde) references kunden(id);
ALTER TABLE
rollback;

Was passiert, wenn a) ein Primary Key nicht erstellt werden kann, weil z.B. der Wert doppelt schon da ist?

test=*# rollback;
ROLLBACK
test=# create table kunden (id int, name text);
CREATE TABLE
test=*# create table vermietung (kunde int, geraet text);
CREATE TABLE
test=*# insert into kunden values (1, 'Kunde1');
INSERT 0 1
test=*# insert into kunden values (1, 'Kunde1');
INSERT 0 1
test=*# alter table kunden add primary key (id);
FEHLER:  konnte Unique Index »kunden_pkey« nicht erstellen
DETAIL:  Schlüssel (id)=(1) ist doppelt vorhanden.
test=*#

Was passiert, wenn b) beim Erstellen des Foreign Keys dieser nicht in der referenzierten Tabelle da ist?

[code]
test=*# truncate kunden;
TRUNCATE TABLE
test=*# alter table kunden add primary key (id);
ALTER TABLE
test=*# insert into vermietung values (1, 'geraet 1');
INSERT 0 1
test=*# alter table vermietung add foreign key (kunde) references kunden(id);
FEHLER:  Einfügen oder Aktualisieren in Tabelle »vermietung« verletzt Fremdschlüssel-Constraint »vermietung_kunde_fkey«
DETAIL:  Schlüssel (kunde)=(1) ist nicht in Tabelle »kunden« vorhanden.
test=*#

Btw.: es gibt eine Extension isn, die einen neuen Datentyp mitbringt: ISBN. Hat den Vorteil, das die ISBN geprüft wird (die haben eine Prüfsumme). Demo:

test=*# create table artikel(id int primary key, isbn isbn);
CREATE TABLE
test=*# insert into artikel values (1, '978-1-84951-906-9');
INSERT 0 1
test=*# insert into artikel values (2, '978-1-84951-906-8');
FEHLER:  invalid check digit for ISBN number: "978-1-84951-906-8", should be 9
ZEILE 1: insert into artikel values (2, '978-1-84951-906-8');
  ^
test=*#

Das Buch kann ich übrigens empfehlen ;-)

 

[akretschmer]

Dein Beispiel hat einen Fehler: ein Unique-Constraint ist was anderes als ein Primary Key. Unique läßt auch NULL-Werte zu

 

[Kampfgummibaerlie]

Stimmt, weil der Primary Key immer eine ganze Zahl ist.
Tut mir leid *ins Eck gehe und mich schäme*

Sprich ich sollte das als Primary Key machen, weil ein Primary Key IMMER ein Constraint ist, und immer eine ganze Zahl?

Ich mache mir mal wieder einen Kaffee
Und wenn ich ehrlich sein darf, es gefällt mir hier (Mehr als bei Sololearn, weil man hier mehr dazu gezwungen wird, auch etwas zu schreiben.... ^^)

Anbei ein Mitgedanke:
Ich habe die Spalte als Integer definiert, sprich ich kann garkeine Dezimalzahl eingeben

Falls der Fehler doch noch sein sollte, wüsste ich gerne, wo :S

 

[akretschmer]

Sprich ich sollte das als Primary Key machen, weil ein Primary Key IMMER ein Constraint ist, und immer eine ganze Zahl?

Du kannst auch andere Datentypen als PK nehmen.

 

[Kampfgummibaerlie]

Ja, weiß ich, meine Schwester (Die kein PostgreSQL kennt, aber beruflich Datenbanken programmiert, jedoch keine Zeit hat, mir weiterzuhelfen) meinte, es ist NIE falsch, eine ID zu erstellen.

Bin ich froh, dass die das nicht kann, sonst wäre ich nie hierher gekommen, und hätte auch keinen Spaß am Ganzen

Denken bevor man schreibt ist auch Vorteilhaft -.-

Also: Ich wusste, das man mehrere Spalten zu einem PK machen kann, und ja, auch andere Typen. Ich wollte irgendwie sagen, dass nur der Integer einen Autowert haben kann, soweit ich es wüsste.

 

[Kampfgummibaerlie]

Neuer Punkt:
Wie vergiebt man mehrere Foreign-Keys? ^^

Create Table Vermietungen (Vermietungs_ID bigserial primary key, Kunden_id integer, Maschinen_Name text, Mietzeit timestamprange, Vermietungs_Kosten money, Primary Key(Vermietungs_ID), Foreign Key(Kunden_id) REFERENCES kunden(kunden_id), Foreign key(Maschinen_id) REFERENCES maschinen(maschinen_id);

geht nämlich nicht:
FEHLER: Syntaxfehler bei »;«
LINE 1: ...oreign key(Maschinen_id) REFERENCES maschinen(maschinen_id);

Muss ein Foreign-Key in der anderen Tabelle ein PK sein? Wenn ja, ist er bereits, geht aber nicht

 

[akretschmer]

Da scheint eine schließende Klammer zu fehlen ...

 

[Kampfgummibaerlie]

Einen Fehler ausgebessert: Ich habe 2 mal einen PK vergeben, 2 mal der Spalte Vermietungs_ID

Anbei, habe ich die Maschnen_ID nichtmal hineingeschrieben bei create, also kanns kein Foreign-Key sein.

Create Table Vermietungen (Vermietungs_ID bigserial primary key, Kunden_id integer, maschinen_id integer, Mietzeit timestamprange, Vermietungs_Kosten money, Foreign Key(Kunden_id) REFERENCES kunden(kunden_id), Foreign key(maschinen_id) REFERENCES maschinen(maschinen_id));

FEHLER: in Tabelle »maschinen«, auf die verwiesen wird, gibt es keinen Unique-Constraint, der auf die angegebenen Schlüssel passt

also muss ich wohl die ID irgendwie als unique schreiben (?)

so funktionierts:

alter table maschinen add unique (maschinen_id)

ALTER TABLE Abfrage wurde in 859 msec erfolgreich zurückgegeben.

Create Table Vermietungen (Vermietungs_ID bigserial primary key, Kunden_id integer, maschinen_id integer, Mietzeit timestamprange, Vermietungs_Kosten money, Foreign Key(Kunden_id) REFERENCES kunden(kunden_id), Foreign key(maschinen_id) REFERENCES maschinen(maschinen_id));

CREATE TABLE Abfrage wurde in 576 msec erfolgreich zurückgegeben

 

[akretschmer]

Du brauchst einen Primary Key, Unique reicht nicht. Bitte zeige hier die korrekten Fehlermeldungen.

 

[Kampfgummibaerlie]

so funktionierts:

alter table maschinen add unique (maschinen_id)

Create Table Vermietungen (Vermietungs_ID bigserial primary key, Kunden_id integer, maschinen_id integer, Mietzeit timestamprange, Vermietungs_Kosten money, Foreign Key(Kunden_id) REFERENCES kunden(kunden_id), Foreign key(maschinen_id) REFERENCES maschinen(maschinen_id));

CREATE TABLE Abfrage wurde in 576 msec erfolgreich zurückgegeben

ersterer Code: der Primary Key ist die selbe Spalte, welche bereits serial war. (Hätte ich dazuschreiben müssen)
erster Code wurde erfolgreich ausgeführt
Ich werde mich für heute Abend herrichten (Habe was vor... ^^)

Also, bis heute Abend, oder unter Umständen auch Morgen.
Anbei: Ich habe die Fehlermeldungen geschrieben, die ich bekommen habe, vl. ein wenig durcheinander gebracht, weil ich mehrere Codes zeitgleich offen hatte.

Also, bis bald

 

[Kampfgummibaerlie]

Nachdem ich mir ja die einzelnen Schritte nebenbei abspeichere:

create table Maschinen (Maschinen_ID smallserial primary key, Maschinen_Name text, Maschinen_Mietpreis money, Maschinen_Wartungsdatum date)
mit diesem Code, habe ich die Tabelle Maschinen erstellt

Mit welchem Code ich dann die Tabelle Vermietungen erstellt habe, steht eh oben.
Habe inzwischen auch schon eine 2. Foreign Key-Regel eingebaut, Kunden müssen auch vorher angemeldet/eingetragen werden.

Und die oben erstellte Tabelle habe ich nachher mit einem Unique bestückt.