Referentielle Integrität in MySQL (Referentielle Integrität in MySQL), Lektion, Seite 723060
https://www.purl.org/stefan_ram/pub/referentielle_mysql (Permalink) ist die kanonische URI dieser Seite.
Stefan Ram
↑SQL-Kurs

Referentielle Integrität in MySQL 

Einen Zelle in einer Fremdschlüsselspalte nennen wir auch eine Referenz.

Eine Referenz in einer Spalte, welche wir Referenzspalte  nennen, bezieht sich auf eine andere Spalte, welche wir Referentenspalte  nennen.

Ein bestimmter Referenzwert in der Referenzspalte bezieht sich auf diejenige Zeile, welche in der Referentenspalte T diesen Referenzwert enthält. Wir nennen jene Zeile auch den Referenten  jenes Referenzwerts.

Die referentielle Integrität  eines Schemas ist gegeben, wenn gilt:

(Ein Referenzwert ist unmarkiert, wenn er nicht »NULL« ist.)

lateinisch "integer" (aus "in" und "tango") „unberührt“, „unversehrt“, „intakt“, „unbeschädigt“.

Bei MySQL  unterstützt nur InnoDB  Fremdschlüssel.

Fremdschlüsselrelation

Festlegung

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '2', 'Amelie Schulz'  );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '3', 'Jonas Seidel'  );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '4', 'Mika Kuhn'    );

CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ));

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin bevorzugt blaue Moebel.' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin wuenscht keine Anrufe' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '4', 'Kunde wird im Juni umziehen.'  );
SELECT * FROM KUNDE;
SELECT * FROM NOTIZ;
Aussprachehinweis
foreign ˈfɔrɪn

Ein Fremdschlüssel kann sich nur auf eine Primärschlüsselspalte beziehen.

Verletzung durch Hinzufügen

Konsole (MySQL )
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '5', 'Lieferung erst ab KW 32 moeglich.'  );
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`s`.`notiz`, CONSTRAINT `notiz_ibfk_1` FOREIGN KEY (`KUNDE`) REFERENCES `kunde` (`KUNDE`))

Verletzung durch Löschen

Konsole (MySQL )
DELETE FROM KUNDE WHERE KUNDE = '1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`s`.`notiz`, CONSTRAINT `notiz_ibfk_1` FOREIGN KEY (`KUNDE`) REFERENCES `kunde` (`KUNDE`))

Verletzung durch Änderung

Konsole (MySQL )
UPDATE KUNDE SET KUNDE = '5' WHERE KUNDE = '4';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`s`.`notiz`, CONSTRAINT `notiz_ibfk_1` FOREIGN KEY (`KUNDE`) REFERENCES `kunde` (`KUNDE`))

Verletzung durch Änderung der Struktur

Konsole (MySQL )
ALTER TABLE KUNDE DROP KUNDE;
ERROR 1829 (HY000): Cannot drop column 'kunde': needed in a foreign key constraint 'notiz_ibfk_1' of table 'notiz'

DELETE-Optionen

»ON DELETE RESTRICT«

Hat dieselbe Bedeutung wie keine Angabe.

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );

CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ) ON DELETE RESTRICT );

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
DELETE FROM KUNDE WHERE KUNDE = '1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`s`.`notiz`, CONSTRAINT `notiz_ibfk_1` FOREIGN KEY (`KUNDE`) REFERENCES `kunde` (`KUNDE`))

»ON DELETE NO ACTION«

Hat dieselbe Bedeutung wie keine Angabe.

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );

CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ) ON DELETE NO ACTION );

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
DELETE FROM KUNDE WHERE KUNDE = '1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`s`.`notiz`, CONSTRAINT `notiz_ibfk_1` FOREIGN KEY (`KUNDE`) REFERENCES `kunde` (`KUNDE`) ON DELETE NO ACTION)

»ON DELETE SET NULL«

Hier wird beim Löschen eines Referenten der Fremdschlüssel aller Referenzen auf »NULL« gesetzt.

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ) ON DELETE SET NULL );

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
DELETE FROM KUNDE WHERE KUNDE = '1';
SELECT * FROM NOTIZ;
+-------+----------------------------------------+
| KUNDE | TEXT                                   |
+-------+----------------------------------------+
| NULL  | Kundin beschwert sich ueber Lieferung. |
+-------+----------------------------------------+

»ON DELETE CASCADE«

Hier werden beim Löschen eines Referenten auch alle Referenzen gelöscht (was sich gegebenenfalls bis zu deren Referenzen u.s.w. fortsetzt).

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ) ON DELETE CASCADE );

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
DELETE FROM KUNDE WHERE KUNDE = '1';
SELECT * FROM NOTIZ;
Empty set

UPDATE-Optionen

»ON UPDATE RESTRICT«

Hat dieselbe Bedeutung wie keine Angabe.

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );

CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ) ON UPDATE RESTRICT );

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
UPDATE KUNDE SET KUNDE = '2' WHERE KUNDE = '1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`s`.`notiz`, CONSTRAINT `notiz_ibfk_1` FOREIGN KEY (`KUNDE`) REFERENCES `kunde` (`KUNDE`))

»ON UPDATE NO ACTION«

Hat dieselbe Bedeutung wie keine Angabe.

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );

CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ) ON UPDATE NO ACTION );

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
UPDATE KUNDE SET KUNDE = '2' WHERE KUNDE = '1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`s`.`notiz`, CONSTRAINT `notiz_ibfk_1` FOREIGN KEY (`KUNDE`) REFERENCES `kunde` (`KUNDE`) ON UPDATE NO ACTION)

»ON UPDATE SET NULL«

Hier wird beim Ändern eines Referenten der Fremdschlüssel aller Referenzen auf »NULL« gesetzt.

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ) ON UPDATE SET NULL );

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
UPDATE KUNDE SET KUNDE = '2' WHERE KUNDE = '1';
SELECT * FROM NOTIZ;
+-------+----------------------------------------+
| KUNDE | TEXT                                   |
+-------+----------------------------------------+
| NULL  | Kundin beschwert sich ueber Lieferung. |
+-------+----------------------------------------+

»ON UPDATE CASCADE«

Hier werden beim Ändern eines Referenten auch alle Referenzen entsprechend geändert (was sich gegebenenfalls bis zu deren Referenzen u.s.w. fortsetzt).

Konsole (MySQL )
DROP SCHEMA S; CREATE SCHEMA S; USE S;

CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ));

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
  TEXT VARCHAR ( 255 ),
  FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ) ON UPDATE CASCADE );

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.'  );
UPDATE KUNDE SET KUNDE = '2' WHERE KUNDE = '1';
SELECT * FROM NOTIZ;
+-------+----------------------------------------+
| KUNDE | TEXT                                   |
+-------+----------------------------------------+
| 2     | Kundin beschwert sich ueber Lieferung. |
+-------+----------------------------------------+

Ergänzende Hinweise

Es sind auch Kombinationen wie »ON DELETE RESTRICT ON UPDATE CASCADE« möglich.

Fremdschlüsseleinschränkungen können auch mit »ADD FOREIGN KEY« hinzugefügt werden.

Syntax
    ADD FOREIGN KEY [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

Fremdschlüsseleinschränkungen können auch mit »ALTER TABLE« hinzugefügt werden.

Beispiel
ALTER TABLE A
  ADD FOREIGN KEY ( E ) REFERENCES H ( K ) ON DELETE CASCADE,
  ADD FOREIGN KEY ( F ) REFERENCES I ( L ) ON DELETE CASCADE,
  ADD FOREIGN KEY ( G ) REFERENCES J ( M ) ON DELETE CASCADE;

Die Prüfungen der referentiellen Integrität können vorübergehend außer Kraft gesetzt werden, etwa bei der Verarbeitung eines Skripts.

Durch das Klientenkommando »SOURCE« wird die hinter »SOURCE« angegebene Datei als Skript ausgeführt. Dieses Kommando darf nicht mit einem Semikolon »;« abgeschlossen werden!

Eingabe
SET foreign_key_checks = 0;
SOURCE dump_file_name
SET foreign_key_checks = 1;

Hinweis für den Dozenten  ► Wb

Zitat von Codd (1990) *
Referential integrity is defined as follows:
Let D be a domain from which one or more primary keys draw their values. Let K be a foreign key, which draws its values from domain D. Every unmarked value which occurs in K must also exist in the database as the value of the primary key on domain D of some base relation.











Seiteninformationen und Impressum   |   Mitteilungsformular  |   "ram@zedat.fu-berlin.de" (ohne die Anführungszeichen) ist die Netzpostadresse von Stefan Ram.   |   Eine Verbindung zur Stefan-Ram-Startseite befindet
sich oben auf dieser Seite hinter dem Text "Stefan Ram".)  |  



Der Urheber dieses Textes ist Stefan Ram. Alle Rechte sind vorbehalten.
Diese Seite ist eine Veröffentlichung von Stefan Ram.









Schlüsselwörter zu dieser Seite/relevant keywords describing this page:





Stefan Ram Berlin slrprd slrprd stefanramberlin spellched stefanram723060 stefan_ram:723060

Referentielle Integrität in MySQL



Stefan Ram, Berlin, and, or, near, uni, online,
slrprd, slrprdqxx, slrprddoc, slrprd723060, slrprddef723060, PbclevtugFgrsnaEnz
Erklärung, Beschreibung, Info, Information, Hinweis,










Der Urheber dieses Textes ist Stefan Ram. Alle Rechte sind vorbehalten.
Diese Seite ist eine Veröffentlichung von Stefan Ram.





https://www.purl.org/stefan_ram/pub/referentielle_mysql