Zwei Fremdschlüssel/Constraints mit phpMyAdmin erstellt, nur ein Index/Key wird angelegt

  • SQL

Es gibt 8 Antworten in diesem Thema. Der letzte Beitrag () ist von Marcus Gräfe.

    Zwei Fremdschlüssel/Constraints mit phpMyAdmin erstellt, nur ein Index/Key wird angelegt

    Ich habe drei Tabellen mit phpMyAdmin erstellt (in einer MariaDB-Datenbank): Benutzer, Zugriffsrechte und eine zugehörige Verknüpfungstabelle.

    In der Verknüpfungstabelle habe ich zwei Fremdschlüssel erstellt. Insgesamt kommt dabei folgender SQL-Code heraus:

    SQL-Abfrage

    1. CREATE TABLE `user` (
    2. `id` int(11) NOT NULL AUTO_INCREMENT,
    3. PRIMARY KEY (`id`)
    4. );
    6. CREATE TABLE `permission` (
    7. `id` int(11) NOT NULL AUTO_INCREMENT,
    8. PRIMARY KEY (`id`)
    9. );
    11. CREATE TABLE `user_permission` (
    12. `user_id` int(11) NOT NULL,
    13. `permission_id` int(11) NOT NULL,
    14. PRIMARY KEY (`user_id`,`permission_id`) USING BTREE,
    15. KEY `cstr-user_permission1` (`permission_id`)
    16. );
    18. ALTER TABLE `user_permission`
    19. ADD CONSTRAINT `cstr-user_permission1` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    20. ADD CONSTRAINT `cstr-user_permission2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

    Meine Frage ist nun, warum in der Tabelle "user_permission" nur KEY `cstr-user_permission1` auftaucht, nicht aber der Key für "cstr-user_permission2".

    Ist das ein Bug von phpMyAdmin (neueste Version) oder fehlt mir hier das SQL-Verständnis?
    Besucht auch mein anderes Forum:
    Das Amateurfilm-Forum

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Marcus Gräfe“ ()

    Marcus Gräfe schrieb:

    ALTER TABLE `user_permission`
    ADD CONSTRAINT `cstr-user_permission1` FOREIGN KEY (`permission_id`) REFERENCES `user_permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `cstr-user_permission2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

    Ich weiß nicht, ob das in einem Befehl geht. Ich kenne das nur so

    SQL-Abfrage

    1. ​ALTER TABLE `user_permission` ADD CONSTRAINT `cstr-user_permission1` FOREIGN KEY (`permission_id`) REFERENCES `user_permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
    2. ALTER TABLE `user_permission` ADD CONSTRAINT `cstr-user_permission2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    ALTER TABLE ging eigentlich schon immer mit beliebig vielen Änderungen, auch z. B. beim Hinzufügen von Feldern.

    Das ist hier allerdings gar nicht mein Problem. Die Frage ist, warum der eine Fremdschlüssel nicht in der Tabelle mittels KEY definiert wird. Also ob das phpMyAdmin "vergessen" hat oder ob das aus irgendeinem Grund gar nicht nötig ist.
    Besucht auch mein anderes Forum:
    Das Amateurfilm-Forum

    Also ich bekomm folgene Fehlermeldung mit deiner Syntax:

    Quellcode

    1. CREATE TABLE `user` (
    2. `id` int(11) NOT NULL AUTO_INCREMENT,
    3. PRIMARY KEY (`id`)
    4. );
    5. CREATE TABLE `permission` (
    6. `id` int(11) NOT NULL AUTO_INCREMENT,
    7. PRIMARY KEY (`id`)
    8. );
    9. CREATE TABLE `user_permission` (
    10. `user_id` int(11) NOT NULL,
    11. `permission_id` int(11) NOT NULL,
    12. PRIMARY KEY (`user_id`,`permission_id`) USING BTREE,
    13. KEY `cstr-user_permission1` (`permission_id`)
    14. );
    15. ALTER TABLE `user_permission`
    16. ADD CONSTRAINT `cstr-user_permission1` FOREIGN KEY (`permission_id`) REFERENCES `user_permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    17. ADD CONSTRAINT `cstr-user_permission2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;




    Es geht aber auch nicht so:

    Quellcode

    1. CREATE TABLE `user` (
    2. `id` int(11) NOT NULL AUTO_INCREMENT,
    3. PRIMARY KEY (`id`)
    4. );
    5. CREATE TABLE `permission` (
    6. `id` int(11) NOT NULL AUTO_INCREMENT,
    7. PRIMARY KEY (`id`)
    8. );
    9. CREATE TABLE `user_permission` (
    10. `user_id` int(11) NOT NULL,
    11. `permission_id` int(11) NOT NULL,
    12. PRIMARY KEY (`user_id`,`permission_id`) USING BTREE,
    13. KEY `cstr-user_permission1` (`permission_id`)
    14. );
    15. ALTER TABLE `user_permission` ADD CONSTRAINT `cstr-user_permission1` FOREIGN KEY (`permission_id`) REFERENCES `user_permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
    16. ALTER TABLE `user_permission` ADD CONSTRAINT `cstr-user_permission2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;



    "Na, wie ist das Wetter bei dir?"
    "Caps Lock."
    "Hä?"
    "Shift ohne Ende!" :thumbsup:

    Ich hatte da leider einen kleinen Tippfehler, entstanden durch die Vereinfachung des SQL-Codes für diesen Forumthread (oben nun auch korrigiert):

    Statt

    SQL-Abfrage

    1. ADD CONSTRAINT `cstr-user_permission1` FOREIGN KEY (`permission_id`) REFERENCES `user_permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

    muss es natürlich

    SQL-Abfrage

    1. ADD CONSTRAINT `cstr-user_permission1` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

    heißen.

    Die Ursprungsfrage hat sich natürlich dadurch nicht verändert.
    Besucht auch mein anderes Forum:
    Das Amateurfilm-Forum

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Marcus Gräfe“ ()

    Innerhalb des CREATE TABLE `user_permission` wird mit KEY `cstr-user_permission1` (`permission_id`) ein Index erstellt. Der taucht dann auch in phpMyAdmin auf.
    Die beiden FKs sollten dann unter Structure -> Relation view auftauchen.

    Ein Foreign Key setzt einen Index voraus, erstellt aber nicht notwendigerweise einen. Die Doku schreibt:
    MySQL requires indexes on foreign keys and referenced keys [...]. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

    Denke das Missverständnis kommt daher, dass der Index und der erste FK den gleichen Namen haben.

    Zum Anzeigen der FKs mit SQL: SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '<Datenbankname>'

    Die beiden FKs lege ich unter "Relation view" an. Der Index wird dann automatisch erstellt. Aber eben nur einer. Das wundert mich. Die Frage ist eben, warum das so ist, vor allem da ich jetzt weiß, dass ein FK einen Index voraussetzt.

    3daycliff schrieb:

    Denke das Missverständnis kommt daher, dass der Index und der erste FK den gleichen Namen haben.

    Demnach lautet die Lösung, erst beide Indizes zu erstellen und danach die Constraints? Ich ging davon aus, dass phpMyAdmin hier selbst tätig wird.

    Früher habe ich SQL-Datenbanken immer manuell getippt, aber derzeit ist das ganze recht umfangreich/komplex, sodass ich mich für ein Erstellen mittels phpMyAdmin entschieden habe. Ich habe früher auch immer direkt in die Tabellendefinition "FOREIGN KEY" geschrieben und nichts mit "CONTRAINTS" gemacht.

    EDIT: OK, laut stackoverflow.com/questions/45…oreign-keys-in-phpmyadmin soll man tatsächlich erst die Indizes erstellen und danach erst die Contraints/Foreign-Keys. Demnach wäre das, was ich hier beobachtet habe, so eine Art nicht zu Ende gedachtes Feature von PMA, manche würden es Bug nennen.
    Besucht auch mein anderes Forum:
    Das Amateurfilm-Forum

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Marcus Gräfe“ ()

    Ah, ich habe die Frage falsch verstanden. Zum Verständnis, das Vorgehen ist:
    1) user und permission erstellen (mit PK)
    2) Tabelle user_permission mit PK (aber ohne den anderen Index) erstellen
    3) die beiden FKs definieren

    Als Ergebnis wurde der KEY `cstr-user_permission1` (`permission_id`) angelegt.
    Und die Frage ist nun, warum kein KEY `cstr-user_permission2` (`user_id`), richtig?

    https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html schrieb:

    In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.
    In dem Fall wird der zweite Index vermutlich nicht benötigt, weil der Primary Key (ist letztendlich auch ein Index) an der Stelle genügt (user_id ist die erste Spalte).

    Wenn man also unbedingt den zweiten Index haben möchte, muss man die in der Tat manuell anlegen. Zumal der automatisch erstellte später auch gelöscht werden könnte:

    https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html schrieb:

    This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint.

    Meine Frage hast du auf jeden Fall exakt verstanden. Genau so war mein Vorgehen und mein Ergebnis.

    3daycliff schrieb:

    In dem Fall wird der zweite Index vermutlich nicht benötigt, weil der Primary Key (ist letztendlich auch ein Index) an der Stelle genügt

    Allerdings geht der Primary Key in dem Fall über zwei/beide Spalten.

    Ich habe aber soeben getestet, was passiert, wenn ich in der referenzierenden Tabelle keinen PK definiere (auch keinen sonstigen Index) und dann mit obigen Anweisungen die beiden FK-Contraints einfüge. Ergebnis: Beide Indizes werden erstellt.

    D. h. wenn die user_id-Spalte an erster Stelle im PK vorkommt (egal wieviele Spalten der umfasst), so braucht MySQL offenbar nicht noch einen Index über nur diese eine Spalte.

    Demnach ist das Rätsel gelöst. Vielen Dank!
    Besucht auch mein anderes Forum:
    Das Amateurfilm-Forum