wake-up-neo.com

Wie füge ich einer vorhandenen SQLite-Tabelle einen Fremdschlüssel hinzu?

Ich habe folgende Tabelle:

CREATE TABLE child( 
  id INTEGER PRIMARY KEY, 
  parent_id INTEGER, 
  description TEXT);

Wie füge ich eine Fremdschlüsseleinschränkung für parent_id Hinzu? Angenommen, Fremdschlüssel sind aktiviert.

Bei den meisten Beispielen wird davon ausgegangen, dass Sie die Tabelle erstellen. Ich möchte die Einschränkung zu einer vorhandenen hinzufügen.

116
Dane O'Connor

Das kannst du nicht.

Obwohl die SQL-92-Syntax zum Hinzufügen eines Fremdschlüssels zu Ihrer Tabelle wie folgt lautet:

ALTER TABLE child ADD CONSTRAINT fk_child_parent
                  FOREIGN KEY (parent_id) 
                  REFERENCES parent(id);

SQLite unterstützt nicht das ADD CONSTRAINT Variante des ALTER TABLE command ( sqlite.org: SQL-Funktionen, die SQLite nicht implementiert ).

Daher ist die einzige Möglichkeit, einen Fremdschlüssel in SQLite 3.6.1 hinzuzufügen, während CREATE TABLE wie folgt:

CREATE TABLE child ( 
    id           INTEGER PRIMARY KEY, 
    parent_id    INTEGER, 
    description  TEXT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

Leider müssen Sie die vorhandenen Daten in einer temporären Tabelle speichern, die alte Tabelle löschen, die neue Tabelle mit der FK-Einschränkung erstellen und die Daten dann wieder aus der temporären Tabelle kopieren. ( sqlite.org - FAQ: Q11 )

184
Daniel Vassallo

Sie können die Einschränkung hinzufügen, wenn Sie die Tabelle ändern und die Spalte hinzufügen, in der die Einschränkung verwendet wird.

Erstellen Sie zunächst eine Tabelle ohne die parent_id:

CREATE TABLE child( 
  id INTEGER PRIMARY KEY,  
  description TEXT);

Dann ändern Sie die Tabelle:

ALTER TABLE child ADD COLUMN parent_id INTEGER REFERENCES parent(id);
50
Jorge Novaes

Bitte überprüfen Sie https://www.sqlite.org/lang_altertable.html#otheralter

Die einzigen von SQLite direkt unterstützten Befehle zum Ändern des Schemas sind die oben gezeigten Befehle "Tabelle umbenennen" und "Spalte hinzufügen". Anwendungen können jedoch andere willkürliche Änderungen am Format einer Tabelle mithilfe einer einfachen Abfolge von Operationen vornehmen. Die Schritte, um willkürliche Änderungen am Schemaentwurf einiger Tabellen X vorzunehmen, lauten wie folgt:

  1. Wenn Fremdschlüsseleinschränkungen aktiviert sind, deaktivieren Sie sie mit PRAGMA foreign_keys = OFF.
  2. Starten Sie eine Transaktion.
  3. Beachten Sie das Format aller Indizes und Trigger, die mit Tabelle X verknüpft sind. Diese Informationen werden in Schritt 8 unten benötigt. Eine Möglichkeit, dies zu tun, besteht darin, eine Abfrage wie die folgende auszuführen: SELECT-Typ, sql FROM sqlite_master WHERE tbl_name = 'X'.
  4. Verwenden Sie CREATE TABLE, um eine neue Tabelle "new_X" im gewünschten überarbeiteten Format von Tabelle X zu erstellen. Stellen Sie sicher, dass der Name "new_X" nicht mit einem vorhandenen Tabellennamen kollidiert.
  5. Übertragen Sie Inhalte von X nach new_X mit einer Anweisung wie: INSERT INTO new_X SELECT ... FROM X.
  6. Lass den alten Tisch X fallen: DROP TABLE X.
  7. Ändern Sie den Namen von new_X in X mit: ALTER TABLE new_X RENAME TO X.
  8. Verwenden Sie CREATE INDEX und CREATE TRIGGER, um Indizes und Trigger zu rekonstruieren, die mit Tabelle X verknüpft sind. Verwenden Sie möglicherweise das alte Format der in Schritt 3 oben gespeicherten Trigger und Indizes als Richtlinie, und nehmen Sie die für die Änderung erforderlichen Änderungen vor.
  9. Wenn Ansichten in einer Weise auf Tabelle X verweisen, die von der Schemaänderung betroffen ist, löschen Sie diese Ansichten mit DROP VIEW und erstellen Sie sie mit den erforderlichen Änderungen neu, um die Schemaänderung mit CREATE VIEW zu berücksichtigen.
  10. Wenn Fremdschlüsseleinschränkungen ursprünglich aktiviert waren, führen Sie PRAGMA foreign_key_check aus, um sicherzustellen, dass die Schemaänderung keine Fremdschlüsseleinschränkungen verletzt hat.
  11. Übernehmen Sie die in Schritt 2 gestartete Transaktion.
  12. Wenn Fremdschlüsseleinschränkungen ursprünglich aktiviert waren, aktivieren Sie sie jetzt erneut.

Die oben beschriebene Vorgehensweise ist völlig allgemein und funktioniert auch dann, wenn die in der Tabelle gespeicherten Informationen durch die Schemaänderung geändert werden. Das oben beschriebene vollständige Verfahren eignet sich beispielsweise zum Löschen einer Spalte, Ändern der Spaltenreihenfolge, Hinzufügen oder Entfernen einer EINZIGARTIGEN Einschränkung oder eines PRIMARY KEY, Hinzufügen von CHECK- oder FOREIGN KEY- oder NOT NULL-Einschränkungen oder Ändern des Datentyps für eine Spalte.

8
situee

Ja, Sie können, ohne eine neue Spalte hinzuzufügen. Sie müssen sorgfältig vorgehen, um eine Beschädigung der Datenbank zu vermeiden. Sichern Sie daher Ihre Datenbank vollständig, bevor Sie dies versuchen.

für Ihr konkretes Beispiel:

CREATE TABLE child(
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  description TEXT
);

--- create the table we want to reference
create table parent(id integer not null primary key);

--- now we add the foreign key
pragma writable_schema=1;
update SQLITE_MASTER set sql = replace(sql, 'description TEXT)',
    'description TEXT, foreign key (parent_id) references parent(id))'
) where name = 'child' and type = 'table';

--- test the foreign key
pragma foreign_keys=on;
insert into parent values(1);
insert into child values(1, 1, 'hi'); --- works
insert into child values(2, 2, 'bye'); --- fails, foreign key violation

oder allgemeiner:

pragma writable_schema=1;

// replace the entire table's SQL definition, where new_sql_definition contains the foreign key clause you want to add
UPDATE SQLITE_MASTER SET SQL = new_sql_definition where name = 'child' and type = 'table';

// alternatively, you might find it easier to use replace, if you can match the exact end of the sql definition
// for example, if the last column was my_last_column integer not null:
UPDATE SQLITE_MASTER SET SQL = replace(sql, 'my_last_column integer not null', 'my_last_column integer not null, foreign key (col1, col2) references other_table(col1, col2)') where name = 'child' and type = 'table';

pragma writable_schema=0;

In beiden Fällen möchten Sie wahrscheinlich zuerst die SQL-Definition sehen, bevor Sie Änderungen vornehmen:

select sql from SQLITE_MASTER where name = 'child' and type = 'table';

Wenn Sie den Ansatz replace () verwenden, kann es hilfreich sein, vor dem Ausführen zunächst den Befehl replace () zu testen, indem Sie Folgendes ausführen:

select replace(sql, ...) from SQLITE_MASTER where name = 'child' and type = 'table';
3
mwag

Wenn Sie das Firefox-Add-On sqlite-manager verwenden, können Sie Folgendes tun:

Anstatt die Tabelle zu löschen und erneut zu erstellen, können Sie sie einfach so ändern.

Klicken Sie im Textfeld Spalten mit der rechten Maustaste auf den letzten aufgelisteten Spaltennamen, um das Kontextmenü aufzurufen, und wählen Sie Spalte bearbeiten. Beachten Sie, dass, wenn die letzte Spalte in der TABLE-Definition der PRIMARY KEY ist, zuerst eine neue Spalte hinzugefügt und dann der Spaltentyp der neuen Spalte bearbeitet werden muss, um die FOREIGN KEY-Definition hinzuzufügen. Fügen Sie im Feld Spaltentyp ein Komma und das an

FOREIGN KEY (parent_id) REFERENCES parent(id)

definition nach Datentyp. Klicken Sie auf die Schaltfläche Ändern und dann auf die Schaltfläche Ja im Dialogfeld Gefährlicher Betrieb.

Referenz: SQLite Manager

3
Baso

Sie können dies versuchen:

ALTER TABLE [Child] ADD COLUMN column_name INTEGER REFERENCES parent_table_name(column_id);
1
Jamshy EK