(MYSQL) Problem mit referenzieller Integrität (Lösung)

[*Markus]

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