MySQL - Foreign Key ?

[Matflasch]

Hi,

ich habe ein kleines Problem. Ich benutze unter Windows FoxServ 2.1. Da ist die MySQL Version 3.23.43 dabei.
Nun wollte ich 2 Tabellen zum testen erstellen und die referenzielle Integrität testen. Ich habe dazu folgende Tabellen erstellt:

#1
tbl_jahr
- id
- name

#2
tbl_album
- id
- name
- jahrid

nun habe ich eine Referenz von 'jahrid' auf tbl_jahr(id) gemacht.
Dann habe ich aus der Tabelle tbl_jahr die Eintraege 1,2,3 geloescht. Eigentlich sollten doch nun auch alle Eintraege von 'tbl_album', die im Feld 'jahrid' 1,2 oder 3 stehen haben, ebenfalls geloescht werden. Sie bleiben aber einfach da. Es geht darum, dass wir FoxServ 2.1 in der Schule benutzen, und soweit ich mich erinnern kann, hat das mal funktioniert mit der MySQL-Version. (Obwohl in der MySQL Dokumentation steht, dass es erst ab Version >3.23.44 geht und dann auch nur mit 'TYPE=INNODB' und selbst dann nur eingeschraenkt.)
Daher kann ich mir leider nicht erklären, warum das nun nicht klappt, bzw warum es in der Schule geklappt hat.

Ich habe es auf meinem Webserver auch nochmal getestet mit Version 4.0.15, einmal ohne 'TYPE=INNODB', dann konnte ich beide Tabellen erstellen, jedoch klappt es mit der referenziellen Integrität nicht. Einmal habe ich es dann noch mit 'TYPE=INNODB' probiert. Dann konnte ich nur die Tabelle tbl_jahr erstellen und tbl_album ergab einen Fehler.

Kann mir jemand sagen, was es nun damit auf sich hat?
Danke schonmal!




#### SQL - Quelltext #####
DROP DATABASE IF EXISTS musikdb;
CREATE DATABASE musikdb;

USE musikdb;

CREATE TABLE tbl_jahr (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL);

INSERT INTO tbl_jahr (name) VALUES ('1990');
INSERT INTO tbl_jahr (name) VALUES ('1991');
INSERT INTO tbl_jahr (name) VALUES ('1992');
INSERT INTO tbl_jahr (name) VALUES ('1993');
INSERT INTO tbl_jahr (name) VALUES ('1994');
INSERT INTO tbl_jahr (name) VALUES ('1995');
INSERT INTO tbl_jahr (name) VALUES ('1996');
INSERT INTO tbl_jahr (name) VALUES ('1997');
INSERT INTO tbl_jahr (name) VALUES ('1998');
INSERT INTO tbl_jahr (name) VALUES ('1999');
INSERT INTO tbl_jahr (name) VALUES ('2000');
INSERT INTO tbl_jahr (name) VALUES ('2001');
INSERT INTO tbl_jahr (name) VALUES ('2002');
INSERT INTO tbl_jahr (name) VALUES ('2003');
INSERT INTO tbl_jahr (name) VALUES ('2004');


CREATE TABLE tbl_album (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
jahrid INTEGER,
FOREIGN KEY (jahrid) REFERENCES tbl_jahr(id) ON DELETE CASCADE);

INSERT INTO tbl_album (name, jahrid) VALUES ('Album 1', 1);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 2', 2);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 3', 3);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 4', 4);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 5', 5);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 6', 6);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 7', 7);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 8', 8);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 9', 9);
INSERT INTO tbl_album (name, jahrid) VALUES ('Album 10', 10);

DESCRIBE tbl_jahr;
DESCRIBE tbl_album;

SELECT * FROM tbl_jahr;
SELECT * FROM tbl_album;

DELETE FROM tbl_jahr WHERE id IN(1,2,3);

SELECT * FROM tbl_jahr;
SELECT * FROM tbl_album;

############################################



Ich habe das ganze nun auch mal mit einer 'Satellitentabelle' gemacht, allerdings auch ohne Erfolg:



##### SQL - Quelltext mit Satellitentabelle: #####

DROP DATABASE IF EXISTS musikdb;
CREATE DATABASE musikdb;

USE musikdb;

CREATE TABLE tbl_jahr (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL);

INSERT INTO tbl_jahr (name) VALUES ('1990');
INSERT INTO tbl_jahr (name) VALUES ('1991');
INSERT INTO tbl_jahr (name) VALUES ('1992');
INSERT INTO tbl_jahr (name) VALUES ('1993');
INSERT INTO tbl_jahr (name) VALUES ('1994');

INSERT INTO tbl_jahr (name) VALUES ('1995');
INSERT INTO tbl_jahr (name) VALUES ('1996');
INSERT INTO tbl_jahr (name) VALUES ('1997');
INSERT INTO tbl_jahr (name) VALUES ('1998');
INSERT INTO tbl_jahr (name) VALUES ('1999');
INSERT INTO tbl_jahr (name) VALUES ('2000');
INSERT INTO tbl_jahr (name) VALUES ('2001');
INSERT INTO tbl_jahr (name) VALUES ('2002');
INSERT INTO tbl_jahr (name) VALUES ('2003');
INSERT INTO tbl_jahr (name) VALUES ('2004');


CREATE TABLE tbl_album (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL);

INSERT INTO tbl_album (name) VALUES ('Album 1');
INSERT INTO tbl_album (name) VALUES ('Album 2');
INSERT INTO tbl_album (name) VALUES ('Album 3');
INSERT INTO tbl_album (name) VALUES ('Album 4');
INSERT INTO tbl_album (name) VALUES ('Album 5');
INSERT INTO tbl_album (name) VALUES ('Album 6');
INSERT INTO tbl_album (name) VALUES ('Album 7');
INSERT INTO tbl_album (name) VALUES ('Album 8');
INSERT INTO tbl_album (name) VALUES ('Album 9');
INSERT INTO tbl_album (name) VALUES ('Album 10');


CREATE TABLE tbl_ref_album_jahr (
jahrid INTEGER NOT NULL,
albumid INTEGER NOT NULL,
FOREIGN KEY (jahrid) REFERENCES tbl_jahr(id) ON DELETE CASCADE,
FOREIGN KEY (albumid) REFERENCES tbl_album(id) ON DELETE CASCADE);

INSERT INTO tbl_ref_album_jahr (jahrid, albumid) VALUES (1,1);
INSERT INTO tbl_ref_album_jahr (jahrid, albumid) VALUES (1,2);
INSERT INTO tbl_ref_album_jahr (jahrid, albumid) VALUES (2,3);
INSERT INTO tbl_ref_album_jahr (jahrid, albumid) VALUES (2,4);
INSERT INTO tbl_ref_album_jahr (jahrid, albumid) VALUES (3,5);
INSERT INTO tbl_ref_album_jahr (jahrid, albumid) VALUES (3,6);


DESCRIBE tbl_jahr;
DESCRIBE tbl_album;
DESCRIBE tbl_ref_album_jahr;

SELECT * FROM tbl_jahr;
SELECT * FROM tbl_album;
SELECT * FROM tbl_ref_album_jahr;

DELETE FROM tbl_jahr WHERE id IN(1,2,3);
DELETE FROM tbl_album WHERE id IN(1,2,3);

SELECT * FROM tbl_jahr;
SELECT * FROM tbl_album;
SELECT * FROM tbl_ref_album_jahr;

[Christoph]

Soweit ich weiss unterstützt MySQL keine Foreign Key Constraints. Allerdings verhält sich MySQL dabei etwas hinterhältig, denn die SQL-Anweisung "... REFERENCES ..." wird stillschweigend ignoriert.

Übrigens werden auch bei leistungsfähigeren Datenbanksystemen abhängige Datensätze nicht automatisch mitgelöscht. Dazu musst Du laut SQL-Standard die Klausel "ON DELETE CASCADE" verwenden; die Voreinstellung ist "ON DELETE RESTRICT". Für mehr Infos siehe http://www.de.postgresql.org/docs/7.4/static/ddl-constraints.html

[Thomas Engelke]

In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints.

For other table types, MySQL Server does parse the FOREIGN KEY syntax in CREATE TABLE commands, but without further action being taken.

Direkt aus der Doku :)

TME