Referentielle Integrität in mysql

[haiflosse]

Hallo!
Ich möchte die Referentielle Integrität in mysql bei einer Beziehung aktivieren.
Ich habe eine Mitarbeiter Tabelle wo ich eine Abteilungsnummer eingebe. Dazu habe ich auch eine Abteilungstabelle.

Im Designer habe ich die Felder abteilung.abtnr mit mitarbeiter.abtnr verbunden, aber ich kann nach wie vor in der Mitarbeiter Tabelle eine Abtnr eingeben, die es in der Abteilungs Tabelle nicht gibt.

Daher wollte ich fragen, wie ich die Referentielle Integrität aktivieren kann.
Danke.

 

[dabadepdu]

Ich glaube, das ist einer der Schwachpunkte von mySQL. Ehrlich gesagt, ich führe nicht genau Buch darüber. Ich habe im Kopf, dass es irgendein ganz brandneues Update für Version 8 gibt, was das endlich kann. Oder war das MariaDB?
Der Witz ist, dass dieses Feature der Referentiellen Integrität einer der Hauptgründe / Existenzberechtigungen für RDBMS ist, also einer von einer Handvoll Gründe. Eine traurige Sache für mySQL.
Du könntest vielleicht eine andere DB nehmen als mySQL.
Es gibt hier aber Spezialisten für mySQL, die können Dir besser helfen. Du bist übrigens glaub ich der Erste hier im Forum, der mySQL nutzt und sich für Referentielle Integrität interessiert. Dafür schon mal mein Lob an Dich.

 

[akretschmer]

m Designer habe ich die Felder abteilung.abtnr mit mitarbeiter.abtnr verbunden, aber ich kann nach wie vor in der Mitarbeiter Tabelle eine Abtnr eingeben, die es in der Abteilungs Tabelle nicht gibt.

Ob Du da in einem Grafikprogramm oder im Schnee eine gelbe Linie ziehst ist der DB vergleichsweise egal, Du mußt das schon via SQL definieren.

postgres=# create table master(id int);
CREATE TABLE
postgres=# create table detail(id int, master_id int);
CREATE TABLE
postgres=# insert into detail values (1,42);
INSERT 0 1
postgres=# alter table master add constraint id_pk primary key (id);
ALTER TABLE
postgres=# alter table detail add constraint fk_master foreign key (master_id) references master(id);
ERROR:  insert or update on table "detail" violates foreign key constraint "fk_master"
DETAIL:  Key (master_id)=(42) is not present in table "master".

Ob MySQL das kann? Probier es aus ...

 

[BerndB]

geht doch.

Beispiel aus dem Netz:

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '2', 'Amelie Schulz'  );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '3', 'Jonas Seidel'  );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '4', 'Mika Kuhn'    );

CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ));

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin bevorzugt blaue Moebel.' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin wuenscht keine Anrufe' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '4', 'Kunde wird im Juni umziehen.'  );

/* Fehler */
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '5', 'Kunde wird im Juni umziehen.'  );

SELECT * FROM KUNDE;

SELECT * FROM NOTIZ;

Das Ergebnis:

mysql> CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));
Query OK, 0 rows affected (0.17 sec)

mysql>
mysql> INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '2', 'Amelie Schulz'  );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '3', 'Jonas Seidel'  );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '4', 'Mika Kuhn'    );
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> CREATE TABLE NOTIZ
    -> ( KUNDE VARCHAR ( 255 ),
    ->   TEXT VARCHAR ( 255 ),
    ->   FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ));
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin bevorzugt blaue Moebel.' );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin wuenscht keine Anrufe' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '4', 'Kunde wird im Juni umziehen.'  );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> /* Fehler */
mysql> INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '5', 'Kunde wird im Juni umziehen.'  );
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`forum`.`NOTIZ`, CONSTRAINT `NOTIZ_ibfk_1` FOREIGN KEY (`KUNDE`) REFERENCES `KUNDE` (`KUNDE`))
mysql>
mysql> SELECT * FROM KUNDE;
+-------+----------------+
| KUNDE | NAME           |
+-------+----------------+
| 1     | Helene Schmidt |
| 2     | Amelie Schulz  |
| 3     | Jonas Seidel   |
| 4     | Mika Kuhn      |
+-------+----------------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM NOTIZ;
+-------+----------------------------------------+
| KUNDE | TEXT                                   |
+-------+----------------------------------------+
| 1     | Kundin beschwert sich ueber Lieferung. |
| 1     | Kundin bevorzugt blaue Moebel.         |
| 1     | Kundin wuenscht keine Anrufe           |
| 4     | Kunde wird im Juni umziehen.           |
+-------+----------------------------------------+
4 rows in set (0.00 sec)

mysql>

Gruß
Bernd

 

[haiflosse]

Danke für die Antwort.
Wenn ich das Beispiel von @BerndB verwende funktioniert dies sehr gut.
Komisch ist, dass meine Beispieldatenbank (proj.zip) dies nicht funktioniert.
Ich habe dort folgenden Foreign Key nachträglich angelegt:

ALTER TABLE mitarbeiter
  ADD  CONSTRAINT ma_abt
        FOREIGN KEY (abtnr )
        REFERENCES abt(abtnr)

Ich kann danach aber immer noch in der Mitarbeiter Tabelle bei abtnr z.b. die abtnr 70 oder eine Zahl eingeben die nicht in der Tabelle abt existiert.

Hoffe es kann mir hier jemand weiterhelfen.
Vielen Dank

 

[akretschmer]

vielleicht passen Wetter oder Wochentag nicht ;-)
MyISAM kann es wohl z.B. auh nicht.

 

[tk1234]

Komisch ist, dass meine Beispieldatenbank (proj.zip) dies nicht funktioniert.

Falsche Engine, verwende InnoDB statt MyISAM - und setze das Charset bei der Gelegenheit auch gleich auf utf8mb4 statt utf8 (das ist gleichbedeutend mit utf8mb3 welches deprecated ist).

 

[haiflosse]

Danke für den Hinweis.
Die myisam konnte ich in innodb ändern, dann funktioniert auch die ref.integrität aber wie kann ich utf8_general_ci in utf8mb4 ändern?
ALTER TABLE abt CONVERT TO CHARACTER SET utf8_general_ci COLLATE utf8mb3_general_ci;
funktioniert leider nicht. Da bekomme ich immer die Meldung:
#1115 - Unbekannter Zeichensatz: 'utf8_general_ci'

 

[tk1234]

aber wie kann ich utf8_general_ci in utf8mb4 ändern?

Garnicht. Du vermischst da zwei Dinge: Charset und Collation - ersteres beschreibt die Kodierung in der die Daten gespeichert werden, zweiteres sind quasi die Sortierregeln für die Daten und hängt vom Charset ab. Du versuchst beide Werte auf eine Collation zu setzen was natürlich nicht geht - richtig wäre:

ALTER TABLE ABT CONVERT TO
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

wobei utf8mb4 das Charset ist und utf8mb4_general_ci die Collation. Übrigens: du solltest dich entscheiden wie du die Tabellennamen (und auch Spalten) schreibst: die Tabelle ABT und abt ist nicht das gleiche, zumindest unter richtigen Betriebssystemen bekommst du eine Fehlermeldung wenn die Tabelle ABT heißt, du aber abt schreibst (und umgekehrt) - mehr oder weniger üblich ist es SQL-Begriffe (z.B. SELECT) groß und Spalten/Tabellen-Namen klein zu schreiben, dann lassen sich Querys besser lesen.

 

[castorp]

zumindest unter richtigen Betriebssystemen bekommst du eine Fehlermeldung wenn die Tabelle ABT heißt, du aber abt schreibst (und umgekehrt) -

Laut SQL Standard ist das zweimal der gleiche (identische) Tabellenname. Das muss vom Betriebssystem unabhängig sein.

 

[tk1234]

Laut SQL Standard ist das zweimal der gleiche (identische) Tabellenname. Das muss vom Betriebssystem unabhängig sein.

Dann hält sich MySQL nicht an den Standard, laut Handbuch ist das vom Betriebssystem abhängig ob bei Groß-/Kleinschreibung unterschieden wird - unter Mint kann ich die Tabellen "ABT" und "abt" in der gleichen Datenbank anlegen.

 

[castorp]

Dann hält sich MySQL nicht an den Standard

Ja, und nicht nur was das betrifft.

ist das vom Betriebssystem abhängig ob bei Groß-/Kleinschreibung unterschieden wird

Genauer gesagt hängt es vom Dateisystem ab. Wenn es ein case-sensitives Dateisystem für Windows gäbe, dann könnte das auch auf Windows passieren. Oder wenn es ein nicht case-sensitives Dateisystem unter Linux gibt, wäre es in Linux wiederum unerheblich (wie es z.B. unter MacOS der Fall ist)

Dazu kommen noch andere Konfigurationseinstellungen. Bei InnoDB hängt es z.B. von der Einstellung innodb_file_per_table ab. Wenn es nicht gesetzt ist, dann spielt das Dateisystem wiederum keine Rolle. Wenn es gesetzt ist, dann spielt das Dateisystem eine Rolle. Und wenn das der Fall ist, dann hängt es wiederum von der Einstellung von lower_case_table_names ab.