1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

MySQL und Referenzielle Integrität – Wer braucht schon Fremdschlüssel?

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von Hony%, 4 März 2014.

  1. Hony%

    Hony% Datenbank-Guru

    Wir brauchen als erstes zwei einfache Tabellen. Eine mit Fremdschlüsselbeziehung.
    Code:
    mysql> CREATE TABLE table1 (id INT NOT NULL, PRIMARY KEY(id));
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> CREATE TABLE table2 (fkid INT NOT NULL, FOREIGN KEY (fkid) REFERENCES table1(id));
    Query OK, 0 rows affected (0.11 sec)
    Werte in table1 einzufügen ist natürlich problemlos möglich. Die dort enthaltenen Werte in table2 ebenso.
    Code:
    mysql> INSERT INTO table1 VALUES (1);
    Query OK, 1 row affected (0.08 sec)
    
    mysql> INSERT INTO table2 VALUES (1);
    Query OK, 1 row affected (0.09 sec)
    
    mysql> INSERT INTO table2 VALUES (2);
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`table2`, CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`fkid`) REFERENCES `table1` (`id`))
    MySQL macht also was es soll. Aber MySQL wäre nicht MySQL wenn sich das nicht umgehen ließe.
    Code:
    mysql> SET FOREIGN_KEY_CHECKS = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> INSERT INTO table2 VALUES (2);
    Query OK, 1 row affected (0.05 sec)
    Wenn man die Prüfung wieder einschaltet stört es MySQL aber nicht im geringsten, dass die Daten nicht den Bedingungen entsprechen:
    Code:
    mysql> SET FOREIGN_KEY_CHECKS = 1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT * from table1;
    +----+
    | id |
    +----+
    |  1 |
    +----+
    1 row in set (0.00 sec)
    
    mysql> SELECT * from table2;
    +------+
    | fkid |
    +------+
    |    1 |
    |    2 |
    +------+
    2 rows in set (0.05 sec)
    Die Datenbank muss man lieben! :D
     
  2. akretschmer

    akretschmer Datenbank-Guru

    ACK.

    Geht aber in PG auch. Soll ich es Dir zeigen? Eigentlich will ich es nicht zeigen, aber es geht. Leider.
     
  3. Hony%

    Hony% Datenbank-Guru

    Ehrlich gesagt – ja. Ich halte es auch für wichtig das Problem zu zeigen. Dabei sollte es für die Datenbank doch nicht so schwer sein die Konsistenz beim Aktivieren einmalig zu prüfen.

    Mit so einer Einstellung wird es extrem erleichtert eine Datenbank als SQL-Dump neu einzuspielen. Aber es gibt eben auch keine Garantie, dass die so wiederhergestellten Daten konsistent sind.
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Code:
    test=*# create table m (id int primary key);
    CREATE TABLE
    test=*# create table s (i int references m);
    CREATE TABLE
    test=*# commit;
    COMMIT
    test=# alter table s disable trigger all;
    ALTER TABLE
    test=*# insert into s values (4711);
    INSERT 0 1
    test=*# commit;
    COMMIT
    test=# select * from m;
     id
    ----
    (0 rows)
    
    test=*# select * from s;
      i
    ------
     4711
    (1 row)
    
    test=*# \d s
      Table "public.s"
     Column |  Type  | Modifiers
    --------+---------+-----------
     i  | integer |
    Foreign-key constraints:
      "s_i_fkey" FOREIGN KEY (i) REFERENCES m(id)
    
    test=*# commit;
    COMMIT
    
    DISABLE TRIGGER ALL setzt Superuser voraus, das entschärft die Sache etwas. Krass ist es dennoch.
     
  5. Hony%

    Hony% Datenbank-Guru

    Ich verstehe ja die Mechanik die zu diesem Problem führt. Technisch betrachtet sind Fremdschlüssel ja nur Trigger und keine geheimnisvolle Macht.

    Ob es falsche Werte gibt lässt sich ja mit einer einfachen Abfrage prüfen:
    Code:
    SELECT i
    FROM s
    WHERE i NOT IN (
    SELECT id
    FROM m)
    Bei PG hängt das ja nur an einer bestimmten Tabelle. Bei MySQL wirkt die Einstellung aber auf die ganze Datenbank. Das bringt mich zu der Frage ob es eine Möglichkeit gibt eine Prüfung durch die Datenbank selbst anzustoßen.
     
  6. akretschmer

    akretschmer Datenbank-Guru

    ACK.


    Zu MySQL kann ich nix sagen.

    In PG kannst Du je Tabelle in pg_trigger abfragen, ob TRIGGER enabled sind, Details http://www.postgresql.org/docs/9.3/interactive/catalog-pg-trigger.html.
    Wer mit dem Feature hantiert, sollte halt wissen, was er tut. Wenn er wieder Trigger enablen will kann er ja vorher prüfen.

    Das einzig sinnvolle Anwendungsszenario für mich ist Bulk-Insert bzw. Restore, wo man zu 100% sicher sein kann, daß die Daten okay sind.
     
  7. Hony%

    Hony% Datenbank-Guru

    Code:
    mysql> SHOW VARIABLES LIKE "foreign_key_checks";
    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | foreign_key_checks | ON    |
    +--------------------+-------+
    1 row in set (0.02 sec)
    Es handelt sich dabei um eine globale Session-Variable.

    Denke ich auch. Und ich vermute, dass genau dieser Anwendungsfall der Grund für die Existenz ist.
     
  8. akretschmer

    akretschmer Datenbank-Guru

    Vermutlich dachten die Macher von MySQL generell, daß eingegebene Daten immer und überall valide sind. Daher werden CHECK-Constraints akzeptiert - und ignoriert.

    *duck*
     
  9. Hony%

    Hony% Datenbank-Guru

    Das ist ja auch ganz klar Aufgabe der Anwendung. Wo kämen wir den da hin wenn jeder willkürlichen Murks an die Datenbank schicken würden? :D
     
    akretschmer gefällt das.
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden