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

[Hony%]

Wir brauchen als erstes zwei einfache Tabellen. Eine mit Fremdschlüsselbeziehung.

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.

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.

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:

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!

 

[akretschmer]

Die Datenbank muss man lieben!

ACK.

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

 

[Hony%]

Soll ich es Dir zeigen?

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.

 

[akretschmer]

Ehrlich gesagt – ja. Ich halte es auch für wichtig das Problem zu zeigen.

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.

 

[Hony%]

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:

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.

 

[akretschmer]

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

ACK.

Das bringt mich zu der Frage ob es eine Möglichkeit gibt eine Prüfung durch die Datenbank selbst anzustoßen.

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.

 

[Hony%]

Zu MySQL kann ich nix sagen.

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.

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

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

 

[akretschmer]

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

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

*duck*

 

[Hony%]

Vermutlich dachten die Macher von MySQL generell, daß eingegebene Daten immer und überall valide sind.

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?