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

Question

Ich habe die folgende Tabelle:

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

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

Die meisten Beispiele gehen davon aus, dass Sie die Tabelle erstellen - ich möchte die Einschränkung zu einer bestehenden Tabelle hinzufügen.

Answer 1

Das können Sie nicht.

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

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

SQLite unterstützt nicht le site ADD CONSTRAINT Variante des ALTER TABLE Befehl ( 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-Beschränkung erstellen und dann die Daten aus der temporären Tabelle wieder hineinkopieren. ( sqlite.org - FAQ: Q11 )

Answer 2

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

Erstellen Sie zunächst eine Tabelle ohne die parent_id:

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

Dann, Tabelle ändern:

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

Answer 3

Ja, das können Sie, ohne eine neue Spalte hinzuzufügen. Sie müssen darauf achten, dass Sie es richtig machen, damit die Datenbank nicht beschädigt wird. Daher sollten Sie Ihre Datenbank vollständig sichern, bevor Sie dies versuchen.

für Ihr spezifisches 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 ausgedrückt:

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 jedem Fall sollten Sie sich zunächst über die SQL-Definition informieren, bevor Sie Änderungen vornehmen:

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

Wenn Sie den replace()-Ansatz verwenden, kann es hilfreich sein, Ihren replace()-Befehl vor der Ausführung zu testen, indem Sie ihn ausführen:

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

Answer 4

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

Die einzigen Befehle zur Schemaänderung, die direkt von SQLite unterstützt werden, sind die Befehle "Tabelle umbenennen" und "Spalte hinzufügen", wie oben gezeigt. Jedoch, Anwendungen können jedoch andere beliebige Änderungen am Format einer Tabelle vornehmen durch eine einfache Abfolge von Operationen vornehmen. Die Schritte zur Durchführung beliebiger Änderungen am Schemadesign einer Tabelle X vorzunehmen, sind wie folgt:

1. Wenn Fremdschlüssel-Beschränkungen aktiviert sind, deaktivieren Sie sie mit PRAGMA foreign_keys=OFF.
2. Starten Sie eine Transaktion.
3. Merken Sie sich das Format aller Indizes und Auslöser, die mit Tabelle X. Diese Informationen werden in Schritt 8 unten benötigt. Eine Möglichkeit ist, eine Abfrage wie die folgende auszuführen: SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.
4. Verwenden Sie CREATE TABLE, um eine neue Tabelle "new_X" zu erstellen, die sich in der gewünschten überarbeiteten Format der Tabelle X. Stellen Sie sicher, dass der Name "new_X" natürlich nicht mit einem bestehenden Tabellennamen kollidiert.
5. Übertragen Sie den Inhalt von X in new_X mit einer Anweisung wie: INSERT INTO new_X SELECT ... FROM X.
6. Löschen Sie die alte Tabelle X: 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 zu rekonstruieren und Triggern zu rekonstruieren, die mit Tabelle X verbunden sind. Verwenden Sie vielleicht das alte Format der Triggers und der Indizes, die in Schritt 3 oben gespeichert wurden, als Leitfaden. Änderungen, die für die Änderung angemessen sind.
9. Wenn irgendwelche Ansichten auf die Tabelle X in einer Weise verweisen, die von der Schemaänderung betroffen sind, dann löschen Sie diese Ansichten mit DROP VIEW und erstellen Sie mit den Änderungen, die notwendig sind, um die Schemaänderung zu berücksichtigen Schemaänderung anzupassen, mit CREATE VIEW.
10. Wenn Fremdschlüssel-Beschränkungen ursprünglich aktiviert waren, führen Sie PRAGMA foreign_key_check aus, um zu überprüfen, dass die Schemaänderung keine keine Fremdschlüssel-Beschränkungen aufgehoben hat.
11. Bestätigen Sie die in Schritt 2 begonnene Transaktion.
12. Wenn Fremdschlüssel-Beschränkungen ursprünglich aktiviert waren, aktivieren Sie sie wieder jetzt.

Das obige Verfahren ist völlig allgemein und funktioniert auch, wenn die Schemaänderung dazu führt, dass sich die in der Tabelle gespeicherten Informationen ändern. Also ist die obige vollständige Prozedur geeignet, um eine Spalte zu löschen, Ändern der Reihenfolge der Spalten, Hinzufügen oder Entfernen einer UNIQUE-Beschränkung oder PRIMARY KEY, Hinzufügen von CHECK- oder FOREIGN KEY- oder NOT NULL-Beschränkungen, oder die Änderung des Datentyps einer Spalte, zum Beispiel.

Answer 5

Als @Daniel Vassallo hat gesagt, dass Sie das nicht tun können. Der Code, den Sie verwenden müssen, ist etwa so:

Gegeben die Tabelle:

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

Ich gehe davon aus, dass Sie den folgenden Foreign Key hinzufügen möchten:

FOREIGN KEY (parent_id) REFERENCES parent(id);

Ich würde also eine temporäre Tabelle auf der Grundlage dieser Tabelle erstellen, dann würde ich eine neue Tabelle wie die erste erstellen, aber mit dem Fremdschlüssel, und schließlich würde ich ihr die Daten der temporären Tabelle hinzufügen:

CREATE TEMPORARY TABLE temp AS
SELECT 
    id,
    parent_id,
    description
FROM child;

DROP TABLE child;

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

INSERT INTO child
 (  id,
    parent_id,
    description)
SELECT
    id,
    parent_id,
    description
FROM temp;