nicht angemeldet
(MYSQL) Verzwicktes Problem mit referenzieller Integrität
Hallo,
im Grunde geht es um 4 Tabellen:
In ArtikelProSortiment soll es so sein, dass entweder a_id, oder z_id NULL sein darf, d.h. ich will erreichen, dass in eines der beiden Felder keine Werte eingefügt werden müssen. (Ideal wäre es, wenn entweder nur das eine oder nur das andere befüllt sein darf, aber es genügt mir, wenn das Ganze generell mal klappen würde, dass ich nicht einfügen muss, wenn ich nicht will).
Ich kann zwar die Tabellen so anlegen, dass ich in ArtikelProSortiment und Klicks diese beiden Felder auf default NULL setze, jedoch kann ich trotzdem nicht nichts einfügen (sql-mode ist STRICT_ALL_TABLES, daran wird nicht gerüttelt). Hier greift nämlich immer natürlich die referenzielle Integrität der anderen Tabellen, da der PK zB in Zubehoer NOT NULL ist, selbst dann wenn die Felder in ArtikelProSortiment NULL sind.
Die Legitimität hierbei ist mir nicht ganz klar: Wenn z_id in Zubehoer NOT NULL ist, und ich in ArtikelProSortiment z_id als NULL definiere und einen Wert dort nicht einfügen will, müsste mir das ja eigentlich erlaubt sein, denn durch die referenzielle Integrität bin ich ja nur insoweit beschränkt, dass im Falle eines Einfügens nur Werte eingefügt werden dürfen, die auch in "Zubehoer" stehen. Das Verhalten, dass ich auch nichts einfügen kann, müsste ja irgendwie herbeigeführt werden können, oder?
Als Workaround könnte ich natürlich Dummywerte 0 als PK in allen referenzierten Tabellen einfügen, um im Falle, dass ich nichts einfügen will, einfach 0 einfüge, aber das scheint mir etwas unprofessionell zu sein.
Außerdem sind es ja nicht nur diese 4 Tabellen, die ich mit Dummywerten beglücken müsste, sondern noch einige andere. Diese "Lösung" will ich aber wenn möglich vermeiden.
Irgendwelche Ideen dazu, wie ich das gewünschte Verhalten herbeiführen könnte?
Ich wünsche auch gleich mal Frohe Weihnachten,
Markus
-
Das Verhalten, dass ich auch nichts einfügen kann, müsste ja irgendwie herbeigeführt werden können, oder?
Ich weiß es bei MySQL zwar nicht zu 100%, aber es wär sinnlos ein Fremdschlüsselfeld als NULL zu definieren, wenn man das dann nicht so eintragen könnte. Dass man das noch extra einstellen muss kann ich mir nicht vorstellen.
Hast du es versucht? Oder wo ist das Problem daran?-
Hallo,
Ich weiß es bei MySQL zwar nicht zu 100%, aber es wär sinnlos ein Fremdschlüsselfeld als NULL zu definieren, wenn man das dann nicht so eintragen könnte. Dass man das noch extra einstellen muss kann ich mir nicht vorstellen.
Hast du es versucht? Oder wo ist das Problem daran?Die Standardeinstellung von MySQL ermöglicht normalerweise diese schlampige Vorgehensweise, dass man in NOT NULL-Felder nichts eintragen kann. Siehe dazu ein anderes Posting von mir,wo ich über diese Tatsache verwundert war:
http://forum.de.selfhtml.org/archiv/2009/12/t193284/Das wollte ich natürlich nicht, und habe sql-mode = STRICT_ALL_TABLES gesetzt, was mir aber jetzt in diesem einen Fall zum Verhängnis wird.
Der Workaround mit dem Setzen von 0 in allen referenzierten Tabellen funktioniert auch nicht so ohne weiteres, da alle Tabellen mit AUTO_INCREMENT bei 1 anfangen zu zählen, und ich 0 nicht mal als erstes INSERT setzen kann.Die Lösung liegt wahrscheinlich in sql-mode. Bin gerade dabei, mir mögliche Einstellungen anzusehen.
Markus
-
Mit http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_no_auto_value_on_zero könnte ich zwar 0 einfügen, aber diese Lösung ist doch nicht so gut. Lese ich nämlich alle Werte irgendwo aus, wäre die Null jetzt auch dabei. Folglich müsste ich im Code überall "if != 0"-Verzweigungen einbauen. Außerdem weiß ich gar nicht in wie weit mir der Provider derartigen Einstellungen erlaubt.
-
Die Standardeinstellung von MySQL ermöglicht normalerweise diese schlampige Vorgehensweise, dass man in NOT NULL-Felder nichts eintragen kann.
Ich dachte eher deine Frage war genau andersrum, warum man in ein NULL-Feld mit Fremdschlüsselcharacter keine NULL eintragen darf?
Aber mit den Modes kenn ich mich leider erst recht nicht aus. Daher kapier ich auch die Frage wahrscheinlich nicht wirklich.
Da muss jemand andres ran.
-
-
-
Ich habe nun eine einfache Lösung unter Einhaltung des Strict-Modes gefunden:
Die beiden Tabellen (Zubehör & Brille) werden nun nicht mehr direkt von ArtikelProSortiment referenziert, sondern davor wurde eine Zwischentabelle (AbstrakterArtikel) gestellt, die diese beiden Tabellen referenziert.
Das Besondere hierbei ist, dass diese FKs als "nicht identifizierend" referenziert wurden, d.h. NULL sein dürfen. In meinem Beispiel kann ich nun aa_id,den PK von AbstrakterArtikel problemlos in den weiteren Tabellen, zB "Klicks" und "ArtikelProSortiment" weiterverwenden, und finde dadurch immer die richtigen Referenzen zu den Artikeln der Kunden, die entweder Zubehör, oder anderes Zeug sein können.Hier das Testbeispiel, mit dem ich der Sache auf den Grund ging:
CREATE TABLE Tab1 (tab1_id INTEGER NOT NULL AUTO_INCREMENT, tab1test VARCHAR(50) NULL, PRIMARY KEY (tab1_id) ) Engine=InnoDB; CREATE UNIQUE INDEX XPKTab1 ON Tab1 ( tab1_id ); CREATE TABLE Tab2 (tab2_id INTEGER NOT NULL AUTO_INCREMENT, tab1_id INTEGER NULL, tab2test VARCHAR(50) NULL, PRIMARY KEY (tab2_id) ) Engine=InnoDB; CREATE UNIQUE INDEX XPKTab2 ON Tab2 ( tab2_id ); ALTER TABLE Tab2 ADD ( CONSTRAINT R_0 FOREIGN KEY (tab1_id) REFERENCES Tab1 (tab1_id) ON DELETE RESTRICT ) ; insert into Tab1 (tab1test) VALUES ('test1'); insert into Tab1 (tab1test) VALUES ('test2');Und hier die Test-INSERTs. Bei falschen Werten greift die referenzielle Integrität, es ist aber keine Voraussetzung, dort einen Wert einfügen zu müssen (was das gewünsche Verhalten ist)
mysql> select @@GLOBAL.sql_mode; +-------------------+ | @@GLOBAL.sql_mode | +-------------------+ | STRICT_ALL_TABLES | +-------------------+ 1 row in set (0.00 sec) mysql> show fields from Tab1; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | tab1_id | int(11) | NO | PRI | NULL | auto_increment | | tab1test | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> show fields from Tab2; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | tab2_id | int(11) | NO | PRI | NULL | auto_increment | | tab1_id | int(11) | YES | MUL | NULL | | | tab2test | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> delete from Tab2; Query OK, 3 rows affected (0.02 sec) mysql> select * from Tab1; +---------+----------+ | tab1_id | tab1test | +---------+----------+ | 1 | test1 | | 2 | test2 | +---------+----------+ 2 rows in set (0.00 sec) mysql> select * from Tab2; Empty set (0.00 sec) mysql> insert into Tab2 (tab2test) VALUES ('foo1'); Query OK, 1 row affected (0.00 sec) mysql> insert into Tab2 (tab1_id, tab2test) VALUES (6,'foo2'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`Tab2`, CONSTRAINT `R_0` FOREIGN KEY (`tab1_id`) REFERENCES `Tab1` (`tab1_id`)) mysql> insert into Tab2 (tab1_id, tab2test) VALUES (2,'foo2'); Query OK, 1 row affected (0.01 sec) mysql> select * from Tab2; +---------+---------+----------+ | tab2_id | tab1_id | tab2test | +---------+---------+----------+ | 5 | NULL | foo1 | | 8 | 2 | foo2 | +---------+---------+----------+ 2 rows in set (0.00 sec) mysql>Markus